Re: Timestamp with vs without time zone.

On Wed, Sep 22, 2021 at 12:44 AM cen <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 )
> 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?