> 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?
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
>
>
> www.nieuwestroom.nl <http://www.nieuwestroom.nl/>
> Kijk gratis terug: webinar Dynamische energie is de toekomst
> <https://nieuwe-oogst.webinargeek.com/webinar/replay/8_AX8F0UtBvv0LoSu8_mJb9olc5c94P8uaBKt8H0TG0/>
>
> PS We hebben een nieuwe huisstijl en website! Met ons 10 jarige bestaan,
> trokken we een nieuwe jas aan.
