[BUGS] DATE_PART() BUG?

[Peter Kelly]

Here are operational details:

RedHat Linux 7.0:

Linux version 2.2.17-14 ([EMAIL PROTECTED]) (gcc version
egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)) #1 Mon Feb 5 15:25:12 EST
2001

PostgreSQL Version info:

postgresql-7.0.2-17
postgresql-server-7.0.2-17
postgresql-devel-7.0.2-17

tbs=# SELECT version(); 
   version   
-
 PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
(1 row)

Server/Memory Info:

Server is a Compaq Proliant 2500, Pentium Pro 200 with 256 MB RAM.

[root@lisa cgi-bin]# cat /proc/meminfo 
total:used:free:  shared: buffers:  cached:
Mem:  263831552 151920640 111910912 82890752 47788032 74584064
Swap: 271556608  7589888 263966720
MemTotal:257648 kB
MemFree: 109288 kB
MemShared:80948 kB
Buffers:  46668 kB
Cached:   72836 kB
BigTotal: 0 kB
BigFree:  0 kB
SwapTotal:   265192 kB
SwapFree:257780 kB

BUG:


We have an SQL statement that is giving wrong output.

Here is an example of the whole statement:

SELECT 
*, Date_Part('Month', CAST ('5/25/2001' AS DATE)) 
- Date_Part('Month', purchasedate ) + 1 AS thismonth 
FROM customers 
WHERE CAST('5/25/2001' AS DATE) - CAST ('12 months' AS INTERVAL) <
purchasedate 
AND purchasedate <= CAST ('5/25/2001' AS DATE) 
AND 25 <= Date_Part('Day', purchasedate) 
AND Date_Part('Day', purchasedate) <= 25 
AND merchantnumber != 'odc12' 
ORDER BY thismonth, id_num

Here is a simple example:

SELECT DATE_PART('DAY', CAST('04/1/2001' AS DATE)) as dayofmonth
 dayofmonth
--
 31
 
Why does April 1st display as May 31st?

Here is the table def:

CREATE TABLE "customers" (
"id_num" int4 DEFAULT nextval('customers_id_num_seq'::text) NOT
NULL,
"merchantnumber" character varying(9),
"producttype" character varying(7),
"purchasedate" date,
"emailaddress" character varying(60),
"firstname" character varying(25),
    "lastname" character varying(50),
"customerfullname" character varying(40),
"salesordernumber" character varying(20),
"notes" character varying(80)
);

Thanks


--
Peter Kelly, ETS.NET Inc.

Email:  mailto:[EMAIL PROTECTED]
PGP Key:http://www.ets.net/pkelly-pgp.html
Phone:  905-713-9978
Fax:905-726-8118

Visit us at http://www.ets.net!
--

"Opinions expressed are property of my evil twin, not my employer."

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[BUGS] RE: DATE_PART() BUG? We have an SQL statement that is giving wrong output.

[Peter Kelly]

Thanks!  That did it:

tbs=# SELECT DATE_PART('DAY', CAST('04/1/2001' AS DATE)) as dayofmonth;
 dayofmonth 

 31
(1 row)

tbs=# SET TIMEZONE = 'GMT' ;
SET VARIABLE
tbs=# SELECT DATE_PART('DAY', CAST('04/1/2001' AS DATE)) as dayofmonth;
 dayofmonth 

  1
(1 row)

Thanks for the quick response.

> -Original Message-
> From: Thomas Lockhart [mailto:[EMAIL PROTECTED]]
> Sent: Friday, May 25, 2001 9:45 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: DATE_PART() BUG? We have an SQL statement that is giving
> wrong output.
> 
> 
> > Short Description
> > DATE_PART() BUG?  We have an SQL statement that is giving 
> wrong output.
> > tbs=# SELECT version();
> > -
> >  PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
> > Here is a simple example:
> > SELECT DATE_PART('DAY', CAST('04/1/2001' AS DATE)) as dayofmonth
> > --
> >  31
> > Why does April 1st display as May 31st?
> 
> This is a known "feature" of 7.0.x (and earlier) on daylight savings
> time boundaries. You will see the problem go away if you set your time
> zone to GMT. Upgrade to 7.1.x.
> 
> - Thomas
> 

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

http://www.postgresql.org/users-lounge/docs/faq.html