Re: problem with bytea and undecorated literals

On 05/04/2010 04:23 AM, Daniele Varrazzo wrote:
> 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:

If you cast text that contains a timezone part to timestamp, it ignores 
the time zone. If you cast timestamptz to timestamp, it converts to UTC.

gxti*=# select '2010-05-04T02:27:11+08:00'::timestamp;
timestamp | 2010-05-04 02:27:11

gxti*=# select '2010-05-04T02:27:11+08:00'::timestamptz;
timestamptz | 2010-05-03 14:27:11-04

gxti*=# select ('2010-05-04T02:27:11+08:00'::timestamptz)::timestamp;
timestamp | 2010-05-03 14:27:11

The second and third statements here are the correct one for psycopg to 
be doing as the initial cast (from text) should be the one which most 
closely matches the contents of the text. From there the user can cast 
it to whatever they like.

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

Absolutely. Cast to the closest representation first, then let the user 
(or their column types) determine the final form.

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

The key to understanding timestamptz is that it *doesn't actually store 
the time zone* -- it is the same size as a timestamp. Postgres just 
converts to UTC on the way in, and back to your client's "timezone" 
setting on the way out.

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

Yes.

>
> -- Daniele

-- m. tharp