This project is archived and is in readonly mode.

Unexpected conversion to server time zone upon inserting non-naïve `datetime.datetime`

#176

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[0], row[1])
    
cur.close()
conn.close()

Running this in ipython yields the following output:

In [3]: %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.

Reported by Sam Homerson · August 17th, 2013 @ 05:15 PM

Activity

Daniele Varrazzo
- 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.
August 20th, 2013 @ 10:47 AM
Daniele Varrazzo
- 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.3040700@gmail.com

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".

August 22nd, 2013 @ 06:04 PM