Re: [GENERAL] Timestamp/Timezone - does this make sense?
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Timestamp/Timezone - does this make sense?
Ron Johnson <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Timestamp/Timezone - does this make sense?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 01:42, Tom Lane wrote: > Mike Harding <[EMAIL PROTECTED]> 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- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Timestamp/Timezone - does this make sense?
Mike Harding <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] 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? ---(end of broadcast)--- TIP 6: explain analyze is your friend