How to compare dates with psycopg2 (PostgreSQL)?

Question

By using the PostgreSQL Python module psycopg2, I want to find rows that have an end time that falls before some timestamp. I tried to do this with a cursor.execute(…) on a SQL query with … where end_time < %s, where %s replaced by a datetime.datetime object with a timezone. The SQL generated by psycopg2 is:

select * from my_table where end_time < '2014-05-01T13:00:00+00:00'::timestamptz;

which returns a row like:

id  |     start_time      |      end_time       
331 | 2014-05-01 14:55:00 | 2014-05-01 15:05:00

The end time is not earlier than 2014-05-01 at 13:00 UTC, contrary to what I was expecting.

What is the proper way of performing the desired time selection in psycopg2?

Are you sure that the time is not converted to local time for display? What is utc value for `end_time`? — jfs, May 05 '14 at 11:23
I understand that these times are UTC: `select end_time at time zone 'UTC'…` yields `2014-05-01 23:05:00+08` for the end time in the returned row I cited. — Eric O. Lebigot, May 05 '14 at 12:30
@J.F.Sebastian: Hmmm, I now understand that I did not get the UTC value, with the `SELECT` in my previous comment, but merely asked a no-timezone timestamp to be considered as being UTC (before being displayed in my local timezone). Having timestamps with timezones *in the table* solves the problem, you were right about focusing on timezones. — Eric O. Lebigot, May 05 '14 at 12:51

Eric O. Lebigot · Accepted Answer · 2014-05-06T01:12:18.727

Alright, using PostgreSQL timestamps with time zones in the table solved the problem.

Here are details about where the problem was:

I put timezone-aware timestamps in columns that are not timezone-aware. I assumed that the timestamp without time zone column would contain the UTC time (like for timestamp with time zone), but it actually contains the timestamp in local time, as described in the documentation:

Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE.

Thus, 00:00 UTC is stored as 08:00 in a column timestamp without time zone when in China.
When doing a comparison like "timestamp without time zone" < "timestamp with time zone", I can't find in the documentation what PostgreSQL does. However, what was observed in the question is consistent with the idea that the timezone is first removed in the same way, and the timestamps without timezone are compared. I should not have been surprised by the result: the late time "15:00" is indeed earlier than 13:00 UTC, in China (UTC+8). The key was to know that displayed (and stored) times are in the local time zone.

The moral I get from this is: conversions between timestamps with and without timezones are better handled explicitly.

the moral should be: [set database timezone to UTC, store UTC time (with timezone) and if you need it you could add local offset, timezone name such as Europe/London separately](http://stackoverflow.com/q/2532729/4279) — jfs, May 05 '14 at 13:51
Interesting link. How do you "set the database timezone to UTC" with PostgreSQL? — Eric O. Lebigot, May 05 '14 at 13:55

Clodoaldo Neto · Answer 2 · 2014-05-05T12:51:10.617

0

Specify the time zone at which the times should be displayed. If start_time and end_time are timestamp without time zone as your output suggests then you also need to tell postgresql at what time zone they should be in

select 
    id,
    start_time at time zone 'UTC' at time zone 'UTC',
    end_time at time zone 'UTC' at time zone 'UTC'
from my_table 
where end_time at time zone 'UTC' < %s

edited May 05 '14 at 12:51

answered May 05 '14 at 11:32

Clodoaldo Neto

118,695
26
233
260

With `where end_time < '2014-05-01T13:00:00+00:00'::timestamptz;`, this yields a row with an end time of `2014-05-01 23:05:00+08`, so at 15:05 UTC, despite the fact that the limit is at 13:00 UTC. Do I understand correctly that the problem is still there? – Eric O. Lebigot May 05 '14 at 12:28
You are right that this is a timezone problem. However, this solution does not work, but doing the double at time zone 'UTC' also for the test work produces a consistent (i.e. non-surprising) result. I just understood that the times are wrong, though: the original times are *not* UTC, so doing the first `at time zone 'UTC'` creates mostly meaningless times… – Eric O. Lebigot May 05 '14 at 13:44

How to compare dates with psycopg2 (PostgreSQL)?

2 Answers2