Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones

> What I do in such cases is to add an extra column with the UTC timestamp
to serve as a linear scale to the local timestamps. That also helps with
ordering buckets in reports and such during DST changes (especially the
ones where an hour repeats).

> For hours and quarter hours I found it to be fairly convenient to base a
view on a join between a date calendar and an (quarter of an) hour per UTC
day table, but materialising that with some indexes may perform better (at
the cost of disk space). I do materialise that currently, but our database
server doesn’t have a lot of memory so I’m often not hitting the cache and
performance suffers a bit (infrastructure is about to change for the better
though).

That's an interesting idea, but I'm not sure I fully understand. Assuming
you're aggregating data: what do you group by? For instance, at an hourly
resolution, if you group by both the UTC timestamp and the local one, you
might end up, say, dividing an hour-long bucket in two for time zones with
half-hour-based offsets, no?

Thanks for the detailed writeup! Definitely helpful to learn more about
what people are using in production to handle this sort of thing.

--
Lincoln Swaine-Moore