How to get Local Time from Timestamp with Timezone?

joergschaber

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?

mosheg

Try:

SET TIME ZONE TO DEFAULT;
SHOW TIMEZONE;
SELECT CURRENT_TIME(0);

joergschaber

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

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)