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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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."
From | Date | Subject | |
---|---|---|---|
Next Message | pgsql-bugs | 2001-05-25 12:45:24 | DATE_PART() BUG? We have an SQL statement that is giving wrong output. |
Previous Message | Oleg Bartunov | 2001-05-25 09:39:43 | Re: Re: rfd: multi-key GiST index problems |