Datestyle issue in postgresql

Question

Using: postgresql
Table name: my_data

I have the following column current_time of type timestamp in my table. The inputs to this field looks like this:

"4/26/2014 5:27:48 PM"

I'm reading this table from a csv file and I'm getting the following error:

ERROR:  date/time field value out of range: "4/25/2014 5:29:37 PM"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY my_data, line 2, column current_time: "4/25/2014 5:29:37 PM"

I looked for solution and tried this, but it couldn't help me:

set datestyle = "ISO, DMY";

Please help me on how to read this file in a proper timestamp format.

Answer 1

Your example uses the MDY (Month before Day) styled date. So you would need something like

SET DateStyle TO MDY;

But generally, it's more safe to use the ISO format. Try to pass that format first, and if you can't you could use the to_timestamp() function for converting between formats.

SELECT to_timestamp('4/25/2014 5:29:37 PM', 'MM/DD/YYYY HH:MI:SS AM');