Re: Timestamp with vs without time zone.

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.