Thread: Timestamp/Timezone - does this make sense?

mvh=> set time zone 'UTC';
SET
mvh=> select now();
              now
-------------------------------
 2007-02-13 03:37:35.660652+00
(1 row)

mvh=> select timestamp with time zone '2007-01-01' at time zone
'America/Los_Angeles';
      timezone
---------------------
 2006-12-31 16:00:00
(1 row)

mvh=> select timestamp '2007-01-01' at time zone 'America/Los_Angeles';
timezone
------------------------
 2007-01-01 08:00:00+00
(1 row)

Where does that extra 8 hours come from?

Mike Harding <mharding@edentreetech.com> writes:
> Where does that extra 8 hours come from?

Ellay is 8 hours west of UTC (at least on 1-Jan, at least till our
congresscritters see fit to monkey with the DST laws again).  What
problem have you got with these answers?  They look right to me.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/14/07 01:42, Tom Lane wrote:
> Mike Harding <mharding@edentreetech.com> writes:
>> Where does that extra 8 hours come from?
>
> Ellay is 8 hours west of UTC (at least on 1-Jan, at least till our
> congresscritters see fit to monkey with the DST laws again).  What
> problem have you got with these answers?  They look right to me.

I think he's asking why:
    select timestamp with time zone '2007-01-01'
        at time zone 'America/Los_Angeles';
    ---------------------
    2006-12-31 16:00:00

returns a different value than
    select timestamp '2007-01-01'
        at time zone 'America/Los_Angeles';
    ------------------------
    2007-01-01 08:00:00+00
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0sNSS9HxQb37XmcRAuFXAJ0Z82uaW7FKKAuCnYbrm/bh8MAyCgCfWUW5
2blMHVkmjhYEjsGzk0o+ybM=
=GbW7
-----END PGP SIGNATURE-----

Ron Johnson <ron.l.johnson@cox.net> writes:
> I think he's asking why:
>     select timestamp with time zone '2007-01-01'
>         at time zone 'America/Los_Angeles';
> returns a different value than
>     select timestamp '2007-01-01'
>         at time zone 'America/Los_Angeles';

Those are transformations in opposite directions; see the manual.

            regards, tom lane

Mike Harding wrote:
> mvh=> set time zone 'UTC';
> SET
> mvh=> select now();
> now
> -------------------------------
> 2007-02-13 03:37:35.660652+00
> (1 row)

"+00" says your database thinks you are in Greenwich.
>
> mvh=> select timestamp with time zone '2007-01-01' at time zone
> 'America/Los_Angeles';
> timezone
> ---------------------
> 2006-12-31 16:00:00
> (1 row)

- "timestamp with time zone '2007-01-01'" = midnight at your location.


>
> mvh=> select timestamp '2007-01-01' at time zone 'America/Los_Angeles';
> timezone
> ------------------------
> 2007-01-01 08:00:00+00
> (1 row)

- "timestamp '2007-01-01' at time zone 'America/Los_Angeles'" is
midnight in Los Angeles.

>
> Where does that extra 8 hours come from?
>
>

In the first case, the data base is telling you the local time in Los
Angeles equivalent to midnight your local time; in the second case it is
telling you the local time at your location equivalent to midnight in
Los_Angeles.