Importing CSV file to Spanner Error (Parse Date)

Question

I started to follow the official documentation of gcp in order to import my csv data to an instance on cloud spanner spanner, but I'm getting thhis error:

 *Error java.time.format.DateTimeParseException: Text '2024-05-30THH24:16:30.892' could not be parsed at index 10*

I'm using this command to generate the csv file:

psql -h localhost -U myuser -d mydatabase -c 
"Copy (SELECT id, TO_CHAR(created_at, 'YYYY-MM-DD"T"HH24:MI:SS.MS') AS created_at FROM mytable) To STDOUT With CSV DELIMITER ',';" > /mycarpet/.csv

The table has this configuration:

id character varying(10) NOT NULL,
created_by character varying(50),

On the configuration of the job I'm setting: dateFormat=yyyy-MM-dd hh:mm:s

Any idea, how I can solve this?

Don't use a function to convert the timestamp. Just set the DateStyle to one of the Date/time forms shown at the link. — Adrian Klaver
– Adrian Klaver, Commented Aug 16, 2024 at 22:31
The Hour is 24, that's invalid data isn't it? 2024-05-30THH24:16:30.892 would be 16:30.892 past midnight as hence the date would be 2024-05-31THH00:16:30.892 — Paul Maxwell
– Paul Maxwell, Commented Aug 17, 2024 at 1:02

Knut Olav Løite · Accepted Answer · 2024-08-17 05:44:04Z

1

The problem seems to be that your export from PostgreSQL results in invalid data being generated. The import job complains that '2024-05-30THH24:16:30.892' is not a valid date/time string, which is obviously a correct conclusion. Your export seems to be writing the fixed string HH24 to the file, instead of the actual hour.

I would recommend that you manually inspect:

What is in your export file.
What the output is when you just run SELECT id, TO_CHAR(created_at, 'YYYY-MM-DD"T"HH24:MI:SS.MS') AS created_at FROM mytable in psql.

Tip: As you seem to try to copy data from a PostgreSQL database to a Spanner PostgreSQL database, you can also use PGAdapter to copy it directly without going through a csv file. See this example for how to do that.

answered Aug 17, 2024 at 5:44

Knut Olav Løite

3,57710 silver badges23 bronze badges

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

Comments

Adrian Klaver · Accepted Answer · 2024-08-17 15:45:23Z

To expand on my comment:

create table ts_test (id integer generated always as identity, ts_fld timestamptz);

insert into ts_test (ts_fld) 
select ts from 
generate_series('2024-08-17 08:31', '2024-08-18 10:01', '4 hrs 10 mins'::interval) as t(ts);

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

select * from ts_test;
 id |         ts_fld         
----+------------------------
  1 | 2024-08-17 08:31:00-07
  2 | 2024-08-17 12:41:00-07
  3 | 2024-08-17 16:51:00-07
  4 | 2024-08-17 21:01:00-07
  5 | 2024-08-18 01:11:00-07
  6 | 2024-08-18 05:21:00-07
  7 | 2024-08-18 09:31:00-07

set datestyle = 'SQL, MDY';

select * from ts_test;
 id |         ts_fld          
----+-------------------------
  1 | 08/17/2024 08:31:00 PDT
  2 | 08/17/2024 12:41:00 PDT
  3 | 08/17/2024 16:51:00 PDT
  4 | 08/17/2024 21:01:00 PDT
  5 | 08/18/2024 01:11:00 PDT
  6 | 08/18/2024 05:21:00 PDT
  7 | 08/18/2024 09:31:00 PDT

select to_char(ts_fld, 'YYYY-MM-DD"T" HH24:MI:SS.MS') from ts_test;
         to_char          
--------------------------
 2024-08-17T 08:31:00.000
 2024-08-17T 12:41:00.000
 2024-08-17T 16:51:00.000
 2024-08-17T 21:01:00.000
 2024-08-18T 01:11:00.000
 2024-08-18T 05:21:00.000
 2024-08-18T 09:31:00.000

Timestamps are not stored formatted, that is something done on presentation to the user. You can change that presentation using DateStyle or by using the Data formatting function to_char(). If the data is going to be ingested by another program any common date format should work in getting the correct information copied over.

Collectives™ on Stack Overflow

Importing CSV file to Spanner Error (Parse Date)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related