Comparing PostgreSQL timestamps without timezones with dates with a timezone

Problem: you have a timestamp field and you need to compare it with something that has a timezone.

Let’s assume your database default timezone is UTC (can check it with show timezone;). Thus, we are making the assumption that your dates are stored in UTC.

Since timestamp does not have any timezone data, we first need to read it in UTC, so it adds the timezone data. Then we can convert it to the desired timezone:

select timestamp_field AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York'

Now, we have the timezone and proper daylight-saving offset.

Just for fun, let’s check if the current hour matches the hour from the saved timestamp field in the ‘America/New_York’ timezone:

EXTRACT ( HOUR FROM timestamp_field at time zone 'UTC' at time zone 'America/New_York') = EXTRACT ( HOUR FROM now() at time zone 'America/New_York')

Notice, that we do not read now() in UTC timezone first, because our DB default timezone is UTC and now() already has all of the timezone data.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.