Vertica convert local time to GMT time

In the use of the Vertica database, a scenario is encountered in which the program collects data from the cluster in the same area and writes the same table, and then we need to display the data in GMT time. At this point we can provide the time zone conversion function through Vertica to achieve this effect.

First look at the Vertica default timezone

Show TIMEZONE;

This assumes that the default timezone for Vertica is "Asia/shanghai"

SET TIMEZONE to ' Asia/shanghai ';

Suppose there is a table, the TS column stores the timestamp, the TZ column stores the time zone, as follows

CREATE TABLE T (TS TIMESTAMP, TZ VARCHAR (32));

And then build two data using america/new_york and Asia/shanghai time zones, respectively.

INSERT into T VALUES (TIMESTAMP ' 2014-11-11 00:00:00 ', ' america/new_york ');
INSERT into T VALUES (TIMESTAMP ' 2014-11-11 00:00:00 ', ' Asia/shanghai ');

At this point, you can convert the time of each local time zone to the GMT time zone by using the following SQL

SELECT
    (TS | | ' ' || TZ):: TIMESTAMP as ' local time ', 
    (TS | | ' ' || TZ):: TIMESTAMP with time ZONE in time ZONE ' GMT ' as ' GMT ' from
T;
 
     local time      |      GMT time
---------------------+---------------------
 2014-11-11 13:00:00 | 2014-11-11 05:00:00
 2014-11-11 00:00:00 | 2014-11-10 16:00:00

Original link: Vertica convert local time to GMT time