Hello John,
this looks like a nice feature. I'm wondering how it relates to the
following use-case:
When drawing charts, the user can select pre-defined widths on times
(like "15 min", "1 hour").
The data for these slots is fitted always to intervalls that start in 0
in the slot, e.g. if the user selects "15 min", the interval always
starts at xx:00, xx:15, xx:30 or xx:45. This is to aid caching of the
resulting data, and so that requesting the same chart at xx:00 and xx:01
actually draws the same chart, and not some bar with only one minute
data at at the end.
In PSQL, this works out to using this as GROUP BY and then summing up
all values:
SELECT FLOOR(EXTRACT(EPOCH from thetime) / 3600) * 3600, SUM(events)
FROM mytable ... GROUP BY 1;
whereas here 3600 means "hourly".
It is of course easy for things like "1 hour", but for yearly I had to
come up with things like:
EXTRAC(YEAR FROM thetime) * 12 + EXTRACT(MONTH FROM thetime)
and its gets even more involved for weeks, weekdays or odd things like
fortnights.
So would that mean one could replace this by:
date_trunc_interval('3600 seconds'::interval, thetime)
and it would be easier, and (hopefully) faster?
Best regards,
Tels
