Vertica convert local time to GMT time

Source: Internet
Author: User
Tags gmt time zone time zones local 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.