Handling Daylight Savings

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Handling Daylight Savings

Walker, Jed S
Handling Daylight Savings

I am wondering how daylight savings is handled by PostgreSQL. We are timestamps with time zones, and are wondering if its possible to specify a timezone as ET instead of EST or EDT. Do we have to know when daylight savings occurs and change our timezone settings on our own?

Thanks,

Jed.

Reply | Threaded
Open this post in threaded view
|

Re: Handling Daylight Savings

Michael Fuhr
On Thu, Aug 04, 2005 at 01:54:39PM -0600, Walker, Jed S wrote:
> I am wondering how daylight savings is handled by PostgreSQL. We are
> timestamps with time zones, and are wondering if it's possible to
> specify a timezone as ET instead of EST or EDT. Do we have to know when
> daylight savings occurs and change our timezone settings on our own?

See "Date/Time Types" in the documentation for the version of
PostgreSQL you're using.  Here's a link for the latest version:

http://www.postgresql.org/docs/8.0/static/datatype-datetime.html

PostgreSQL 8.0 comes with its own timezone database.  Earlier
versions rely on whatever the operating system provides.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Handling Daylight Savings

Frank Bax
At 04:34 PM 8/4/05, Michael Fuhr wrote:

>On Thu, Aug 04, 2005 at 01:54:39PM -0600, Walker, Jed S wrote:
> > I am wondering how daylight savings is handled by PostgreSQL. We are
> > timestamps with time zones, and are wondering if it's possible to
> > specify a timezone as ET instead of EST or EDT. Do we have to know when
> > daylight savings occurs and change our timezone settings on our own?
>
>See "Date/Time Types" in the documentation for the version of
>PostgreSQL you're using.  Here's a link for the latest version:
>
>http://www.postgresql.org/docs/8.0/static/datatype-datetime.html
>
>PostgreSQL 8.0 comes with its own timezone database.  Earlier
>versions rely on whatever the operating system provides.


How is the actual date of change handled for each spring/fall change?  Is
the "timezone database" configurable by year?  What happens if proposed
changes to DST effective dates in North America are adopted?


---------------------------(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
Reply | Threaded
Open this post in threaded view
|

Re: Handling Daylight Savings

Michael Fuhr
On Thu, Aug 04, 2005 at 05:04:50PM -0400, Frank Bax wrote:
> How is the actual date of change handled for each spring/fall change?  Is
> the "timezone database" configurable by year?  What happens if proposed
> changes to DST effective dates in North America are adopted?

Each timezone file has entries for each year, showing when DST
changes take effect.  On some systems you can use the "zdump"
command to see this information.  For example:

% zdump -v EST5EDT | grep 2005
EST5EDT  Sun Apr  3 06:59:59 2005 UTC = Sun Apr  3 01:59:59 2005 EST isdst=0 gmtoff=-18000
EST5EDT  Sun Apr  3 07:00:00 2005 UTC = Sun Apr  3 03:00:00 2005 EDT isdst=1 gmtoff=-14400
EST5EDT  Sun Oct 30 05:59:59 2005 UTC = Sun Oct 30 01:59:59 2005 EDT isdst=1 gmtoff=-14400
EST5EDT  Sun Oct 30 06:00:00 2005 UTC = Sun Oct 30 01:00:00 2005 EST isdst=0 gmtoff=-18000

If the rules change then the timezone files should be updated.
According to the PostgreSQL source code, its timezone database is
adapted from the library at the following site:

ftp://elsie.nci.nih.gov/pub/

Presumably new releases of PostgreSQL will be kept reasonably
current.  As for how the operating system's timezone database is
managed, that depends on the vendor and on whether the system
administrator is keeping up with such things.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings