On 9/22/21 9:56 AM, Michael Lewis wrote:
On Wed, Sep 22, 2021 at 12:44 AM cen <cen.is.i...@gmail.com
<mailto:cen.is.i...@gmail.com>> wrote:
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 <http://e.id> )
from events AS e
join tenants AS t ON t.id <http://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?
If your event doesn't tell you its locale, all is lost.
All events happen at some point in UTC-space. The consumer of the event
must adjust UTC according.
