Unexpected conversion to server time zone upon inserting non-naïve `datetime.datetime`
Reported by Sam Homerson | August 17th, 2013 @ 05:15 PM
Upon inserting a non-naïve datetime.datetime object, i.e. one with a time zone, psycopg2 converts the timestamp before inserting it, even if the time zone of the timestamp is tzutc().
Here is an example that shows the problem (test DB provided by api.postgression.com).
import logging from datetime import datetime, timedelta from dateutil.tz import tzutc import psycopg2 logging.getLogger().setLevel(logging.DEBUG) DB_URL = 'postgres://irsdvdmxcxatqe:jdFO7u4Grz-k8MNjzwBeJxVMHi@ec2-54-221-225-251.compute-1.amazonaws.com:5432/d2i3i0ocq6as5l' timestamp = datetime(1970, 1, 1, 0, 0, 0, 0) timestamptz = datetime(1970, 1, 1, 0, 0, 0, 0, tzinfo=tzutc()) conn = psycopg2.connect(DB_URL) cur = conn.cursor() logging.debug('Set timezone') cur.execute("SET TIMEZONE = 'America/New_York'") logging.debug('Create table') cur.execute("CREATE TABLE test (" +"id serial PRIMARY KEY, " +"timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL);") conn.commit() logging.debug('Insert timestamp') cur.execute("INSERT INTO test (timestamp) VALUES (%s)", ( (timestamp,) )) conn.commit() logging.debug('Insert timestamptz') cur.execute("INSERT INTO test (timestamp) VALUES (%s)", (timestamptz,)) conn.commit() logging.debug('Result') cur.execute("SELECT * FROM test;") for row in cur.fetchall(): print 'id=%r timestamp=%r' %(row, row) cur.close() conn.close()
Running this in ipython yields the following output:
In : %run util/psycopg2-postgres-timestamp-bug.py DEBUG:root:Set timezone DEBUG:root:Create table DEBUG:root:Insert timestamp DEBUG:root:Insert timestamptz DEBUG:root:Result id=1 timestamp=datetime.datetime(1970, 1, 1, 0, 0) id=2 timestamp=datetime.datetime(1969, 12, 31, 19, 0)
So the first timestamp, the one without a time zone, was not converted to the server time zone and rather treated as a UTC timestamp. This is great.
However, the second timestamp, which explicitly is in UTC, is converted to the server's time zone and then inserted.
Note that in the above example, I'm setting the time zone for the session. The situation that bothers me, however, is when the server time zone is set to something other than UTC, and the same conversion happens.
Comments and changes to this ticket
- State changed from new to hold
The current behaviour descends from the way psycopg casts datetime objects and how postgresql reacts to that.
Psycopg commands result in:
SET TIMEZONE = 'America/New_York'; create table testtz (id serial primary key, ts timestamp); INSERT INTO testtz (ts) VALUES ('1970-01-01T00:00:00'::timestamp); INSERT INTO testtz (ts) VALUES ('1970-01-01T00:00:00+00:00'::timestamptz); select * from testtz; id | ts ----+--------------------- 1 | 1970-01-01 00:00:00 2 | 1969-12-31 19:00:00 (2 rows)
the surprising bit is that running:
INSERT INTO testtz (ts) VALUES ('1970-01-01T00:00:00+00:00'); select * from testtz where id = 3; id | ts ----+--------------------- 3 | 1970-01-01 00:00:00
In record 3 postgres received an unknown literal containing a tz aware timestamp: it decided to insert it discarding the timestamp specification. I find this really surprising.
In record 2 the timestamp has a timezone specification: the conversion to unaware timestamp implies a zone change instead.
I honestly find psycopg behaviour more reasonable, although I can believe this can be argued about forever. What bothers me is that if we changed psycopg implementation to support libpq parameter we would likely end up changing this behaviour (but several other behaviours would change as well, that was the reason to start being explicit about the cast, so actually my bother level is not that high).
I'd say this is not going to change in psycopg2: if you are writing a timestamp with time zone into a non-tz-aware field and you explicitly want "truncation instead of rounding" you can use
timestamptz.replace(tzinfo=None). In psycopg3 probably it would work as you expect, as a side effect of changing the way of passing parameters.
I'd be happy to ask postgres ML why these results are different (I haven't found any note in the docs):
piro=> SET TIMEZONE = 'America/New_York';SET piro=> select '1970-01-01T00:00:00+03:00'::timestamp; timestamp --------------------- 1970-01-01 00:00:00 (1 row) piro=> select '1970-01-01T00:00:00+03:00'::timestamptz::timestamp; timestamp --------------------- 1969-12-31 16:00:00 (1 row)
the problem is here.
- State changed from hold to invalid
I've written to pgsql-general to get some explanation. Here's an answer: http://www.postgresql.org/message-id/52136DB9.firstname.lastname@example.org
So it's documented that Postgres silently discards the tzinfo, and the workaround is sort of what psycopg implements. While I understand the implementation constraints, I don't find Postgres behaviour sane in this regard (it's not even SQL compliant) so I'm happy psycopg behaves in a saner way. Too bad we may not be able to keep the sanity in the future.
Please drop the tzinfo using
timestamptz.replace(tzinfo=None)if you want the postgres interpretation of the object.
Marking the bug invalid but meaning "wontfix".
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.