How to generate a column for 24-hour or 7-day Builder histograms
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_hourbelow. This query extracts hours from your timestamp column. The
my_hourcolumn 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
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!