Re: Time zone offset in to_char()
From: Adrian Klaver
> I didn’t succeed in calling SET LOCAL TIMEZONE from within the function.
> Could be I missed something, then Google (stackoverflow) pointed me to
> set_config().
CREATE OR REPLACE FUNCTION public.tz_fnc()
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
SET LOCAL TIMEZONE = 'UTC';
RAISE NOTICE '%', to_char(now(), 'OF');
END;
$function$
Turns out that the reason I cannot get this to work is that in my case the time
zone value comes from a text parameter:
ciscache=> create or replace function ciscache.ToDatetimeOffset(ts_
timestamptz, tz_ text)
returns varchar(34)
language plpgsql
as $$
begin
--perform set_config('timezone', tz_, true /* local */);
set local timezone to tz_;
return to_char(ts_, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
end;
$$;
CREATE FUNCTION
ciscache=> select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'),
to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
ERROR: invalid value for parameter "TimeZone": "tz_"
CONTEXT: SQL statement "set local timezone to tz_"
PL/pgSQL function todatetimeoffset(timestamp with time zone,text) line 4 at SQL
statement
But set_config() doesn't have that issue.
Re: Time zone offset in to_char()
> set_config ( setting_name text, new_value text, is_local boolean ) → text
>
> Sets the parameter setting_name to new_value, and returns that value. If
> is_local is true, the new value will only apply during the current
> transaction. If you want the new value to apply for the rest of the current
> session, use false instead. This function corresponds to the SQL command SET.
>
> set_config('log_statement_stats', 'off', false) → off"
> "
I tried this like so:
select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'),
to_char(current_timestamp, ‘-MM-DD HH24:MI:SS.SU0 TZH:TZM’).
The result of the second call was based on time zone ‘Europe/Amsterdam’, where
it wasn’t when called outside the transaction (when it was based on UTC
corresponding to the server time zone).
So the time zone set with set_config(…, …, true) appeared to leak out of
function scope and applied to transaction scope (as described in the quoted
text).
For brevity I could run that query tomorrow when I’m back at work.
Following up on my own mail from yesterday evening, here's the output that
shows the function using set_config 'leaking' the timezone change to outside
the function (the first select vs. the 2nd select) into the (same) transaction,
whereas the function with the time zone bound to the header does not (the 3rd
select).
These are all from a single session, ran consecutively - a straight copy-paste
from psql.
ciscache=> select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'),
to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
todatetimeoffset | to_char
+
2024-01-12 09:41:44.7019350 +01:00 | 2024-01-12 09:41:44.7019350 +01:00
(1 row)
ciscache=> select to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0
TZH:TZM');
to_char
2024-01-12 08:41:54.4739800 +00:00
(1 row)
ciscache=> select ToDatetimeOffsetNL(current_timestamp),
to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
todatetimeoffsetnl | to_char
+
2024-01-12 09:42:44.1989210 +01:00 | 2024-01-12 08:42:44.1989210 +00:00
(1 row)
Regards,
Alban Hertroijs.
Re: Time zone offset in to_char()
> 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). Would a function that dispatches its calls to a suitable array of hard-coded functions based on an IN parameter help any ? Karsten Well, probably, but we don't have many time zones that are relevant to us. For that, the current functions would be sufficient. The drawback, as mentioned, being that we need to maintain those functions in each deployment, which is a bit of a hassle (albeit a minor one) because we need to customise both the TDV side and the PostgreSQL side in that case. Our preferred solution would be to just add a few entries to the TDV database-specific capabilities file (as described in my initial message). Provided that such a solution is possible, that is. If not, my current approach may have to suffice. The reason I decided to ask on the ML is that I'm finding it hard to believe that this transformation would be this difficult, so I expect that I must be missing something. Regards, Alban Hertroys
Re: Time zone offset in to_char()
Am 11.01.2024 um 16:06 schrieb Alban Hertroijs : 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, '-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', '-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.
Time zone offset in to_char()
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, '-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...
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_, '-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_, '-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
[Afbeelding met tekst, buiten, teken Automatisch gegenereerde beschrijving]
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.
