How to generate a column for 24-hour or 7-day Builder histograms

Learn how to generate numeric values representing a day or week, to be used later with Builder histogram widgets

To give map viewers more granular control over which dates/times they select, some CARTO users opt to create a histogram widget instead of a time series widget. To use a histogram widget you must convert your date/timestamp column values into numbers.

• Start by adding a new field in your query, like my_hour below. This query extracts hours from your timestamp column. The my_hour column will be populated with numbers representing those hours:

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

Then add a histogram based on my_hour and set the number of bins to 24.

• To generate numbers representing days of the week instead you can use the query below. The number 1 represents Monday.

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

Then just add a 7-bin histogram based on my_day .

If you want the days of the week numbers to start on Sunday instead of Monday, just use dow instead of isodow and you’re done!