Thread: Handling Daylight Savings

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?

Thanks,

Jed.

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

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?

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