Skip to main content
Question

COPY format for ISO8601 date time value

  • September 18, 2021
  • 2 replies
  • 11 views
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

    2 replies

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • September 18, 2021

    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
    Forum|alt.badge.img+1
    • marcothesane
    • Participating Frequently
    • September 19, 2021

    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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement