Re: Datetime patch - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: Datetime patch
Date
Msg-id 200307251922.h6PJMk827471@candle.pha.pa.us
Whole thread Raw
In response to Re: Datetime patch  (Bruce Momjian <pgman@candle.pha.pa.us>)
Responses Re: Datetime patch
List pgsql-patches
I did a little more looking at this...

>     test=> select '2003-09-31'::date;
>     ERROR:  Bad date external representation '2003-09-31'
>
> I did find the following change in the regression tests:
>
>   INSERT INTO TIMESTAMP_TBL VALUES ('02-10-1997 17:32:01 PST');
>   INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 PST');
>   INSERT INTO TIMESTAMP_TBL VALUES ('97FEB10 5:32:01PM UTC');
> + ERROR:  Bad timestamp external representation '97FEB10 5:32:01PM UTC'

We still accept 10FEB97, which is a more common format.  Seems this now
fails because the year/day detection is not gone, which is fine, I
think.  This works as expected:

    test=> select '01FEB03'::date;
        date
    ------------
     2003-02-01
    (1 row)

>   INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC');
> + ERROR:  Bad timestamp external representation '97/02/10 17:32:01 UTC'

Again, this one should fail.

Seems these are all related to two-digit years.  This works when
specifying the year as 1997:

test=> select '97/02/10 17:32:01 UTC'::date;
ERROR:  Bad date external representation '97/02/10 17:32:01 UTC'
test=> select '1997/02/10 17:32:01 UTC'::date;
    date
------------
 1997-02-10
(1 row)

>   INSERT INTO TIMESTAMP_TBL VALUES ('97.041 17:32:01 UTC');
> + ERROR:  Bad timestamp external representation '97.041 17:32:01 UTC'

I just played around with this one and got it working:

    test=> select '2004.041'::date;
        date
    ------------
     2004-02-10
    (1 row)

    test=> select '2004.001'::date;
        date
    ------------
     2004-01-01
    (1 row)

Seems you now need a 4-digit year to specify this format.  Also
interesting is that the day must be three digits.

As part of the patch application, I will modify the years to have the
leading '19' so the regression tests will pass.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

pgsql-patches by date:

Previous
From: Kris Jurka
Date:
Subject: Re: UPDATED Patch for adding DATACUBE operator
Next
From: Tom Lane
Date:
Subject: Re: UPDATED Patch for adding DATACUBE operator