TIMESTAMP_WITH_TIMEZONE import error

[andyinspace2]

I have a csv I'm trying to import with Datetimes in this format:
"2021-05-28T21:34:25.000Z"

I'm using SQL Workbench Build 128 and the following command:

WbImport -type=text
-file='20220720.csv'
-delimiter=,
-table=myDestoTable
-quoteChar="
-multiLine=true
-skipTargetCheck=true
-stringDates=true
-timestampFormat="yyyy-MM-dd'T'HH:mm:ss.Z"
-emptyStringIsNull=true;

I receive:

Could not convert [2021-05-28T21:34:25.000Z] for datatype TIMESTAMP_WITH_TIMEZONE
Importing file  '20220720.csv' into table myDestoTable
Error importing row 1: An error parsing column [createddate], value=[2021-05-28T21:34:25.000Z]: workbench.util.ConverterException: Could not convert [2021-05-28T21:34:25.000Z] for datatype TIMESTAMP_WITH_TIMEZONE
The file "20220720.csv " was not imported

Desto DB:
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.40083

Desto Column type

TIMESTAMP_WITH_TIMEZONE

I've tried:
-timestampFormat="yyyy-MM-ddTHH:mm:ss.Z"

-timestampFormat="yyyy-MM-dd HH:mm:ss.Z"

-timestampFormat="yyyy-MM-dd'T'HH:mm:ss.SSSZ"

Multiple variations of the above and nothing works.

Any advice on the proper timestampFormat?

[andyinspace2]

Driver version: redshift-jdbc42-2.1.0.9.jar

[Thomas Kellerer]

You need a placeholder for the milliseconds ("SSS") and the you need to use a lower case "z" for the abbreviated timezone. The uppercase "Z" represents an offset (e.g. +0200)

-timestampFormat="yyyy-MM-dd'T'HH:mm:ss.SSSz"

[andyinspace2]

Thanks for the reply Thomas,

I tried with the updated format you provided -timestampFormat="yyyy-MM-dd'T'HH:mm:ss.SSSz" 

but I still received the error:

Could not convert [2021-08-05T00:12:46.000Z] for datatype TIMESTAMP_WITH_TIMEZONE
Importing file 'C:\extractNew.csv' into table case
Error importing row 1: An error parsing column [createddate], value=[2021-08-05T00:12:46.000Z]: workbench.util.ConverterException: Could not convert [2021-08-05T00:12:46.000Z] for datatype TIMESTAMP_WITH_TIMEZONE
The file "C:\extractNew.csv" was not imported

Could it be failing to import due to my java version?

java -version
openjdk version "17.0.1" 2021-10-19
OpenJDK Runtime Environment Temurin-17.0.1+12 (build 17.0.1+12)
OpenJDK 64-Bit Server VM Temurin-17.0.1+12 (build 17.0.1+12, mixed mode, sharing)

[andyinspace2]

Full repo steps with minimal data

Postgres table:
CREATE TABLE IF NOT EXISTS mytest
(
    id VARCHAR(18) NOT NULL  ENCODE lzo
    ,createddate TIMESTAMP WITH TIME ZONE   ENCODE az64
)

Create file
mytestimport.csv:
"id","createddate"
"abc123","2021-08-05T00:12:46.000Z"

WB Command:
WbImport -type=text
-file='mytestimport.csv'
-delimiter=,
-table=mytest
-stringDates=true
-timestampFormat="yyyy-MM-dd'T'HH:mm:ss.SSSz"

Result

Could not convert ["2021-08-05T00:12:46.000Z"] for datatype TIMESTAMP_WITH_TIMEZONE

Importing file 'mytestimport.csv' into table mytest

Error importing row 1: An error parsing column [createddate], value=["2021-08-05T00:12:46.000Z"]: workbench.util.ConverterException: Could not convert ["2021-08-05T00:12:46.000Z"] for datatype TIMESTAMP_WITH_TIMEZONE

The file "mytestimport.csv" was not imported

If I remove the quotes from the date to look like this
mytestimport.csv:
"id","createddate"
"abc123",2021-08-05T00:12:46.000Z

I get this error:
Can't infer the SQL type to use for an instance of java.time.ZonedDateTime. Use setObject() with an explicit Types value to specify the type to use.
Importing file 'mytestimport.csv' into tablemytest
Error importing row 1
Error message: Can't infer the SQL type to use for an instance of java.time.ZonedDateTime. Use setObject() with an explicit Types value to specify the type to use. [SQL State=07006]
Values from input file: "abc123",2021-08-05T00:12:46.000Z
The file "mytestimport.csv" was not imported

