Re: why does extract ( HOUR FROM TIMESTAMP '2010-01-01 00:00:00' at time zone 'utc' ) == 16?

On 03/18/2011 10:17 AM, bubba postgres wrote:
> Thank you for your thorough reply. It will take some time to digest
> your advice, but yes, I am specifically trying to avoid all TZ issues
> by using UTC everywhere all the time. My assumption was that Timestamp
> without timezone meant UTC, guess not.
>
> Regards,
> -JD
>

If you need to deal with multiple time zones, you can't avoid TZ issues.
But be aware that regardless of how you specify a point in time
(timestamp with time zone), PostgreSQL stores it internally at UTC and
that point in time can be displayed in any time zone you wish.

create table tzexamp (mytimestamp timestamptz);

-- The following are equivalent (based on my default timezone of
US/Pacific):
insert into tzexamp values (timestamptz '2010-01-01 00:00:00');
insert into tzexamp values (timestamptz '2010-01-01 08:00:00-00');
insert into tzexamp values (timestamptz '2010-01-01 03:00:00 EST5EDT');
insert into tzexamp values (abstime(1262332800));
insert into tzexamp values (timestamptz 'January 1 02:00:00 2010
posix/America/Chicago');
set timezone to 'Asia/Macao';
insert into tzexamp values (timestamptz '2010-01-01 16:00:00');
set timezone to default;

select * from tzexamp;
mytimestamp
------------------------
2010-01-01 00:00:00-08
2010-01-01 00:00:00-08
2010-01-01 00:00:00-08
2010-01-01 00:00:00-08
2010-01-01 00:00:00-08
2010-01-01 00:00:00-08

But for the client connecting from Japan:

set timezone to 'Asia/Tokyo';

select * from tzexamp;
mytimestamp
------------------------
2010-01-01 17:00:00+09
2010-01-01 17:00:00+09
2010-01-01 17:00:00+09
2010-01-01 17:00:00+09
2010-01-01 17:00:00+09
2010-01-01 17:00:00+09

Or, of course, GMT:

set timezone to 'UTC';

select * from tzexamp;
mytimestamp
------------------------
2010-01-01 08:00:00+00
2010-01-01 08:00:00+00
2010-01-01 08:00:00+00
2010-01-01 08:00:00+00
2010-01-01 08:00:00+00
2010-01-01 08:00:00+00

Cheers,
Steve