From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | cen <cen(dot)is(dot)imba(at)gmail(dot)com> |
Cc: | Tim Uckun <timuckun(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Timestamp with vs without time zone. |
Date: | 2021-09-21 21:34:50 |
Message-ID: | CAHOFxGpHM8Ka+S2eTSMSR=TRA5DsQ95C+H7Ezrs5d_1UjD6HXQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-09-21 21:49:21 | Re: Timestamp with vs without time zone. |
Previous Message | cen | 2021-09-21 20:39:38 | Re: Timestamp with vs without time zone. |