Re: [GENERAL] Default date format to ISO + 1 bug

Web Manager wrote:
>
> And then... I have a second broblem:
> I have postgres version 6.4.2
>
> I created a new testing db.
> createdb test
> create table toto (num int2, name varchar(16), date_insc date);
>
> When I : insert into toto values (1,'mapaquin',date('now'));
> it give me : 1|mapaquin|12-31-1999
>
> BUT IT IS JAN 13th !!!!!!

Here's with 6.5.2:

girgen=> select date('now');
date
----------
2000-01-01 <----- not quite right ;-)
(1 row)

girgen=> select 'now'::date;
?column?
----------
2000-01-13
(1 row)

girgen=> select date('now'::date);
date
----------
2000-01-13
(1 row)

girgen=> select date('1999-04-01 15:38:15'::date);
date
----------
1999-04-01
(1 row)

It seems, the date() function can't take a string, it needs a date/time type of some sort? This is the cleanest way, probably:

insert into toto values (1,'mapaquin','now');

You can also use 'now'::date with date(), like date('now'::date)...

date() is probably broken in a way; it gives 2000-01-01 for anything it doesn't understand:

pp=> select date(1072842322);
date
----------
2003-12-31
(1 row)

pp=> select date(10728423224); <----- too large number
date
----------
2000-01-01
(1 row)

Maybe this is better than failing, I'm not sure...

PS. I have the line

PGDATESTYLE=ISO; export PGDATESTYLE

in my ~pgsql/.profile, which gives ISO dates, as previously pointed out. You also probably want to set LC_COLLATE or LC_ALL to your proper locale for sorting etc.

/Palle