Skip site navigation (1) Skip section navigation (2)


From: Peter Kelly <pkelly(at)ETS(dot)NET>
To: "'pgsql-bugs(at)postgresql(dot)org'" <pgsql-bugs(at)postgresql(dot)org>
Cc: "'gord(at)jdp(dot)com'" <gord(at)jdp(dot)com>, "'drew(at)ss(dot)org'" <drew(at)ss(dot)org>
Subject: DATE_PART() BUG?
Date: 2001-05-25 12:42:02
Message-ID: (view raw or whole thread)
Lists: pgsql-bugs
Here are operational details:

RedHat Linux 7.0:

Linux version 2.2.17-14 (root(at)porky(dot)devel(dot)redhat(dot)com) (gcc version
egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)) #1 Mon Feb 5 15:25:12 EST

PostgreSQL Version info:


tbs=# SELECT 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(at)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


We have an SQL statement that is giving wrong output.

Here is an example of the whole statement:

	*, 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) <
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
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
        "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)


Peter Kelly, ETS.NET Inc.

Email:		mailto:pkelly(at)ets(dot)net
PGP Key:
Phone:		905-713-9978
Fax: 		905-726-8118

Visit us at!

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

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2001-05-25 12:45:24
Subject: DATE_PART() BUG? We have an SQL statement that is giving wrong output.
Previous:From: Oleg BartunovDate: 2001-05-25 09:39:43
Subject: Re: Re: rfd: multi-key GiST index problems

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group