Am 11.01.2024 um 16:06 schrieb Alban Hertroijs <a.hertro...@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.

Reply via email to