COPY format for ISO8601 date time value

kamil_kliczbor

Greetings,
I am trying to implement the COPY functionality where I have the input from the dates written in the ISO8601 format like: '2021-09-18T23:59:59.1234567+02:00'.

According to the docs from https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/COPY/COPYParameters.htm

When loading date/time columns, using FORMAT significantly improves load performance. COPY supports the same formats as the TO_DATE function.

I tried to match the formatting parameters for the timezone expressed as the digits (not the timezone name). Unfortunately with failure. I followed specification from
https://vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TemplatePatternsForDateTimeFormatting.htm

Can anyone help with the following formatting string to be aware of the timezone ?
'SELECT TO_TIMESTAMP('2021-09-18T23:59:59.1234567+02:00', 'YYYY-MM-DD HH24:MI:SS.USXYZ')'

XYZ - how to format such timezone in Vertica?

kind regards,
Kamil

Jim_Knicely

Does this help with the original format?

verticademos=> \! cat /home/dbadmin/t.txt
ABC|2021-09-18T23:59:59.1234567+02:00

verticademos=> CREATE TABLE t (c1 VARCHAR, c2 TIMESTAMP);
CREATE TABLE

verticademos=> COPY t (c1, c2_f FILLER VARCHAR, c2 AS c2_f::TIMESTAMP) FROM '/home/dbadmin/t.txt';
 Rows Loaded
-------------
           1
(1 row)

verticademos=> SELECT * FROM t;
 c1  |             c2
-----+----------------------------
 ABC | 2021-09-18 21:59:59.123457
(1 row)

marcothesane

Well - '2021-09-18T23:59:59.1234567+02:00' is not a literal for a TIMESTAMP , but for a TIMESTAMP WITH TIME ZONE, or also, TIMESTAMPTZ .

And it loads with no problem - and no necessary transformation - into a correctly defined column.

See this scenario:

DROP TABLE IF EXISTS ts;
CREATE TABLE IF NOT EXISTS ts (
  tsz TIMESTAMPTZ
);
COPY ts FROM STDIN;
'2021-09-18T23:59:59.1234567+02:00'
\.
SELECT
 *
,tsz AT TIMEZONE 'America/New_York' AS other_tsz
FROM ts;
              tsz              |         other_tsz          
-------------------------------+----------------------------
 2021-09-18 23:59:59.123457+02 | 2021-09-18 17:59:59.123457

On how to work with TIMESTAMP AT TIMEZONE , see this from the Vertica docu:
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Date-Time/TIMESTAMPATTIMEZONE.htm?zoom_highlight=timestamp with time zone