How to get Local Time from Timestamp with Timezone?

joergschaberjoergschaber Vertica Customer
edited June 2022 in General Discussion

Hi,

I want to store timestamps with timezone offset info, like this:

CREATE TABLE TimeZoneTest (tstz TIMESTAMPTZ);
Set timezone to 'Europe/Berlin';
INSERT INTO TimeZoneTest VALUES ('2022-06-06 10:03:44-02:00');
INSERT INTO TimeZoneTest VALUES ('2022-06-06 10:03:44+01:00');

Now, I can select the time at each timezone, e.g. like

SELECT tstz AS 'Europe/Berlin', tstz AT TIMEZONE 'UTC' AS 'UTC' from TimeZoneTest;

However, how do I select the local time, i.e. 10:03:44, irrespective of the time zone, such that I get

2022-06-06 10:03:44
2022-06-06 10:03:44

as a result? Or can I get the offset only?

Answers

  • moshegmosheg Vertica Employee Administrator

    Try:

    SET TIME ZONE TO DEFAULT;
    SHOW TIMEZONE;
    SELECT CURRENT_TIME(0);
    
  • joergschaberjoergschaber Vertica Customer

    I am looking for a query on the above table, that would give the result

    2022-06-06 10:03:44
    2022-06-06 10:03:44
    

    i.e. returns the local time of the inserted times with time zones.

  • Bryan_HBryan_H Vertica Employee Administrator
    edited June 2022

    According to doc, "TIMESTAMPTZ is the same as TIMESTAMP WITH TIME ZONE: both data types store the UTC offset of the specified time"
    https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Date-Time/TIMESTAMP.htm
    You could store as VARCHAR truncating the timezone offset and cast/convert later as needed. The closest you can get with a datetime type is a second column with TIMESTAMP without time zone, though this may calculate local time offset, as shown in the example below:
    dbadmin=> create table tztest (example int, tstz timestamptz, ts timestamp);
    dbadmin=> show timezone;
    name | setting
    ----------+------------
    timezone | US/Eastern
    dbadmin=> insert into tztest values (1,'2022-06-06 10:03:44-02:00','2022-06-06 10:03:44+01:00');
    dbadmin=> insert into tztest values (2,'2022-06-06 10:03:44','2022-06-06 10:03:44');
    dbadmin=> select * from tztest ;
    example | tstz | ts
    ---------+------------------------+---------------------
    1 | 2022-06-06 08:03:44-04 | 2022-06-06 05:03:44
    2 | 2022-06-06 10:03:44-04 | 2022-06-06 10:03:44
    (2 rows)
    dbadmin=> set timezone 'utc';
    dbadmin=> insert into tztest values (3,'2022-06-06 10:03:44-02:00','2022-06-06 10:03:44+01:00');
    dbadmin=> insert into tztest values (4,'2022-06-06 10:03:44','2022-06-06 10:03:44');
    dbadmin=> select * from tztest ;
    example | tstz | ts
    ---------+------------------------+---------------------
    1 | 2022-06-06 12:03:44+00 | 2022-06-06 05:03:44
    2 | 2022-06-06 14:03:44+00 | 2022-06-06 10:03:44
    3 | 2022-06-06 12:03:44+00 | 2022-06-06 09:03:44
    4 | 2022-06-06 10:03:44+00 | 2022-06-06 10:03:44
    (4 rows)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file