On 21. 09. 21 23:34, Michael Lewis wrote:
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.