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.