Thread

1. DATE_PART() BUG? We have an SQL statement that is giving wrong output.

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-05-25T12:45:24Z

   Peter Kelly (pkelly@ets.net) reports a bug with a severity of 1
   The lower the number the more severe it is.
   
   Short Description
   DATE_PART() BUG?  We have an SQL statement that is giving wrong output.
   
   Long Description
   Here are operational details:
   
   RedHat Linux 7.0:
   
   Linux version 2.2.17-14 (root@porky.devel.redhat.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@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
   
   
   Sample Code
   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)
   );
   
   
   
   No file was uploaded with this report
   
   
   

2. Re: DATE_PART() BUG? We have an SQL statement that is giving wrong output.

   Thomas Lockhart <lockhart@fourpalms.org> — 2001-05-25T13:44:47Z

   > 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