Thread: Time zone specifications, abbreviations vs full names

Either I am exceptionally confused, or the documentation in 8.5.3 appears to be wrong.  Could someone clarify what I'm
missing?

The documentation states: "In short, this is the difference between abbreviations and full names: abbreviations always
representa fixed offset from UTC, whereas most of the full names imply a local daylight-savings time rule, and so have
twopossible UTC offsets."  "PST" is given as a specific example of an abbreviation in this case.  But it appears that
using"PST" gives you a time zone rule: 

CREATE TABLE t (
   z TIMESTAMP WITH TIME ZONE
);

insert into t values ( '2011-03-13 01:00 PST'::timestamptz);
insert into t values ( '2011-03-13 01:00-7'::timestamptz );
update t set z = z + '2 hours'::interval;

select * from t;
           z
------------------------
 2011-03-13 04:00:00-07
 2011-03-13 03:00:00-07
(2 rows)

--

But if "PST" really did imply a fixed offset, shouldn't the results be the same?

--
-- Christophe Pettus
   xof@thebuild.com

On Sunday, May 08, 2011 10:23:44 am Christophe Pettus wrote:
> Either I am exceptionally confused, or the documentation in 8.5.3 appears
> to be wrong.  Could someone clarify what I'm missing?
>
> The documentation states: "In short, this is the difference between
> abbreviations and full names: abbreviations always represent a fixed
> offset from UTC, whereas most of the full names imply a local
> daylight-savings time rule, and so have two possible UTC offsets."  "PST"
> is given as a specific example of an abbreviation in this case.  But it
> appears that using "PST" gives you a time zone rule:
>
> CREATE TABLE t (
>    z TIMESTAMP WITH TIME ZONE
> );
>
> insert into t values ( '2011-03-13 01:00 PST'::timestamptz);
> insert into t values ( '2011-03-13 01:00-7'::timestamptz );
> update t set z = z + '2 hours'::interval;
>
> select * from t;
>            z
> ------------------------
>  2011-03-13 04:00:00-07
>  2011-03-13 03:00:00-07
> (2 rows)
>
> --
>
> But if "PST" really did imply a fixed offset, shouldn't the results be the
> same?

No, PST has an offset of -8. So for your initial entry you have:

test(5432)aklaver=>SELECT z at time zone 'UTC' from t;
      timezone
---------------------
 2011-03-13 09:00:00
 2011-03-13 08:00:00

and after the update:

test(5432)aklaver=>SELECT z at time zone 'UTC' from t;
      timezone
---------------------
 2011-03-13 11:00:00
 2011-03-13 10:00:00


>
> --
> -- Christophe Pettus
>    xof@thebuild.com

--
Adrian Klaver
adrian.klaver@gmail.com

On May 8, 2011, at 11:40 AM, Adrian Klaver wrote:

> No, PST has an offset of -8.

Head > desk.  Thank you.

--
-- Christophe Pettus
   xof@thebuild.com