This project is archived and is in readonly mode.

#176 ✓invalid
Sam Homerson

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

Comments and changes to this ticket

  • Daniele Varrazzo

    Daniele Varrazzo August 20th, 2013 @ 10:47 AM

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

  • Daniele Varrazzo

    Daniele Varrazzo August 22nd, 2013 @ 06:04 PM

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

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="https://github.com/psycopg/psycopg2/issues">the new tracker</a>.
<br/>
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.

Shared Ticket Bins

Pages