Joining between date and timestamp fields in Postgres

Joining tables on date and timestamp with timezone fields in Postgres1 needs careful handling because of time zones and daylight-saving time.

To illustrate, assume we have two tables:

  • t1 which has a field of type date and a foreign-key t2_id to
  • t2 which 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.


  1. Which correspond to time-zone-aware datetime values in Python. [return]
----

Something wrong? Suggest an improvement or add a comment (see article history)
Tagged with: postgres
Filed in: tips

Previous: Little-known words relevant to software development
Next: Shortcuts of the old and minimalist

Copyright © 2005-2017 David Winterbottom
Content licensed under CC BY-NC-SA 4.0.