From: Michael Glaesemann <grzm@seespotcode.net>
To: marchesini@unipg.it
Cc: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: date problems
Date: Thu, 30 Aug 2007 09:39:47 -0500
Message-ID: <39419CF6-0708-46BE-9C90-2CDC950313F4@seespotcode.net> (raw)
In-Reply-To: <1188478863.4876.23.camel@ivangeo>
References: <1188478863.4876.23.camel@ivangeo>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net, marchesini@unipg.it
Subject: Re: date problems
In-Reply-To: <39419CF6-0708-46BE-9C90-2CDC950313F4@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox