Обсуждение: HINT: Perhaps you need a different "datestyle" setting - postgresql
Hi, I am actually trying to copy data from my csv file to DB. But I am getting the DateStyle error. I tried different ways to make it workable. But no luck! prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV DELIMITER ',' HEADER ; ERROR: date/time field value out of range: " 19/08//2014" HINT: Perhaps you need a different "datestyle" setting. CONTEXT: COPY orders, line 2, column delivery_date: " 19/08//2014" prac_db=# SET datestyle = "ISO"; SET prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV DELIMITER ',' HEADER ; ERROR: date/time field value out of range: " 19/08//2014" HINT: Perhaps you need a different "datestyle" setting. CONTEXT: COPY orders, line 2, column delivery_date: " 19/08//2014" prac_db=# SET datestyle = "ISO, IS"; ERROR: invalid value for parameter "DateStyle": "ISO, IS" DETAIL: Unrecognized key word: "is". prac_db=# SET datestyle = "ISO, IS 7900:2001"; ERROR: invalid value for parameter "DateStyle": "ISO, IS 7900:2001" DETAIL: List syntax is invalid. prac_db=# SET datestyle = "ISO, BIS IS"; ERROR: invalid value for parameter "DateStyle": "ISO, BIS IS" DETAIL: List syntax is invalid. prac_db=# SET datestyle = "ISO, BIS IS 7900:2001"; ERROR: invalid value for parameter "DateStyle": "ISO, BIS IS 7900:2001" DETAIL: List syntax is invalid. prac_db=# My Data style inside the CSV exactly like - http://en.wikipedia.org/wiki/Date_and_time_notation_in_India. How to fix this problem? -- ================ Regards, Arup Rakshit ================ Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan
Hi
try postgres=# set datestyle to DMY;
SET
postgres=# SELECT '19/08/2014'::date;
date
------------
2014-08-19
(1 row)
DEFAULT EUROPEAN ISO NONEUROPEAN SQL YMD
DMY GERMAN MDY POSTGRES US
postgres=# set datestyle to SQL,DMY;
SET
postgres=# SELECT '19/08/2014'::date;
date
------------
19/08/2014
(1 row)
Regards
Pavel Stehule
Pavel Stehule
2014-12-28 11:43 GMT+01:00 Arup Rakshit <aruprakshit@rocketmail.com>:
Hi,
I am actually trying to copy data from my csv file to DB. But I am getting the
DateStyle error.
I tried different ways to make it workable. But no luck!
prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV
DELIMITER ',' HEADER ;
ERROR: date/time field value out of range: " 19/08//2014"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: COPY orders, line 2, column delivery_date: " 19/08//2014"
prac_db=# SET datestyle = "ISO";
SET
prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV
DELIMITER ',' HEADER ;
ERROR: date/time field value out of range: " 19/08//2014"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: COPY orders, line 2, column delivery_date: " 19/08//2014"
prac_db=# SET datestyle = "ISO, IS";
ERROR: invalid value for parameter "DateStyle": "ISO, IS"
DETAIL: Unrecognized key word: "is".
prac_db=# SET datestyle = "ISO, IS 7900:2001";
ERROR: invalid value for parameter "DateStyle": "ISO, IS 7900:2001"
DETAIL: List syntax is invalid.
prac_db=# SET datestyle = "ISO, BIS IS";
ERROR: invalid value for parameter "DateStyle": "ISO, BIS IS"
DETAIL: List syntax is invalid.
prac_db=# SET datestyle = "ISO, BIS IS 7900:2001";
ERROR: invalid value for parameter "DateStyle": "ISO, BIS IS 7900:2001"
DETAIL: List syntax is invalid.
prac_db=#
My Data style inside the CSV exactly like -
http://en.wikipedia.org/wiki/Date_and_time_notation_in_India. How to fix this
problem?
--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.
--Brian Kernighan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sunday, December 28, 2014 12:54:30 PM Pavel Stehule wrote: > Hi > > try > > postgres=# set datestyle to DMY; > SET > postgres=# SELECT '19/08/2014'::date; > date > ------------ > 2014-08-19 > (1 row) > > Postgres supports following styles only: > > DEFAULT EUROPEAN ISO NONEUROPEAN SQL > YMD > DMY GERMAN MDY POSTGRES US > > or more exactly: > > postgres=# set datestyle to SQL,DMY; > SET > postgres=# SELECT '19/08/2014'::date; > date > ------------ > 19/08/2014 > (1 row) > > > http://www.postgresql.org/docs/9.4/static/datatype-datetime.html Thanks for the link. I was following this - http://dba.stackexchange.com/questions/19679/how-to-set-postgresql-database-to-see-date-as-mdy-permanently. This actuallylead me to those combinations that I had already tried. Now I have another problem: prac_db=# SET datestyle = "SQL, DMY"; SET prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV DELIMITER ',' HEADER ; ERROR: time zone displacement out of range: " 9-25 AM" CONTEXT: COPY orders, line 2, column delivery_time: " 9-25 AM" prac_db=# ======= ERROR: time zone displacement out of range: " 9-25 AM" ======= How to fix this ? prac_db=# \d orders ; Table "public.orders" Column | Type | Modifiers ------------------+------------------------+----------------------------------------------------------- order_id | integer | not null default nextval('orders_order_id_seq'::regclass) delivery_address | text | order_date | date | delivery_date | date | status | text | delivery_time | time without time zone | courier_id | integer | Indexes: "orders_pkey" PRIMARY KEY, btree (order_id) \d: extra argument ";" ignored prac_db=# -- ================ Regards, Arup Rakshit ================ Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan
2014-12-28 12:06 GMT+01:00 Arup Rakshit <aruprakshit@rocketmail.com>:
On Sunday, December 28, 2014 12:54:30 PM Pavel Stehule wrote:
> Hi
>
> try
>
> postgres=# set datestyle to DMY;
> SET
> postgres=# SELECT '19/08/2014'::date;
> date
> ------------
> 2014-08-19
> (1 row)
>
> Postgres supports following styles only:
>
> DEFAULT EUROPEAN ISO NONEUROPEAN SQL
> YMD
> DMY GERMAN MDY POSTGRES US
>
> or more exactly:
>
> postgres=# set datestyle to SQL,DMY;
> SET
> postgres=# SELECT '19/08/2014'::date;
> date
> ------------
> 19/08/2014
> (1 row)
>
>
> http://www.postgresql.org/docs/9.4/static/datatype-datetime.html
Thanks for the link. I was following this -
http://dba.stackexchange.com/questions/19679/how-to-set-postgresql-database-to-see-date-as-mdy-permanently. This actually lead me to those combinations
that I had already tried.
Now I have another problem:
prac_db=# SET datestyle = "SQL, DMY";
SET
prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV
DELIMITER ',' HEADER ;
ERROR: time zone displacement out of range: " 9-25 AM"
CONTEXT: COPY orders, line 2, column delivery_time: " 9-25 AM"
prac_db=#
=======
ERROR: time zone displacement out of range: " 9-25 AM"
=======
hmm this format is strange - I don't know how to fix it in Postgres
some possible solution:
1. fix export to use some Postgres well known format - ISO YYYY-MM-DD HH:MM:SS +TZ
2. Import to varchar column with later recoding
3. Do some pretransformations CSV file before import to Postgres
Regards
Pavel
Pavel
How to fix this ?
prac_db=# \d orders ;
Table "public.orders"
Column | Type | Modifiers
------------------+------------------------+-----------------------------------------------------------
order_id | integer | not null default
nextval('orders_order_id_seq'::regclass)
delivery_address | text |
order_date | date |
delivery_date | date |
status | text |
delivery_time | time without time zone |
courier_id | integer |
Indexes:
"orders_pkey" PRIMARY KEY, btree (order_id)
\d: extra argument ";" ignored
prac_db=#
--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.
--Brian Kernighan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> On 28 Dec 2014, at 12:06, Arup Rakshit <aruprakshit@rocketmail.com> wrote: > > Now I have another problem: > > prac_db=# SET datestyle = "SQL, DMY"; > SET > prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV > DELIMITER ',' HEADER ; > ERROR: time zone displacement out of range: " 9-25 AM" > CONTEXT: COPY orders, line 2, column delivery_time: " 9-25 AM" > prac_db=# > > ======= > ERROR: time zone displacement out of range: " 9-25 AM" > ======= > > How to fix this ? Shouldn’t that read "9:25 AM”, with a colon instead of a dash? If that’s the case, fix your data before feeding it to yourorders table. If not, what is “9-25 AM" supposed to mean? A timezone offset (which is what +/- means in a time) of more than +/-12 hoursmakes no sense on this planet. Neither does a time-range, especially not with the AM addition. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
On Sunday, December 28, 2014 01:24:00 PM you wrote: > > > On 28 Dec 2014, at 12:06, Arup Rakshit <aruprakshit@rocketmail.com> wrote: > > > > Now I have another problem: > > > > prac_db=# SET datestyle = "SQL, DMY"; > > SET > > prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV > > DELIMITER ',' HEADER ; > > ERROR: time zone displacement out of range: " 9-25 AM" > > CONTEXT: COPY orders, line 2, column delivery_time: " 9-25 AM" > > prac_db=# > > > > ======= > > ERROR: time zone displacement out of range: " 9-25 AM" > > ======= > > > > How to fix this ? > > Shouldn’t that read "9:25 AM”, with a colon instead of a dash? If that’s the case, fix your data before feeding it to yourorders table. Data came that way. > > If not, what is “9-25 AM" supposed to mean? A timezone offset (which is what +/- means in a time) of more than +/-12 hoursmakes no sense on this planet. Neither does a time-range, especially not with the AM addition. No it is not a range. It is 9 hours 25 minutes( morning). :) > Alban Hertroys -- ================ Regards, Arup Rakshit ================ Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible,you are, by definition, not smart enough to debug it. --Brian Kernighan