The Vertica Database Forums

Hi, All

In Oracle you have the TZH(timezone hours) pattern one can use to convert string containing the timestamp and offset to the needed value. Could not find this for Vertica. Is it there? Any example of it.

The way I tried it and could not get it to work: Br,
_pj_

This works:

dbadmin=> SELECT SOME_DATE AS SOME_DATE_AS_STRING,
dbadmin-> TO_TIMESTAMP(SOME_DATE, 'YYYYMMDD HH24MISS') AS SOME_DATE_AS_TIMESTAMP ,
dbadmin-> TO_TIMESTAMP_TZ(SOME_DATE, 'YYYYMMDD HHMISStzh') AS SOME_DATE_AS_TIMESTAMP_TZH
dbadmin-> FROM (SELECT '20170207T014757+02' AS SOME_DATE) EXAMPLE;
SOME_DATE_AS_STRING | SOME_DATE_AS_TIMESTAMP | SOME_DATE_AS_TIMESTAMP_TZH
---------------------+------------------------+----------------------------
20170207T014757+02 | 2017-02-07 01:47:57 | 2017-02-06 18:47:57-05
(1 row)

Hi, JimKnicely

Thanks for your reply.

In general you have posted then the same query that I provided in the initial post. Just that the output differs due to the fact that your database has different timezone.
Mine has:
>> show timezone
/*
name setting
timezone Europe/Tallinn
*/
This is then UTC+2hours.

So if the TZH pattern would work the same way as in Oracle then the string '20170207T014757+02' should be casted to timestamp '2017-02-07 01:47:57' +2 hours = '2017-02-07 03:47:57'. But it does not, the hour is remaining the same 01. In your case the timestamp_tz is different just because you have different timezone.

Br,
_pj_

There are 4 time data types in play:

TIME - which keeps hours/minutes/seconds kind of thing
TIMETZ - which keeps what time PLUS a time zone. TIMETZ is an awful data type and I'd avoid it like the plague.
TIMESTAMP - which keeps microseconds from a fixed time, hence it can handle hours/minutes/seconds/days/months/years
TIMESTAMPTZ - which is like that but converts between GMT and your local time zone automatically

So let's deconstruct the time example.

-- If we give a timezone, and ask for the same, it is preserved
CBear=> SELECT TIME '10:23:54 CDT' AT TIME ZONE 'CDT';
timezone
-------------
10:23:54-05
(1 row)

-- Asking for time in my time zone to be converted to CDT
CBear=> SELECT TIME '10:23:54 EDT' AT TIME ZONE 'CDT';
timezone
-------------
09:23:54-05
(1 row)

-- Time constant is assumed to be in my EDT time zone, then converted to CDT.
CBear=> SELECT TIME '10:23:54' AT TIME ZONE 'CDT';
timezone
-------------
09:23:54-05
(1 row)

The timestamp example is working differently. This is because AT TIME ZONE does 2 different things, one to timestamps, and one to timestamptz!
First is to take a TIMESTAMPTZ, and turn it into a TIMESTAMP at the time zone you ask. That could be what you wanted.
CBear=> SELECT TIMESTAMPTZ '2004-10-19 10:23:54 EDT' AT TIME ZONE 'CDT';
timezone
---------------------
2004-10-19 09:23:54
(1 row)

-- Assumes the constant came from your timezone
CBear=> SELECT TIMESTAMPTZ '2004-10-19 10:23:54' AT TIME ZONE 'CDT';
timezone
---------------------
2004-10-19 09:23:54
(1 row)

-- Use of timezone in the string
CBear=> SELECT TIMESTAMPTZ '2004-10-19 10:23:54 CDT' AT TIME ZONE 'CDT';
timezone
---------------------
2004-10-19 10:23:54

The other thing it does is take a TIMESTAMP and turn it into a TIMESTAMPTZ by saying, in essence, that it was recorded at the time zone you say. For display purposes, it will get displayed in the local timezone.

-- Comes back looking the same
CBear=> SELECT TIMESTAMP '2004-10-19 10:23:54' AT TIME ZONE 'EDT';
timezone
------------------------
2004-10-19 10:23:54-04
(1 row)

-- The timestamp is treated as if originally CDT, so it displays an hour higher in EDT (-4)
CBear=> SELECT TIMESTAMP '2004-10-19 10:23:54' AT TIME ZONE 'CDT';
timezone
------------------------
2004-10-19 11:23:54-04
(1 row)

Note that the Oracle docs say about TO_TIMESTAMP_TZ funtion:

This function does not convert character strings to TIMESTAMP WITH LOCAL TIME ZONE. To do this, use a CAST function, as shown in CAST.

However in Vertica the TO_TIMESTAMP_TZ function DOES convert to my local timezone:

dbadmin=> select to_char(to_timestamp_tz('2017-04-11 02:38 CDT', 'YYYY-MM-DD HH24:MI TZ'), 'YYYY-MM-DD HH24:MI TZ');
to_char
----------------------
2017-04-11 03:38 EDT
(1 row)