PostgreSQL COPY Command – Importing Datetime Fields in ‘m/dd/yyyy’ Format

datetimepostgresql

I'm looking to import a series of CSV files that contain a datetime field in the format of m/dd/yyyy h:mm:ss into a PostgreSQL table using the native /copy command.

I get the following error on import:

ERROR: date/time field value out of range: "5/13/2010 0:00:00"
HINT: Perhaps you need a different "datestyle" setting.

I tried setting the datestyle setting to ISO,MDY and SQL,MDY to no avail.I also attempted to modify the field type in the target table to character to force the fields to be read as strings, but that didn't work either.

Any ideas? Ideally I would like to have the data directly read into the target table as dates, but would settle for character strings. Thanks in advance!

Test Case

PostgreSQL Target Table:

CREATE TABLE testtable
(
    count_date timestamp without time zone,
    location character(40),
    count smallint
)
WITH (
    OIDS=TRUE
);

Data:

"count_date","location","count"
5/12/2010 0:00:00,"SEGMENT A",50
5/12/2010 0:00:00,"SEGMENT B",40
5/12/2010 0:00:00,"SEGMENT C",30
5/13/2010 0:00:00,"SEGMENT A",20
5/13/2010 0:00:00,"SEGMENT B",10
5/13/2010 0:00:00,"SEGMENT C",5

Best Answer

I'd recommend always using ISO date format for any kind of data interchange.

Consider Google's Crush Tools convdate for a quick fix:

  convdate --delim ',' --field 1 --preserve_header --input_format '%m/%d/%Y %I:%M:%S' --output_format '%Y-%m-%dT%H:%M:%S' < myData.csv |
  psql -c '\copy foo from stdin with (format csv, header true)'

0:00:00 is probably not a valid time though. Use csvquote if your data has embedded commas or newlines.

Related Question