Re: problem with bytea and undecorated literals

On 05/04/2010 01:36 PM, Michael Tharp wrote:
> 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
>

Oops, I suppose I should read my own output -- if you cast timestamptz 
to timestamp, it converts to local time. You can always do "at time zone 
'utc'" to get a timestamp in UTC (or any other arbitrary timezone) of 
course.

-- m. tharp