Re: date problems

On Aug 30, 2007, at 8:01 , ivan marchesini wrote:

> a lot of table have some timestamp fields containing data as DD/MM/ 
> YYYY,
> and I'm no able to copy this table into postgres... because it needs
> YYYY/MM/DD...
> I used \copy...

The input and output formats of dates is controlled by the datestyle  
setting. You can easily change this to allow COPY (and I assume  
\copy) to load the dates in their current format.

test=# create table dates (a_date date primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"dates_pkey" for table "dates"
CREATE TABLE
test=# copy dates (a_date) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.>> 2007/08/30>> 2007/08/29>> \.

No problem loading dates in YMD.

test=# copy dates (a_date) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.>> 08/28/2007>> \.

No problem with MDY.

test=# copy dates (a_date) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.>> 27/08/2007>> \.
ERROR:  date/time field value out of range: "27/08/2007"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY dates, line 1, column a_date: "27/08/2007"

DMY fails.

test=# show datestyle;
DateStyle
-----------
ISO, MDY
(1 row)

The current datestyle is ISO for output and MDY for input. This  
explains why '27/08/2007' failed.

test=# set datestyle to 'iso, dmy'; -- output still iso, input day- 
month-year
SET
test=# show datestyle;
DateStyle
-----------
ISO, DMY
(1 row)

test=# copy dates (a_date) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.>> 27/08/2007>> \.

Since the datestyle was changed, we can now input '27/08/2007'.

test=# select * from dates;   a_date
------------
2007-08-30
2007-08-29
2007-08-28
2007-08-27
(4 rows)

And there they are: all output in ISO format.

Hope this helps.

Michael Glaesemann
grzm seespotcode net