Thread: date_part returns wrong day for 1974-04-28

Phil Steinke (lintec@clarkhall.ca) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
date_part returns wrong day for 1974-04-28

Long Description
The date_part function is returning the wrong value for the date 1974-04-28.  It seems to work with other similar dates
(+/-a day, +/- a year). 

I'm using:
                            version
---------------------------------------------------------------
 PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2

Specifically, the postgresql_7.0.3-4 package from Debian unstable (sid), on a Pentium 100 with 32 megs of RAM (10MB
stillfree though). 

Feel free to contact me for more information.


Sample Code
select date_part('day', date('1974-04-28'));


No file was uploaded with this report

> Short Description
> date_part returns wrong day for 1974-04-28
> Long Description
> The date_part function is returning the wrong value for the date 1974-04-28.  It seems to work with other similar
dates(+/- a day, +/- a year). 
> Feel free to contact me for more information.

You are likely running across a "known feature" in PostgreSQL for
versions before the almost-here v7.1. I do not know which time zone you
are running in (need that info!) but in the US Pacific Time Zone
1973-1975 have variations due to the first energy crisis resulting from
the war in the Middle East. April 28 is not a significant date in my
time zone, but may be in yours.

                        - Thomas

* Thomas Lockhart was mumbling about..
> You are likely running across a "known feature" in PostgreSQL for
> versions before the almost-here v7.1. I do not know which time zone you
> are running in (need that info!) but in the US Pacific Time Zone

Aha..  I'm in Eastern (Canada), and doing this for any switch from EST to
EDT causes the problem.. e.g. April 1st of this year:

=> select timestamp(date('2001-04-01'));
       timestamp
------------------------
 2001-03-31 23:00:00-05
(1 row)

This skewed timestamp then gives the wrong day for the date_part.  I think
that time switches in EST/EDT are supposed to happen at 2am, not midnight,
to help avoid this kind of thing.

Phil