Am 11.01.2024 um 16:06 schrieb Alban Hertroijs <a.hertroijs@nieuwestroom.nl>:
Hi all,
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...
have you tried to use the proper time zone before you pass it to the to_char() function?
Time: 2,095 ms
mkrueger=# select to_char(now() at time zone 'Europe/Amsterdam', 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
to_char
------------------------------------
2024-01-11 16:24:52.8736860 +00:00
(1 row)
At least it seems to do what you need.
That is indeed what I started out with, but that output is not correct for my purposes. That TZH field should read '+01' instead of '+00', so:
2024-01-11 16:24:52.8736860 +01:00
For analytical purposes, I tacked a 'TZ' at the end of that format string, and it kept coming out either empty or giving 'UTC' instead of 'CET'. And according to the Internet, that is because PG timestamps don't actually store the time zone information (it's stored relative to UTC) and thus there is no information to base the output of TZ, TZH and TZM on other than the current scope's time zone.
This is in fact exactly the problem that I tried to work around using those functions.
Regards,
Alban Hertroys.