Re: HINT: Perhaps you need a different "datestyle" setting - postgresql

2014-12-28 12:06 GMT+01:00 Arup Rakshit <aruprakshit(at)rocketmail(dot)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

>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>