How can I import TIMESTAMP into POSTGRES without seconds? Coming from large CSV

Question

Please excuse the newbie POSTGRES question, I'm a little more familiar with MSMSQL, but I'd probably struggle there too.

I have a CSV with approx 12 million rows I am trying to import. One of the fields is a date field in the format "YYYY-MM-DD HH:MM".

My table has the field type set to TIMESTAMP (is this right?). The CSV import fails, stating "ERROR: date/time field value out of range: "2019-20-09 04:00"".

How can I import this type of date string?

My current statements look like:

CREATE TABLE WoSWeatherGrids
(
    id serial NOT NULL,
    Location NUMERIC(6),
    Date TIMESTAMP,
)

COPY WosWeatherGrids(Location,Date) 
FROM '\\location\Weathergrids.csv' DELIMITER ',' CSV HEADER;

The error received is:

ERROR:  date/time field value out of range: "2019-20-09 04:00"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY wosweathergrids, line 2, column date: "2019-20-09 04:00"
SQL state: 22008

I'm not able to change the strings in the CSV, as this is a process that runs automatically from the output of another piece of software that will hopefully occur a couple of times per day. I'm hoping there's a simple method that can be highlighted to allow me to import this string type.

CSV Sample type

Location,Date
1075,2019-20-09 04:00
1075,2019-20-09 05:00
1075,2019-20-09 06:00
1075,2019-20-09 07:00

EDIT: The issue seems to be extending from the fact that Postgres doesn't like YDM DateStyle format. One of the original systems is using MS SQL, and T-SQL can handle that. I cannot modify that system, so still need to deal with this issue.

EDIT2: per the documentation here I've been able to cast an example YYYY-DD-MM HH:SS date type to what I need using the following. select to_char(to_timestamp('2019-20-09 04:00', 'YYYY-DD-MM HH24:MI'), 'HH24:MI DD/MM/YYYY');. I'm still somewhat stuck in that I need to either understand how to do this from the input of the CSV, or how to create a temp table, and then a proper table and cast on the way across.

What Is your datestyle?, You must change the datestyle to YDM, the hints Is clear HINT: Perhaps you need a different "datestyle" setting — Anthony Sotolongo
– Anthony Sotolongo, Commented Sep 23, 2019 at 3:18
Good catch. However, YDM is not a datestyle in postgres, apparently. ERROR: invalid value for parameter "DateStyle": "ISO, YDM" when using SET datestyle to "ISO, YDM" — anakaine
– anakaine, Commented Sep 23, 2019 at 3:41

Laurenz Albe · Accepted Answer · 2019-09-23 05:32:51Z

3

You'll have to set datestyle to ISO, DMY or similar. The important part here is the ISO, since your timestamp is (almost) in ISO format.

answered Sep 23, 2019 at 5:32

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anthony Sotolongo · Accepted Answer · 2019-09-23 13:41:35Z

2

create a temp column(fec) and after update the date column, example:

CREATE  TABLE WoSWeatherGrids
 (
   id serial NOT NULL,
   Location NUMERIC(6),
   Date TIMESTAMP,
   fec text
 )

COPY WosWeatherGrids(Location,fec) 
FROM '/tmp/datos.csv' DELIMITER ',' CSV HEADER;

update WoSWeatherGrids set date = to_timestamp(fec,'YYYY-DD-MM HH:MI');

alter TABLE WoSWeatherGrids drop COLUMN fec;

answered Sep 23, 2019 at 13:41

Anthony Sotolongo

1,6882 gold badges13 silver badges23 bronze badges

1 Comment

anakaine Over a year ago

This is the best answer as it doesn't require modifying the way that dates are handled

AresGoWs · Accepted Answer · 2023-03-24 00:54:37Z

2

I ran into the same problem. I fixed it by turning on the "Header" in Options section. It may have interpreted the first line of your CSV file as data rather than column headers.

answered Mar 24, 2023 at 0:54

AresGoWs

211 bronze badge

Comments

Mauricio · Accepted Answer · 2023-11-16 02:47:14Z

0

Inside the postgresql shell. First run:

SET datestyle TO iso, YMD;

Then, run the csv command:

\copy test_table from 'path-to/data-table.csv' WITH DELIMITER ',' CSV HEADER;

answered Nov 16, 2023 at 2:47

Mauricio

3,2274 gold badges37 silver badges56 bronze badges

Collectives™ on Stack Overflow

How can I import TIMESTAMP into POSTGRES without seconds? Coming from large CSV

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related