Not sure which one is the better error to have but nothing is working for me at the moment :)

[Thomas Kellerer]

Your values are quoted, so you need to define a quote character using -quoteChar='"'

WbImport -type=text
-file=mytestimport.csv
-delimiter=,
-table=mytest
-quoteChar='"'
-timestampFormat="yyyy-MM-dd'T'HH:mm:ss.SSSz";

The -stringDates parameter is useless for text imports, it's only used when importing spreadsheets.

Thomas

andyinspace2 schrieb am 23.07.2022 um 01:44:
> Full repo steps with minimal data
>

> *Postgres table:*

> CREATE TABLE IF NOT EXISTS mytest
> (
>     id VARCHAR(18) NOT NULL  ENCODE lzo
>     ,createddate TIMESTAMP WITH TIME ZONE   ENCODE az64
> )
>

> *Create file*

> mytestimport.csv:
> "id","createddate"
> "abc123","2021-08-05T00:12:46.000Z"
>

> *WB Command:*

> WbImport -type=text
> -file='mytestimport.csv'
> -delimiter=,
> -table=mytest
> -stringDates=true
> -timestampFormat="yyyy-MM-dd'T'HH:mm:ss.SSSz"
>

> *Result*

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/5a4138eb-f773-45db-8214-4f6b7ee2d0b0n%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/5a4138eb-f773-45db-8214-4f6b7ee2d0b0n%40googlegroups.com?utm_medium=email&utm_source=footer>.

[andyinspace2]

Hello, 

I've updated with the quotechar command you have provided.  Now I get the Java error:

Can't infer the SQL type to use for an instance of java.time.ZonedDateTime. Use setObject() with an explicit Types value to specify the type to use.

Full description

WbImport -type=text
-file='mytestimport.csv'
-delimiter=,
-table=mytest

-quoteChar='"'
-timestampFormat="yyyy-MM-dd'T'HH:mm:ss.SSSz";

An error occurred when executing the SQL command:

WbImport -type=text
-file='mytestimport.csv'
-delimiter=,
-table=mytest

-quoteChar='"'
-timestampFormat="yyyy-MM...

Can't infer the SQL type to use for an instance of java.time.ZonedDateTime. Use setObject() with an explicit Types value to specify the type to use.

Importing file 'mytestimport.csv' into table mytest
Error importing row 1

Error message: Can't infer the SQL type to use for an instance of java.time.ZonedDateTime. Use setObject() with an explicit Types value to specify the type to use. [SQL State=07006]

Values from input file: "abc123","2021-08-05T00:12:46.000Z"

The file "mytestimport.csv" was not imported

1 statement failed.

Execution time: 2.35s

[Thomas Kellerer]

Yet another problem with the Redshift JDBC driver.

You can try to configure a different way of passing values.

Run this (Workbench specific) "SQL" command in a SQL tab connected to redshift:

WbSetDBConfig import.setobject.usetype=true;

This change will be persisted in workbench.settings, so you only need to do it once.

Thomas

> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/e814f549-0ede-4270-a74f-ad8f1f3cdeb9n%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/e814f549-0ede-4270-a74f-ad8f1f3cdeb9n%40googlegroups.com?utm_medium=email&utm_source=footer>.

[andyinspace2]

Thanks for your suggestion,

I performed WbSetDBConfig import.setobject.usetype=true; 

After re-running the command I now get

Cannot cast an instance of java.time.ZonedDateTime to type Types.TIMESTAMP_WITH_TIMEZONE
Importing file 'mytestimport.csv' into table ssdlv2_stg.mytest
Error importing row 1
Error message: Cannot cast an instance of java.time.ZonedDateTime to type Types.TIMESTAMP_WITH_TIMEZONE [SQL State=07006]

Values from input file: "abc123","2021-08-05T00:12:46.000Z"
The file "mytestimport.csv" was not imported

I googled for similar errors and found https://github.com/pgjdbc/pgjdbc/issues/977  

Thank you for your patience so far.  Any other suggestions on how to get this time stamp to import ?

Thanks,

Andrew

[Thomas Kellerer]

This looks like another problem with the JDBC driver.

I have no access to a Redshift installation, so I can't debug this.

Regards
Thomas

> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/5e1dcbff-9e0c-4a0f-979a-43206cd8c401n%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/5e1dcbff-9e0c-4a0f-979a-43206cd8c401n%40googlegroups.com?utm_medium=email&utm_source=footer>.