Re: Time zone offset in to_char() - Mailing list pgsql-general

From michael@kruegers.email
Subject Re: Time zone offset in to_char()
Date
Msg-id 98D10A7F-73DE-4BEE-B873-045226458E11@kruegers.email
Whole thread Raw
In response to Time zone offset in to_char()  (Alban Hertroijs <a.hertroijs@nieuwestroom.nl>)
Responses Re: Time zone offset in to_char()
List pgsql-general

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?

mkrueger=# select to_char(now(), 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
              to_char
------------------------------------
 2024-01-11 16:24:21.9154740 +01:00
(1 row)

Time: 12,351 ms
mkrueger=# select to_char(now() at time zone 'UTC', 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
              to_char
------------------------------------
 2024-01-11 15:24:38.1619810 +00:00
(1 row)

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.

Regards,
Michael


I'm dealing with a data virtualisation system (TIBCO TDV) here that connects different types of data-sources, among which is an MS SQL database with said type. The virtualisation software uses PostgreSQL (14.10 on Ubuntu Linux 22.04) for caching data. TDV doesn't understand this datetimeoffset type and treats it internally as a VARCHAR(34) - hence the string output - which is obviously kind of hard to work with for aggregations and such.

However, in TDV we can create a translation between TDV functions that accept a timestamp type and a time zone name with a translation to native PostgreSQL functions, operands and whatnot. That's what I'm looking for.
It currently have this:
ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)

In the above, I worked around the issue using a couple of user-defined functions in PG. That should give a reasonable idea of the desired functionality, but it's not an ideal solution to my problem:
1). The first function has as a drawback that it changes the time zone for the entire transaction (not sufficiently isolated to my tastes), while
2). The second function has the benefit that it doesn't leak the time zone change, but has as drawback that the time zone is now hardcoded into the function definition, while
3). Both functions need to be created in the caching database before we can use them, while we have several environments where they would apply (DEV, pre-PROD, PROD).

/* Based this one on a stackoverflow post */
create or replace function ciscache.ToDatetimeOffset(ts_ timestamptz, tz_ text)
returns varchar(34)
language plpgsql
as $$
begin
      perform set_config('timezone', tz_, true /* local */);
      return to_char(ts_, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
end;
$$;

create or replace function ciscache.ToDatetimeOffsetNL(ts_ timestamptz)
returns varchar(34)
language plpgsql
set timezone to 'Europe/Amsterdam'
as $$
begin
      return to_char(ts_, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
end;
$$;

Is there a way to do this without functions, or if not, at least without having to hard-code the time zone or leaking the time zone change to other calls within the same transaction?

Any suggestions much appreciated.
 
Groet,
 
Alban Hertroijs
Data engineer  NieuweStroom
aanwezig ma t/m vr, di tot 13:30 uur

 

 


PS We hebben een nieuwe huisstijl en website! Met ons 10 jarige bestaan, trokken we een nieuwe jas aan.



pgsql-general by date:

Previous
From: Karsten Hilbert
Date:
Subject: Aw: Time zone offset in to_char()
Next
From: Alban Hertroijs
Date:
Subject: Re: Time zone offset in to_char()