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

DATE_PART() BUG?

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: 81568ACE3F6BD41189D90050046EB17A093DFC@bart.int.ets.net (view raw or flat)
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
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(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

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:pkelly(at)ets(dot)net
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."

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-2014 The PostgreSQL Global Development Group