Обсуждение: 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.