Vertica converts Local time to GMT time, verticagmt

Vertica converts Local time to GMT time, verticagmt

When using Vertica databases, the program collects data from clusters in different time zones and writes it to the same table. Then we need to display the data according to the GMT time. In this case, we can use Vertica to provide the time zone conversion function.

First, let's take a look at the default TimeZone of Vertica.

SHOW TIMEZONE;

Assume that the default TimeZone of Vertica is "Asia/Shanghai"

SET TIMEZONE TO 'Asia/Shanghai';

Assume that there is a table, the TS column stores the timestamp, And the TZ column stores the time zone, as shown below:

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

Then, the two data types are created using the 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 time, you can use the following SQL to convert the time of each Local time zone to the GMT time zone.

SELECT    (TS || ' ' || TZ)::TIMESTAMP AS 'LOCAL TIME',     (TS || ' ' || TZ)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'GMT' AS 'GMT TIME'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 article: Vertica converts Local time to GMT time