Thread

1. date bug (again)

   Darcy Buskermolen <darcy@ok-connect.com> — 2000-08-15T05:38:18Z

   let me try this again...
   
   
   Here is an other set of strange bugs I've discovered relating to dates.
   
   this bug only rears it's head for 1 day a year form what I can find..
   october 31'st, the intent of the queries is to get the last day of a month..
   
   
   
   
   SELECT date_part('day', ('2000-11-1 0:00'::datetime + '-1day'))::int4 AS
   days_in_month;
   
   days_in_month
   --------------
     31
   (1 row)
   
   
   SELECT date_part('day', (('2000-10-1 0:00'::datetime + '1 month') + '-1
   day'))::int4 AS days_in_month;
   
   days_in_month
   --------------
     30
   (1 row)
   
   
   SELECT date_part('day', date_trunc('month', ('2000-10-1 0:00'::datetime +
   '1 month')) + '-1 day')::int4 AS days_in_month;
   
   days_in_month
   --------------
     30
   (1 row)
   
   
   

2. Re: date bug (again)

   Philip Warner <pjw@rhyme.com.au> — 2000-08-15T05:52:39Z

   At 22:38 14/08/00 -0700, Darcy Buskermolen wrote:
   >let me try this again...
   >
   >
   >Here is an other set of strange bugs I've discovered relating to dates.
   >
   >SELECT date_part('day', ('2000-11-1 0:00'::datetime + '-1day'))::int4 AS
   >days_in_month;
   >
   >  31
   >
   >
   >SELECT date_part('day', (('2000-10-1 0:00'::datetime + '1 month') + '-1
   >day'))::int4 AS days_in_month;
   >
   >  30
   >
   >SELECT date_part('day', date_trunc('month', ('2000-10-1 0:00'::datetime +
   >'1 month')) + '-1 day')::int4 AS days_in_month;
   >
   >  30
   >(1 row)
   
   FWIW, with 7.0.2 under Linux, I get 31 in each case.
   
   
   ----------------------------------------------------------------
   Philip Warner                    |     __---_____
   Albatross Consulting Pty. Ltd.   |----/       -  \
   (A.B.N. 75 008 659 498)          |          /(@)   ______---_
   Tel: (+61) 0500 83 82 81         |                 _________  \
   Fax: (+61) 0500 83 82 82         |                 ___________ |
   Http://www.rhyme.com.au          |                /           \|
                                    |    --________--
   PGP key available upon request,  |  /
   and from pgp5.ai.mit.edu:11371   |/
   
   

3. Re: date bug (again)

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-19T02:59:16Z

   Darcy Buskermolen <darcy@ok-connect.com> writes:
   > SELECT date_part('day', (('2000-10-1 0:00'::datetime + '1 month') + '-1
   > day'))::int4 AS days_in_month;
   
   > days_in_month
   > --------------
   >   30
   > (1 row)
   
   This is not a bug, or at least it's not entirely clear what the behavior
   ought to be.  The issue is what happens at a daylight-savings
   transition.  The results I get (US Eastern timezone) are
   
   regression=# select '2000-10-1 0:00'::timestamp;
           ?column?
   ------------------------
    2000-10-01 00:00:00-04
   (1 row)
   
   regression=# select '2000-10-1 0:00'::timestamp + '1 month';
           ?column?
   ------------------------
    2000-10-31 23:00:00-05
   (1 row)
   
   See what's happening?  You get a result that's exactly 31 days times
   24 hours later, but that date_trunc()'s down to only 30 days.  A
   finer-grain example is
   
   regression=# select '2000-10-29 0:00'::timestamp ;
           ?column?
   ------------------------
    2000-10-29 00:00:00-04
   (1 row)
   
   regression=# select '2000-10-29 0:00'::timestamp + '1 day';
           ?column?
   ------------------------
    2000-10-29 23:00:00-05
   (1 row)
   
   The real question is whether "+ 1 day" ought to mean "+ 24 hours"
   or not, and if not what it *should* mean...
   
   			regards, tom lane