I have data from many years with precise timestamps called event_date. I want to show one year's time frame, and the count of value1 across all years combined.
I can do this with monthly accuracy with the dimension MONTH(event_date) or weekly accuracy WEEK(event_date), but DATE(event_date) is the same as event_date and shows the time all the way from beginning to the end. DAY(event_date) shows only one month's time frame.
I can also do something like MONTH(event_date)&'-'&DAY(event_date), which works, but after this the continuous scale doesn't work. I guess it gets too cramped.
Is it possible to have daily time unit, but display it in a year's time frame (with maybe only monthly labels) ?
Date() is just a formatting and left the real value untouched. This means you need to cut the date-part from the timestamp. This could be done per rounding with:
date(floor(event_date)) as date
Beside this it's recommended not to keep timestamps within the datamodel else to split dates and times - could be done per time(frac(event_date)) as time - and to connect those fields with a master calendar and master timetable which then contain all needed period- and time-fields. And if there is really a need to display and/or to calculate with a timestamp within the UI you could just add them again with timestamp(date + time).
