Обсуждение: leap day bug after 1901
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é
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é
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é
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'
> > 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
> > > 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