to_char(date,'YYYYMMDD') bug

Started by Jord Tannerover 23 years ago2 messagesbugs
Jump to latest
#1Jord Tanner
jord@indygecko.com

Greetings,

It looks like to_char is unable to convert dates that fall in the range
of 01/01/1902 to 12/31/1969 (see below). I am running the Postgresql RPM
that is distributed with RH 8.0 (7.2.2.1). PGDATESTYLE is set to "SQL".
I find to_char behaves correctly on my RH 7.2 box with Postgresql 7.2
compiled from source.

Are there any known workarounds, or should I try compiling 7.2.3 from
source on my RH 8 box?

Jord Tanner
Independent Gecko Consultants
jord@nospam-indygecko.com

gbi=# select to_char('12/31/1969'::date, 'YYYYMMDD');
ERROR: Unable to convert date to tm
gbi=# select to_char('01/01/1970'::date, 'YYYYMMDD');
to_char
----------
19700101
(1 row)

gbi=# select to_char('01/01/1900'::date, 'YYYYMMDD');
to_char
----------
19000101
(1 row)

gbi=# select to_char('01/01/1930'::date, 'YYYYMMDD');
ERROR: Unable to convert date to tm
gbi=# select to_char('01/01/1920'::date, 'YYYYMMDD');
ERROR: Unable to convert date to tm
gbi=# select to_char('01/01/1910'::date, 'YYYYMMDD');
ERROR: Unable to convert date to tm
gbi=# select to_char('01/01/1901'::date, 'MMDDYYYY');
to_char
----------
01011901
(1 row)

gbi=# select to_char('01/01/1902'::date, 'MMDDYYYY');
ERROR: Unable to convert date to tm

gbi=# select to_char('01/01/2000'::date, 'YYYYMMDD');
to_char
----------
20000101
(1 row)

gbi=# select version();

version
---------------------------------------------------------------------------------------------------------
PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jord Tanner (#1)
Re: to_char(date,'YYYYMMDD') bug

Jord Tanner <jord@indygecko.com> writes:

It looks like to_char is unable to convert dates that fall in the range
of 01/01/1902 to 12/31/1969 (see below). I am running the Postgresql RPM
that is distributed with RH 8.0 (7.2.2.1). PGDATESTYLE is set to "SQL".

You need PG 7.2.3; it works around the glibc boys' decision to
deliberately break mktime() for pre-1970 dates. If you're not happy
about that (and you shouldn't be), complain to them.

regards, tom lane