Skip to main content
Question

How to get Local Time from Timestamp with Timezone?

  • June 21, 2022
  • 3 replies
  • 8 views
joergschaber
Forum|alt.badge.img+2

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?

    3 replies

    mosheg
    Forum|alt.badge.img+2
    • mosheg
    • Participating Frequently
    • June 21, 2022

    Try:

    SET TIME ZONE TO DEFAULT;
SHOW TIMEZONE;
SELECT CURRENT_TIME(0);
    joergschaber
    Forum|alt.badge.img+2
    • joergschaber
    • Author
    • Participating Frequently
    • June 22, 2022

    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_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Participating Frequently
    • June 23, 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)

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement