Re: Timestamp with vs without time zone.

On 9/22/21 9:56 AM, Michael Lewis wrote:
> On Wed, Sep 22, 2021 at 12:44 AM cen <cen(dot)is(dot)imba(at)gmail(dot)com
> <mailto:cen(dot)is(dot)imba(at)gmail(dot)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.