Postgres Pro
Downloads
Home   >   mailing lists

Thread: leap day bug after 1901

leap day bug after 1901

From
José Soares
Date:
Hi all,

Seems that PostgreSQL checks for leap day only before 1902.

prova=> select date '0001-02-29';
ERROR:  date_in: day must be limited to values 1 through 28 in
'0001-02-29'

prova=> select date '1701-02-29';
ERROR:  date_in: day must be limited to values 1 through 28 in
'1701-02-29'

prova=> select date '1901-02-29';
ERROR:  date_in: day must be limited to values 1 through 28 in
'1901-02-29'

prova=> select date '1902-02-29';
  ?column?
----------
1902-03-01
(1 row)

José

Re: [GENERAL] leap day bug after 1901

From
José Soares
Date:
José Soares ha scritto:

> Hi all,
>
> Seems that PostgreSQL checks for leap day only before 1902.
>
> prova=> select date '0001-02-29';
> ERROR:  date_in: day must be limited to values 1 through 28 in
> '0001-02-29'
>
> prova=> select date '1701-02-29';
> ERROR:  date_in: day must be limited to values 1 through 28 in
> '1701-02-29'
>
> prova=> select date '1901-02-29';
> ERROR:  date_in: day must be limited to values 1 through 28 in
> '1901-02-29'
>
> prova=> select date '1902-02-29';
>   ?column?
> ----------
> 1902-03-01
> (1 row)

PostgreSQL checks for date validity only for dates less than 1902 and
greater than 2037.

hygea=> select date '1901-04-31';
ERROR:  date_in: day must be limited to values 1 through 30 in
'1901-04-31'
hygea=> select date '1902-04-31';
  ?column?
----------
1902-05-01
(1 row)

hygea=> select date '2037-04-31';
  ?column?
----------
2037-05-01
(1 row)

hygea=> select date '2038-04-31';
ERROR:  date_in: day must be limited to values 1 through 30 in
'2038-04-31'
hygea=>

I looked at ../src/backend/utils/adt/
and I saw things like...

