TIMESTAMP_WITH_TIMEZONE import error

54 views
Skip to first unread message

andyinspace2

unread,
Jul 21, 2022, 5:13:59 PMJul 21
to SQL Workbench/J - DBMS independent SQL tool
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

unread,
Jul 21, 2022, 5:25:16 PMJul 21
to SQL Workbench/J - DBMS independent SQL tool
Driver version: redshift-jdbc42-2.1.0.9.jar

Thomas Kellerer

unread,
Jul 22, 2022, 1:53:57 AMJul 22
to sql-wo...@googlegroups.com
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

unread,
Jul 22, 2022, 6:41:51 PMJul 22
to SQL Workbench/J - DBMS independent SQL tool
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

unread,
Jul 22, 2022, 7:44:15 PMJul 22
to SQL Workbench/J - DBMS independent SQL tool
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

unread,
Jul 24, 2022, 1:48:38 PMJul 24
to sql-wo...@googlegroups.com
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

unread,
Jul 25, 2022, 3:35:23 PMJul 25
to SQL Workbench/J - DBMS independent SQL tool
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

unread,
Jul 27, 2022, 2:42:59 AMJul 27
to sql-wo...@googlegroups.com
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

unread,
Jul 27, 2022, 2:49:36 PMJul 27
to SQL Workbench/J - DBMS independent SQL tool
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

unread,
Jul 30, 2022, 5:01:10 PMJul 30
to sql-wo...@googlegroups.com
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>.
Reply all
Reply to author
Forward
0 new messages