Thread: Daylight Savings Time

Hello List:

I need to know if there is a convienient way of establishing whether DST is
active within a function dealing with adjusting timestamps to other time
zones. The problem is that if I have the following timestamp:

'04/21/2006 17:05 EDT'

and I use the timezone() function in the following manner:

return (timezone ('CST', '04/21/2006 17:05 EDT')

I get a two hour difference in time. Note that neither of the two arguments
are hard coded as this example. The "CST" value is stored in the customer
profile because that is their time zone and the timestamp is generated from
argeuments passed into the function.

This is:
rnd=# select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)

TIA

Terry Lee Tucker <terry@esc1.com> writes:
> I need to know if there is a convienient way of establishing whether DST is
> active within a function dealing with adjusting timestamps to other time
> zones. The problem is that if I have the following timestamp:
> '04/21/2006 17:05 EDT'
> and I use the timezone() function in the following manner:
> return (timezone ('CST', '04/21/2006 17:05 EDT')
> I get a two hour difference in time.

Perhaps you should be using a DST-aware timezone specification?  Since
8.1 you could do

regression=# select timezone ('CST6CDT', '04/21/2006 17:05 EDT'::timestamptz);
      timezone
---------------------
 2006-04-21 16:05:00
(1 row)


            regards, tom lane

On Friday 21 April 2006 05:47 pm, Tom Lane <tgl@sss.pgh.pa.us> thus
communicated:
--> Terry Lee Tucker <terry@esc1.com> writes:
--> > I need to know if there is a convienient way of establishing whether
 DST is --> > active within a function dealing with adjusting timestamps to
 other time --> > zones. The problem is that if I have the following
 timestamp:
--> > '04/21/2006 17:05 EDT'
--> > and I use the timezone() function in the following manner:
--> > return (timezone ('CST', '04/21/2006 17:05 EDT')
--> > I get a two hour difference in time.
-->
--> Perhaps you should be using a DST-aware timezone specification?  Since
--> 8.1 you could do
-->
--> regression=# select timezone ('CST6CDT', '04/21/2006 17:05
 EDT'::timestamptz); -->       timezone
--> ---------------------
-->  2006-04-21 16:05:00
--> (1 row)
-->
-->
-->             regards, tom lane
-->
Thanks for the reply Tom. We will be upgrading to version 8.x hopefully in
August. I can implement a work around until then. So, when we can upgrade, we
will change the timezone specification in the customer profiles to the
DST-aware specification, and we will be set.