From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | jgirvin <james(dot)girvin(at)oasissystems(dot)com(dot)au> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: dst question |
Date: | 2010-03-11 05:53:07 |
Message-ID: | 13467.1268286787@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
jgirvin <james(dot)girvin(at)oasissystems(dot)com(dot)au> writes:
> Australia will come out of DST on the 4th April 2010 at 03:00:00 and
> will be +9:30 from utc, currently we are +10:30 utc.
OK, so this is a "fall back" transition for you guys, right?
> This is 4 seconds into the last hour prior to dst changeover, now the
> to_timestamp result is showing +09:30 as the offset and now() correctly
> shows +10:30
> # select now(), to_timestamp( to_char(now(),'yyyy-mm-dd
> hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss');
> now | to_timestamp
> ----------------------------------+---------------------------
> 2010-04-04 02:00:04.841797+10:30 | 2010-04-04 02:00:04+09:30
> (1 row)
> Can someone explain as to why the output from the to_timestamp shows the
> offset at +09:30 when within the hour of the dst changeover and is this
> expected....
The problem is that the output of to_char() is ambiguous, since you
didn't include the timezone in the format spec. Times between 02:00 and
03:00 occur twice on that date, and there's no way to know which time
"02:00:04" refers to. The assumption that to_timestamp uses (along with
our other datetime input code) is that an ambiguous time should be
resolved as standard time, ie, the second occurrence of "02:00:04".
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2010-03-11 06:59:56 | Re: Finding duplicates only. |
Previous Message | Chris Travers | 2010-03-11 04:52:07 | Re: Naming conventions for lots of stored procedures |