Issues with timestamp coversion

[Chris Deere]

Hi all,

I am new to SQL WB and am having trouble with the following error:

Error importing row 1: An error parsing column [first_timestamp], value=[2021-12-09 01:40:30+00:00]: workbench.util.ConverterException: Could not convert [2021-12-09 01:40:30+00:00] for datatype TIMESTAMP_WITH_TIMEZONE

I suspect the issue is that input file does not have "T" separating the date and time.

I have a postgresSQL database and have tried setting my field "first_timestamp" as both TIMESTAMP and TIMESTAMP WITH TIMEZONE. 

I have tried adding this to my WbImport:   -timestampFormat="yyyy-MM-dd HH:mm:SS+ZZ:ZZ" (also a few other variations) but it makes no difference to the error.

Can anyone point me in the right direction on this?

Regards,

Chris

[Thomas Kellerer]

There are two problems as far as I can tell:

The uppper case S is for milliseconds not seconds.

The upper case Z is for a timezone offset without colon.

The lower case z works for a complete "time zone" specification.
And the "+" sign is not needed in the format mask, as that is implied with time zones (could be "-" as well)

In either case, the place holder is only required once for the whole time zone, not for each character, so

-timestampFormat=yyyy-MM-dd HH:mm:ssz

will work here.

Regards
Thomas

[Chris Deere]

Hi Thomas,

Thank you so much for that, and for SQL Workbench - it's a very handy new addition to my toolbox!

I tried so many combinations of string formats with no luck, but my import is now running, and it's greatly appreciated.

Cheers,

Chris

--
You received this message because you are subscribed to a topic in the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sql-workbench/6BXIq7fLz-g/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sql-workbenc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/e3c7b27a-fdfd-5e5d-983a-c1a8f7cca7db%40sql-workbench.net.

--

Chris Deere

0413 217770