Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to get Local Time from Timestamp with Timezone?

edited June 21 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 Administrator

    Try:

    SET TIME ZONE TO DEFAULT;
    SHOW TIMEZONE;
    SELECT CURRENT_TIME(0);
    
  • 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 Administrator
    edited June 23

    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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.