#define MIN_DAYNUM -24856               /* December 13, 1901 */
#define MAX_DAYNUM 24854                /* January 18, 2038 */

 /* validate, before going out of range on some members */
 if (tm->tm_year < 1901 || tm->tm_year > 2038

#define UTIME_MINYEAR (1901)
#define UTIME_MAXYEAR (2038)

Any body knows what does it mean ?

José

Re: [GENERAL] leap day bug after 1901

From
José Soares
Date:
José Soares ha scritto:

> Hi all,
>
> Seems that PostgreSQL checks for leap day only before 1902.
>
> prova=> select date '0001-02-29';
> ERROR:  date_in: day must be limited to values 1 through 28 in
> '0001-02-29'
>
> prova=> select date '1701-02-29';
> ERROR:  date_in: day must be limited to values 1 through 28 in
> '1701-02-29'
>
> prova=> select date '1901-02-29';
> ERROR:  date_in: day must be limited to values 1 through 28 in
> '1901-02-29'
>
> prova=> select date '1902-02-29';
>   ?column?
> ----------
> 1902-03-01
> (1 row)

PostgreSQL checks for correct dates only for dates less than 1902 and
greater than 2037.

hygea=> select date '1900-04-31';
ERROR:  date_in: day must be limited to values 1 through 30 in
'1900-04-31'
hygea=> select date '1901-04-31';
ERROR:  date_in: day must be limited to values 1 through 30 in
'1901-04-31'
hygea=> select date '1902-04-31';
  ?column?
----------
1902-05-01
(1 row)

hygea=> select date '2037-04-31';
  ?column?
----------
2037-05-01
(1 row)

hygea=> select date '2038-04-31';
ERROR:  date_in: day must be limited to values 1 through 30 in
'2038-04-31'
hygea=> select date '2039-04-31';
ERROR:  date_in: day must be limited to values 1 through 30 in
'2039-04-31'

I took a look at
src/backend/utils/adt/
        dt.c
        datetime.c
        nabstime.c

and I saw things like...


 if (tm->tm_year < 1901 || tm->tm_year > 2038

and

#define UTIME_MINYEAR (1901)
#define UTIME_MINMONTH (12)
#define UTIME_MINDAY (14)
#define UTIME_MAXYEAR (2038)
#define UTIME_MAXMONTH (01)
#define UTIME_MAXDAY (18)

Any body knows what does it mean ?

José

Re: [GENERAL] leap day bug after 1901

From
José Soares
Date:

José Soares ha scritto:

> Hi all,
>
> Seems that PostgreSQL checks for leap day only before 1902.
>
> prova=> select date '0001-02-29';
> ERROR:  date_in: day must be limited to values 1 through 28 in
> '0001-02-29'
>
> prova=> select date '1701-02-29';
> ERROR:  date_in: day must be limited to values 1 through 28 in
> '1701-02-29'
>
> prova=> select date '1901-02-29';
> ERROR:  date_in: day must be limited to values 1 through 28 in
> '1901-02-29'
>
> prova=> select date '1902-02-29';
>   ?column?
> ----------
> 1902-03-01
> (1 row)
>
> José

Any body knows why PostgreSQL checks for date validity only
for dates less than 1902 and greater than 2037 ?

hygea=> select date '1901-04-31';
ERROR:  date_in: day must be limited to values 1 through 30 in
'1901-04-31'
hygea=> select date '1902-04-31';
  ?column?
----------
1902-05-01
(1 row)

hygea=> select date '2037-04-31';
  ?column?
----------
2037-05-01
(1 row)

hygea=> select date '2038-04-31';
ERROR:  date_in: day must be limited to values 1 through 30 in
'2038-04-31'

I looked at .../src/backend/utils/adt/ and I saw many references to
1901-2038
in files dt.c, datetime.c and nabstime.c.


Any body knows what does it mean ?


#define UTIME_MINYEAR (1901)
#define UTIME_MAXYEAR (2038)


#define MIN_DAYNUM -24856               /* December 13, 1901 */
#define MAX_DAYNUM 24854                /* January 18, 2038 */

/* validate, before going out of range on some members */
if (tm->tm_year < 1901 || tm->tm_year > 2038

Jose'

Re: [HACKERS] Re: [GENERAL] leap day bug after 1901

From
Thomas Lockhart
Date:
> > prova=> select date '1901-02-29';
> > ERROR:  date_in: day must be limited to values 1 through 28 in
> > '1901-02-29'
> > prova=> select date '1902-02-29';
> > ----------
> > 1902-03-01
> Any body knows why PostgreSQL checks for date validity only
> for dates less than 1902 and greater than 2037 ?

Yes (and no :) Unix system time goes from ~1902 to ~2038, and the time
zone database included with your system can only work in that range.
So I use system routines for dates within that range and bypass those
(and do not assign time zones) outside that range.

However, I'm puzzled as to why these checks don't seem to be effective
when using dates within this range. Will look at it, and post patches.

Thanks for the report.

                    - Tom

--
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Re: [HACKERS] Re: [GENERAL] leap day bug after 1901

From
Thomas Lockhart
Date:
> > > prova=> select date '1901-02-29';
> > > ERROR:  date_in: day must be limited to values 1 through 28
> > > prova=> select date '1902-02-29';
> > > ----------
> > > 1902-03-01
> > Any body knows why PostgreSQL checks for date validity only
> > for dates less than 1902 and greater than 2037 ?

postgres=> select date '1902-02-29 UTC';
ERROR:  date_in: day must be limited to values 1 through 28

For dates somewhere close the the Unix system time range, I use system
routines to determine the local time zone if it was not specified in
the input. That call also shifts/resolves the input time fields to be
consistant. Afterwards, I check for a valid day, but the shift has
already hidden the problem.

I'll look at moving the checks to within the parsing code, to just
before the time zone is resolved.

This will also affect the behavior of datetime and abstime.

                    - Tom

--
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California