Am 11.01.2024 um 16:06 schrieb Alban Hertroijs <[email protected]>:
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.
