How to select timestamptz with offset with AT TIME ZONE

Question

I've fiddeling around with timestamps in PostgreSQL, reading a interesting Blog about this behavior, but have no clue to solve following problem:

How to select a 'timestamp with time zone' column with AT TIME ZONE from another column, including the offset to UTC in one step?

I've a sample table in PostgreSQL 10:

CREATE TABLE public.test_tz
(
    id serial,
    in_zone_timestamp timestamp without time zone,
    in_zone character varying COLLATE pg_catalog."default",
    CONSTRAINT test_tz_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

PostgreSQL's setting is

timezone=localtime

and the server runs at 'Europe/Berlin'

$ date --> Fri Mar 9 12:15:23 CET 2018

The timestamp column should store with this messy syntax:

INSERT INTO public.test_tz ( in_zone_timestamp, in_zone) VALUES
('2017-02-01 00:00:00' AT TIME ZONE current_setting('TIMEZONE') AT TIME ZONE 'Asia/Singapore', 'Asia/Singapore' );

If someone knows a better way, please don't hold back! The other solution is to use '2017-02-01 00:00:00-08' as the value, but I don't know the offset value.

I want to store the local timestamp like: 'The user hit a key at 2017-02-01 00:00:00 in Singapore'.

If I ask the database: Which time it was here in Europe, when the user in Singapore is hitting a key, I got:

SELECT in_zone_timestamp FROM public.test_tz;
--> '2017-01-31 17:00:00+01'

This seems OK, because Singapore has a offset of +8 hours.

If I want to know, which time it was in Singapore, I use:

SELECT in_zone_timestamp AT TIME ZONE in_zone FROM public.test_tz;
--> '2017-02-01 00:00:00'

That's OK too, but however, it doesn't return the offset to 'UTC', so I can't see that this timestamp is not in my local time!

I try some combinations of AT TIME ZONE or converting to timestamptz, but the results are not what I want. I expected a result like:

--> '2017-02-01 00:00:00+08'

At here, I only see one solution, to manually concat/convert/manipulate the result and add the offset by hand, but is this the only way?

Sorry if I explain this question a little bit too comlicated and hope someone can follow my thoughts.

Thanks in advance

Second sentence: "How to select a 'timestamp with time zone' column with AT TIME ZONE from another column, including the offset to UTC in one step?" Sorry if my english is too ugly.... but I don't have clear idea, why you don't have a clear idea, what my question is? I got '2017-02-01 00:00:00' , but I want '2017-02-01 00:00:00+08'. — Banzai
There were so many sentences with question mark... also, I did not understand what you meant before I saw the example. I'll try to answer. — Laurenz Albe

Laurenz Albe Laurenz Albe · Accepted Answer · 2018-03-09T12:53:59

The only way to have PostgreSQL convert a timestamp to a string like 2017-02-01 00:00:00+08 automatically is to change the session time zone:

SET timezone = 'Asia/Singapore';

You can use SET LOCAL to change the setting only for the duration of a transaction.

If you don't want that, you can get the offset with a query like this:

SELECT TIMESTAMP '2000-01-01 00:00:00' AT TIME ZONE 'UTC'
     - TIMESTAMP '2000-01-01 00:00:00' AT TIME ZONE 'Asia/Singapore';

 ?column? 
----------
 08:00:00
(1 row)

How to select timestamptz with offset with AT TIME ZONE

2 Answers