TO_TIMESTAMP_TZ from string containing the UTC offset

Moderator: NorbertKrupa

Post Reply
_pj_
Newbie
Newbie
Posts: 6
Joined: Wed Mar 29, 2017 1:14 pm

TO_TIMESTAMP_TZ from string containing the UTC offset

Post by _pj_ » Mon Apr 10, 2017 1:45 pm

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:

Code: Select all

SELECT SOME_DATE AS SOME_DATE_AS_STRING
, TO_TIMESTAMP(SOME_DATE, 'YYYYMMDD HH24MISS') AS SOME_DATE_AS_TIMESTAMP
, TO_TIMESTAMP_TZ(SOME_DATE, 'YYYYMMDD HHMISStzh') AS SOME_DATE_AS_TIMESTAMP_TZH
FROM --EXAMPLE
    (
    SELECT '20170207T014757+02' AS SOME_DATE
    FROM --DUAL
        DUAL
    WHERE 1 = 1
    ) EXAMPLE
WHERE 1 = 1
/*
SOME_DATE_AS_STRING	SOME_DATE_AS_TIMESTAMP	SOME_DATE_AS_TIMESTAMP_TZH
20170207T014757+02	2017-02-07 01:47:57	2017-02-07 01:47:57
*/
Br,
_pj_

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: TO_TIMESTAMP_TZ from string containing the UTC offset

Post by JimKnicely » Mon Apr 10, 2017 2:58 pm

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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

_pj_
Newbie
Newbie
Posts: 6
Joined: Wed Mar 29, 2017 1:14 pm

Re: TO_TIMESTAMP_TZ from string containing the UTC offset

Post by _pj_ » Tue Apr 11, 2017 11:33 am

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_

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: TO_TIMESTAMP_TZ from string containing the UTC offset

Post by JimKnicely » Tue Apr 11, 2017 7:33 pm

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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: TO_TIMESTAMP_TZ from string containing the UTC offset

Post by JimKnicely » Tue Apr 11, 2017 7:48 pm

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)

Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL Functions”