COPY format for ISO8601 date time value
kamil_kliczbor
Community Edition User ✭
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
Answers
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)Well -
'2021-09-18T23:59:59.1234567+02:00'is not a literal for aTIMESTAMP, but for aTIMESTAMP 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.123457On 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