Re: problem with bytea and undecorated literals

On Tue, May 4, 2010 at 8:13 AM, Karsten Hilbert <[email protected]> wrote:
> On Tue, May 04, 2010 at 03:02:23AM +0100, Daniele Varrazzo wrote:
>
>> >>> cur.execute("select '2010-05-04T02:27:11+08:00'::timestamptz")
>> >>> cur.fetchone()[0].isoformat()
>> '2010-05-03T19:27:11+01:00' # should have been 08:00

> Could this be PostgreSQL converting the
>        '2010-05-04T02:27:11+08:00'
> to UTC on the way in and giving it back to you as
>        '2010-05-03T19:27:11+01:00'
> on the way out due to the testing client's timezone
> being set to "+1" ?

You're right: I think I was too sleepy and didn't notice the time
changing, only the offset. psycopg parses correctly what PG gives it
back.

There are always puffs of smoke coming out from my ears when I deal
with timezones, so I'd like my other statement to be reviewed: it
seems that if we cast a datetime with tz specified to ::timestamp we
truncate the tz part:

test=> select '2010-05-04T02:27:11+08:00'::timestamp;
      timestamp
---------------------
 2010-05-04 02:27:11

so psycopg should decide which type to cast into according to the
tzinfo presence:

test=> select '2010-05-04T02:27:11+08:00'::timestamptz;
      timestamptz
------------------------
 2010-05-03 19:27:11+01

...even if this is not what I was expecting honestly... I should read
PG docs about the argument again.

Is the behaviour "cast into timestamp if tzinfo is None, else
timestamptz" a correct one for psycopg?

-- Daniele