Обсуждение: 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