CODEWITHSUNDEEP
postgresqlpsycopg2
MattP
MattP

Reputation: 2863

PostgreSQL COPY FROM csv with different date format

I am trying to upload CSV data to a PostgreSQL database in Python using the COPY FROM STDIN function.

In the CSV file my Date field is DD-MM-YYYY HH:MI and this gives me an error:

psycopg2.errors.DatetimeFieldOverflow: date/time field value out of range: "31-12-2020 08:09"

Is there a way I can define the Date/Time format when using COPY FROM?

DB column is type TIMESTAMP if relevant.

I only know how to do this with a line-by-line INSERT statement.

Upvotes: 6

Views: 7322

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19655

Just before the COPY command do:

set datestyle = euro;

show datestyle;
 DateStyle 
-----------
 ISO, DMY

Then this works:

SELECT '31-12-2020 08:09'::timestamp;
      timestamp      
---------------------
 2020-12-31 08:09:00

Otherwise with my default datestyle:

show datestyle;
 DateStyle 
-----------
 ISO, MDY

SELECT '31-12-2020 08:09'::timestamp;
ERROR:  date/time field value out of range: "31-12-2020 08:09"
LINE 1: SELECT '31-12-2020 08:09'::timestamp;

For more information see here Date input Table 8.15. Date Order Conventions

Upvotes: 5

Related Questions