Related to this current discussion and exchange of ideas... is there a best practice for retrieving data in such a way as the rows are localized to a timezone for where/group by purposes. That is, if I have a table which has events, but those events belong to a tenant or some entity that has a location which implies a timezone (or at least an offset), is there a best way to write a query similar to the below? Please forgive and overlook if there is some obvious syntax error, as this is just a quick and dirty example. Might it make sense to store a "localized" version of the timestamp *without* timezone on the event record such that an index can be used for fast retrieval and even grouping?
select date_trunc( 'month', e.event_datetime AT TIMEZONE t.time_zone_name ) AS event_date,
count( e.id ) from events AS e join tenants AS t ON t.id = e.tenant_id
where e.event_datetime AT TIMEZONE t.time_zone_name >= '01/01/2021'::DATE AND e.event_datetime AT TIMEZONE t.time_zone_name < '09/01/2021'::DATE;
This is an interesting case. A simplified query example would be to "give me all events for this year".
I am not sure what the cost of shifting UTC is, probably not much, but depending on use case it might make sense to deconstruct into date and time for query optimization.
Does that solve anything? My concern is that for the events at the begin and end of the where (or group by) we may want to include them or exclude them depending on the time in that location. If I want to know how many events happen between 8am and 5pm for each location in California and New York for example, I'm not certain if a second timestamp that is "localized" should be stored, or if there is some better way to achieve that goal. At least for some data in the evening, the date value will be different between UTC and local timezone for that location. Just setting my session to an east coast equivalent timezone when I pull the data still doesn't give me "what are all the events that happened in California" accurately. The goal is that the data is accurate for that location. Not as much a concern if there are only a few hours apart, but if the user is running a report across properties all over the world... what's the proper way to ensure each date means the day for that location?