Thread: Default date format to ISO + 1 bug

Hello,

since I am a simple Postgres user, I don't understand the instruction to
change the postgresql default date format from 01-13-2000 to 2000-01-13

The timestamp format is OK with : 2000-01-13 09:11:24-05 but
Date gives : month-day-year

For Postgres v6.5 (and earlier) the default date/time style is
"non-European traditional Postgres" (I guess that means that timestamp
does not follow this default format). How do I change this?

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 !!!!!!

(yes, my PC has the rigth date!)

When I create a new table:
insert into toto2 (num int2, name varchar(16), date_insc timestamp
default now());

I make an insert: insert into toto values (1,'mapaquin');
it gives me:     1|mapaquin|2000-01-13 09:11:24-05

and now, it's OK!!!!

What is wrong?

Thank's!
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Marc Andre Paquin

On Thu, Jan 13, 2000 at 10:09:35AM -0500, Web Manager wrote:
> since I am a simple Postgres user, I don't understand the instruction to
> change the postgresql default date format from 01-13-2000 to 2000-01-13
>
> The timestamp format is OK with : 2000-01-13 09:11:24-05 but
> Date gives : month-day-year
>
> For Postgres v6.5 (and earlier) the default date/time style is
> "non-European traditional Postgres" (I guess that means that timestamp
> does not follow this default format). How do I change this?

you can use the set command to change the datestyle:
SET DATESTYLE TO 'ISO'|'SQL'|'Postgres'|'European'|'US'|'NonEuropean'

i set mine to 'ISO' which is 'yyyy-mm-dd hh:mm:ss-tz'

to make it permanent, you can set the environment variable PGDATESTYLE
in your startup script.

--
[ Jim Mercer                 jim@reptiles.org              +1 416 506-0654 ]
[          Reptilian Research -- Longer Life through Colder Blood          ]
[  Don't be fooled by cheap Finnish imitations; BSD is the One True Code.  ]

On Thu, Jan 13, 2000 at 10:09:35AM -0500, Web Manager wrote:
> Hello,
>
> since I am a simple Postgres user, I don't understand the instruction to
> change the postgresql default date format from 01-13-2000 to 2000-01-13

Either set the environment variable 'PGDATESTYLE', eg for csh

setenv PGDATESTYLE European

or in your connection to the database

SET DateStyle TO 'European';

> And then... I have a second broblem:
... 'now' broken, now() working
> What is wrong?

I think this one was mentioned a few days ago, so it's a "known problem"?

Cheers,

Patrick

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_ALLto your proper locale for sorting etc. 

/Palle