Joining tables on
timestamp with timezone fields in Postgres1 needs careful handling
because of time zones and daylight-saving time.
To illustrate, assume we have two tables:
t1which has a field of type
dateand a foreign-key
t2which has a field of
timestamp with timezone.
We want to build SQL queries that join between these two tables with additional date constraints on the join.
Assume the date values in
t1 correspond to the
Europe/London time zone.
Join rows where a timestamp value is within the date
SELECT t1.* FROM t1 JOIN t2 ON ( t1.t2_id = t2.id AND t1.date_field = (t2.datetime_field AT TIME ZONE 'Europe/London')::DATE )
Here we convert the datetime values to the
Europe/London time zone before
casting to date.
Join rows where a timestamp value is equal to midnight of the date
SELECT t1.* FROM t1 JOIN t2 ON ( t1.t2_id = t2.id AND t1.date_field::TIMESTAMP = t2.datetime_field AT TIME ZONE 'Europe/London' )
Here we use the fact that type-casting a
date field with
::TIMESTAMP converts it to the midnight timestamp of the date.
- Which correspond to time-zone-aware
datetimevalues in Python. [return]