How to Generate a Column for 24-Hour or 7-Day Builder Histograms

To create histogram widgets in Builder, you need to convert date/timestamp values into numeric representations. This guide demonstrates two approaches for different histogram types.

24-Hour Histograms

Extract hours from your timestamp column using this query:

SELECT
    *,
    extract(hour from m.my_datetime) + round(extract(minute from m.my_datetime)/60) as my_hour
FROM
    table_name

Create a histogram widget based on my_hour and configure it with 24 bins.

7-Day Histograms

To generate numeric values representing days of the week (Monday = 1):

SELECT
    *,
    extract(isodow from m.my_datetime) as my_day
FROM
    table_name

Add a 7-bin histogram based on my_day.

Note: Replace isodow with dow if you prefer Sunday as your week’s starting day instead of Monday.

These approaches give map viewers more granular control over which dates/times they select compared to traditional time series widgets.