On 1/11/24 15:32, Jim Nasby wrote:
> On 1/11/24 9:06 AM, Alban Hertroijs wrote:
>> I'm basically looking for a one-liner to convert a timestamptz (or a
>> timestamp w/o time zone if that turns out to be more convenient) to a
>> string format equal to what MS uses for their datetimeoffset type. I
>> got almost there with to_char(ts, 'YYYY-MM-DD HH24:MI:SS.US0
>> TZH:TZM'). Unfortunately(?), the server lives at time zone UTC, while
>> we need to convert to both UTC and Europe/Amsterdam zones. The above
>> always gives me +00 for the TZH output, while it should be +01 now and
>> +02 in the summer...
>
> The issue here is that timestamptz doesn't store the original timezone;
> it always converts whatever is passed in to UTC and stores that. When
> you read the timezone back, by default it will be in the timezone
> specified in the TimeZone GUC. While there's a bunch of ways you can set
> that, for what you're looking to do I don't think any of them are
> appropriate; instead you want to use either AT TIME ZONE or timezone():
>
> create table tstz(tstz timestamptz);
> insert into tstz values(now());
> SHOW timezone;
> TimeZone
> ----------
> CST6CDT
> (1 row)
>
> select * from tstz ;
> tstz
> ------------------------------
> 2024-01-11 17:29:00.04933-06
> (1 row)
>
> select timezone('UTC',tstz) from tstz ;
> timezone
> ---------------------------
> 2024-01-11 23:29:00.04933
> (1 row)
>
> select tstz AT TIME ZONE 'UTC' from tstz ;
> timezone
> ---------------------------
> 2024-01-11 23:29:00.04933
> (1 row)
The problem with this is as mentioned here:
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
"timestamp with time zone AT TIME ZONE zone → timestamp without time zone
Converts given time stamp with time zone to time stamp without time
zone, as the time would appear in that zone."
So when you do something like:
test=# set timezone = 'UTC';
SET
test=# select now() AT TIME ZONE 'UTC' ;
timezone
----------------------------
2024-01-11 23:44:46.021986
(1 row)
test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', 'YYYY-MM-DD
HH24:MI:SS.US0 TZH:TZM') ;
to_char
------------------------------------
2024-01-12 00:44:57.5421420 +00:00
(1 row)
You end up with string that does not the correct offset as the AT TIME
ZONE outputs a timestamp not timestamptz value.
--
Adrian Klaver
adrian.klaver@aklaver.com