Thread: formatting a date when some nulls exist

Hi All,
I'm trying to format a date type column, which contains some nulls.  I'm
getting an error:

ewtest=# select to_char(birthday, 'YYYY-MM-DD') from contacts;
ERROR:  Unable to convert date to tm

I'm assuming that the error is because of the null values.  If I
constrain the query to a record which I know has a valid date, it
returns the formatted date as I would expect:

ewtest=# select to_char(birthday, 'YYYY-MM-DD') from contacts where
fn='Rhys';
   to_char
------------
  1943-10-18
(1 row)

I suspect that I need to COALESCE or CASE my way out of this, but I
haven't been able to figure out how.  I'm hoping someone out there can
tell me how to return the formatted date or null or an empty string.

Thanks for any help!

Eric.

Eric Walstad <eric@walstads.net> writes:
> I'm trying to format a date type column, which contains some nulls.  I'm
> getting an error:

> ewtest=# select to_char(birthday, 'YYYY-MM-DD') from contacts;
> ERROR:  Unable to convert date to tm

How old is your Postgres??  AFAIR that misbehavior has been gone since
6.5 or so.  Certainly any recent version works:

regression=# create table foo (f1 date);
CREATE TABLE
regression=# insert into foo values('today');
INSERT 288761 1
regression=# insert into foo values(null);
INSERT 288762 1
regression=# select to_char(f1, 'YYYY-MM-DD') from foo;
  to_char
------------
 2002-09-02

(2 rows)

regression=#

            regards, tom lane

Hi Tom,

Here's the version:

ewtest=# select version();
                            version
-------------------------------------------------------------
  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

...and...
[ewalstad@uluwatu ewalstad]$ uname -a
Linux uluwatu 2.4.18-10 #1 Wed Aug 7 11:39:21 EDT 2002 i686 unknown

Thanks for your time and assistance!

Eric.

Tom Lane wrote:
> Eric Walstad <eric@walstads.net> writes:
>
>>I'm trying to format a date type column, which contains some nulls.  I'm
>>getting an error:
>
>
>>ewtest=# select to_char(birthday, 'YYYY-MM-DD') from contacts;
>>ERROR:  Unable to convert date to tm
>
>
> How old is your Postgres??  AFAIR that misbehavior has been gone since
> 6.5 or so.  Certainly any recent version works:
>
> regression=# create table foo (f1 date);
> CREATE TABLE
> regression=# insert into foo values('today');
> INSERT 288761 1
> regression=# insert into foo values(null);
> INSERT 288762 1
> regression=# select to_char(f1, 'YYYY-MM-DD') from foo;
>   to_char
> ------------
>  2002-09-02
>
> (2 rows)
>
> regression=#
>
>             regards, tom lane

Eric Walstad <eric@walstads.net> writes:
> [ewalstad@uluwatu ewalstad]$ uname -a
> Linux uluwatu 2.4.18-10 #1 Wed Aug 7 11:39:21 EDT 2002 i686 unknown

Ah, you're using the new-and-"improved" glibc.  I'll bet you have dates
in your table that precede 1/1/1970?  The glibc boys decided (quite
arbitrarily) that mktime(3) should stop supporting pre-1970 dates, and
that in turn broke a number of Postgres operations.

I just today committed a fix that works around this problem.  It'll
be in PG 7.3 if it survives beta testing.  I'm afraid I don't have any
very good answer for 7.2 ... but do complain to your Linux distributor
that mktime() is broken.  Because it is, and the glibc authors need to
hear about it often enough to realize that they made a stupid decision.

            regards, tom lane

Tom,

Thanks for your help.  I'll send RH a note.
FWIW, I got around the problem by doing this:
SELECT EXTRACT(YEAR FROM birthday) || '-' || LPAD(EXTRACT(MONTH FROM
birthday), 2, '0') || '-' || LPAD(EXTRACT(DAY FROM birthday), 2, '0') AS
bday FROM ewtest;

Which is really ugly, but works for me for now.

Thanks again,

Eric.

Tom Lane wrote:
> Eric Walstad <eric@walstads.net> writes:
>
>>[ewalstad@uluwatu ewalstad]$ uname -a
>>Linux uluwatu 2.4.18-10 #1 Wed Aug 7 11:39:21 EDT 2002 i686 unknown
>
>
> Ah, you're using the new-and-"improved" glibc.  I'll bet you have dates
> in your table that precede 1/1/1970?  The glibc boys decided (quite
> arbitrarily) that mktime(3) should stop supporting pre-1970 dates, and
> that in turn broke a number of Postgres operations.
>
> I just today committed a fix that works around this problem.  It'll
> be in PG 7.3 if it survives beta testing.  I'm afraid I don't have any
> very good answer for 7.2 ... but do complain to your Linux distributor
> that mktime() is broken.  Because it is, and the glibc authors need to
> hear about it often enough to realize that they made a stupid decision.
>
>             regards, tom lane

Tom,
Thanks for your help.  I'll send RH a note.
FWIW, I got around the problem by doing this:
SELECT EXTRACT(YEAR FROM birthday) || '-' || LPAD(EXTRACT(MONTH FROM
birthday), 2, '0') || '-' || LPAD(EXTRACT(DAY FROM birthday), 2, '0') AS
bday FROM ewtest;

Which is really ugly, but works for me for now.

Thanks again,

Eric.

Tom Lane wrote:
> Eric Walstad <eric@walstads.net> writes:
>
>>[ewalstad@uluwatu ewalstad]$ uname -a
>>Linux uluwatu 2.4.18-10 #1 Wed Aug 7 11:39:21 EDT 2002 i686 unknown
>
>
> Ah, you're using the new-and-"improved" glibc.  I'll bet you have dates
> in your table that precede 1/1/1970?  The glibc boys decided (quite
> arbitrarily) that mktime(3) should stop supporting pre-1970 dates, and
> that in turn broke a number of Postgres operations.
>
> I just today committed a fix that works around this problem.  It'll
> be in PG 7.3 if it survives beta testing.  I'm afraid I don't have any
> very good answer for 7.2 ... but do complain to your Linux distributor
> that mktime() is broken.  Because it is, and the glibc authors need to
> hear about it often enough to realize that they made a stupid decision.
>
>             regards, tom lane