Heatmap in 30 Minute Intervals

pstrauss

I've created a heatmap based on the timestamp of records with the day of week on the Category 1 axis and the hour of day on the Category 2 axis. However, the business is asking for a version that breaks things down into 30 minute intervals. I'm currently using the HOUR function on the timestamp to extract the hour, but I'm not sure how I could bucket these into 30 minute intervals.

Any ideas?

GrantSmith

You could do something like:

CONCAT(HOUR(`Timestamp`), ':', FLOOR(MINUTE(`Timestamp`)/30)*30)

Minute will return the number of minutes in the timestamp (0-59)

/30 will return a number between 0 and < 2

FLOOR will convert your fraction to 0 or 1.

*30 will convert the interval to either 0 or 30. This will then give you your half-hour buckets.

You may need to extract the hour and minute to separate beast modes in order to sort based on the hour and then the 30 minute bucket so that it's sorted properly.

pstrauss

Thanks @GrantSmith that's a good idea.

pstrauss

Managed to make it work. Thanks @GrantSmith