Psycopg psycopg

Comments and changes to this ticket

• 

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