Thread: Why can't I use daylight savings time aware time zones with the AT TIME ZONE clause?
Why can't I use daylight savings time aware time zones with the AT TIME ZONE clause?
From
"Davidson, Robert"
Date:
<p><font face="Arial" size="2">I would like to use daylight savings time aware time zones to display dates, but can't seemto get them to work no matter what alias I use:</font><p><font face="Arial" size="2">select '2005-02-25 00:00:00 -800'AT TIME ZONE 'CST6CDT' as interval</font><br /><font face="Arial" size="2">…</font><br /><font face="Arial" size="2">ERROR: time zone "cst6cdt" not recognized</font><p><font face="Arial" size="2">select '2005-02-25 00:00:00 -800'AT TIME ZONE 'America/Chicago' as interval</font><br /><font face="Arial" size="2">...</font><br /><font face="Arial"size="2">ERROR: time zone "america/chicago" not recognized</font><p><font face="Arial" size="2">However non-awaretime zones are available:</font><br /><font face="Arial" size="2">select '2005-02-25 00:00:00 -800' AT TIME ZONE'CST' as interval</font><br /><font face="Arial" size="2">2005-02-25 02:00:00</font><p><font face="Arial" size="2">select'2005-02-25 00:00:00 -800' AT TIME ZONE 'CDT' as interval</font><br /><font face="Arial" size="2">2005-02-2503:00:00</font><p><font face="Arial" size="2">Setting the time zone works just fine</font><br /><fontface="Arial" size="2">set time zone 'America/Chicago';</font><br /><font face="Arial" size="2">Query returned successfullywith no result in 20 ms.</font><p><font face="Arial" size="2">set time zone 'CST6CDT';</font><br /><font face="Arial"size="2">Query returned successfully with no result in 20 ms.</font><br /><p><font face="Arial" size="2">Thereis a file ../pgsql/share/timezone/CST6CDT</font><br /><font face="Arial" size="2">And ../pgsql/share/timezone/America/Chicago</font><p><fontface="Arial" size="2">select version();</font><br /><font face="Arial"size="2">PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3</font><p><font face="Arial" size="2">Whycan't I use daylight savings time aware time zones with the AT TIME ZONE clause?</font><p><font face="Arial"size="2">Many thanks,</font><p><font face="Arial" size="2">Robert Davidson</font>
Re: Why can't I use daylight savings time aware time zones with the AT TIME ZONE clause?
From
Michael Fuhr
Date:
On Thu, Mar 02, 2006 at 02:33:08PM -0800, Davidson, Robert wrote: > I would like to use daylight savings time aware time zones to > display dates, but can't seem to get them to work no matter what > alias I use: > > select '2005-02-25 00:00:00 -800' AT TIME ZONE 'CST6CDT' as interval > ... > ERROR: time zone "cst6cdt" not recognized This works only since 8.1. Here's an excerpt from the 8.1 Release Notes: * Allow the full use of time zone names in AT TIME ZONE, not just the short list previously available (Magnus) -- Michael Fuhr