Vertica change local time to GMT time

Such a scenario was encountered during the use of the Vertica database. The program never collects data from a cluster in the same time zone to write to 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 you have a table, a TS columnstore timestamp, a time zone where the TZ column is stored, such as the following

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

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 ');

You can now convert the time in 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 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 link: Vertica change local time to GMT time

Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced.

Vertica change local time to GMT time