Thread: Time zone offset in to_char()

Time zone offset in to_char()

From
Alban Hertroijs
Date:
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...

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

 

Afbeelding met tekst, buiten, teken

Automatisch gegenereerde beschrijving

 

www.nieuwestroom.nl

Kijk gratis terug: webinar Dynamische energie is de toekomst


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

 

Attachment

Aw: Time zone offset in to_char()

From
Karsten Hilbert
Date:
> In the above, I worked around the issue using a couple of user-defined functions in PG. That should give a reasonable
ideaof 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
isolatedto 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
zoneis 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
environmentswhere 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




Aw: Time zone offset in to_char()

From
Karsten Hilbert
Date:
> > In the above, I worked around the issue using a couple of user-defined functions in PG. That should give a
reasonableidea 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
isolatedto 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
timezone 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
environmentswhere 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? 

Without testing the idea - could it even generate the hardcoded function as needed, based on the parameter, and then
runit ? 

Karsten



Re: Time zone offset in to_char()

From
michael@kruegers.email
Date:

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.



Re: Time zone offset in to_char()

From
Alban Hertroijs
Date:
 

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?

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.

Re: Time zone offset in to_char()

From
Alban Hertroijs
Date:
> 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()

From
Adrian Klaver
Date:
On 1/11/24 07:06, Alban Hertroijs wrote:
> Hi all,
> 

> 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

I don't think the set_config and SET are acting the way you think they are:

set_config(https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET)

"
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"
"

SET(https://www.postgresql.org/docs/current/sql-set.html)

"If SET (or equivalently SET SESSION) is issued within a transaction 
that is later aborted, the effects of the SET command disappear when the 
transaction is rolled back. Once the surrounding transaction is 
committed, the effects will persist until the end of the session, unless 
overridden by another SET.

The effects of SET LOCAL last only till the end of the current 
transaction, whether committed or not. A special case is SET followed by 
SET LOCAL within a single transaction: the SET LOCAL value will be seen 
until the end of the transaction, but afterwards (if the transaction is 
committed) the SET value will take effect.

The effects of SET or SET LOCAL are also canceled by rolling back to a 
savepoint that is earlier than the command.

If SET LOCAL is used within a function that has a SET option for the 
same variable (see CREATE FUNCTION), the effects of the SET LOCAL 
command disappear at function exit; that is, the value in effect when 
the function was called is restored anyway. This allows SET LOCAL to be 
used for dynamic or repeated changes of a parameter within a function, 
while still having the convenience of using the SET option to save and 
restore the caller's value. However, a regular SET command overrides any 
surrounding function's SET option; its effects will persist unless 
rolled back.
"

> 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).


-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Time zone offset in to_char()

From
Adrian Klaver
Date:
On 1/11/24 08:04, Alban Hertroijs wrote:
>      > 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)

Are you referring to?:

"It currently have this:
ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)
"

I thought the issue there was maintaining the two Postgres functions?


> 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

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Time zone offset in to_char()

From
Adrian Klaver
Date:
On 1/11/24 08:48, Adrian Klaver wrote:
> On 1/11/24 08:04, Alban Hertroijs wrote:

>> 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)
> 
> Are you referring to?:
> 
> "It currently have this:
> ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
> ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)
> "

It finally dawned on me, you want to replace the user defined functions 
above with Postgres builtins only. Try as I might I could not come with 
that solution.

> 
> I thought the issue there was maintaining the two Postgres functions?
> 
> 
>> 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
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Time zone offset in to_char()

From
"Daniel Verite"
Date:
    Alban Hertroijs wrote:

> 1). The first function has as a drawback that it changes the time zone for
> the entire transaction (not sufficiently isolated to my tastes)

But if you add in the function declaration
   SET timezone TO  'Europe/Amsterdam'
like in your 2nd function, or simply
  SET timezone FROM CURRENT
doesn't that solve this problem?

Because as the doc says

  If a SET clause is attached to a function, then the effects of a SET
  LOCAL command executed inside the function for the same variable are
  restricted to the function: the configuration parameter's prior
  value is still restored at function exit

The actual value to which the timezone is set through this statement
does not matter, as the first instruction in the function overwrites
it:
   perform set_config('timezone', tz_, true /* local */);

The point is that the prior value of timezone being restored
automatically at function exit, the temporary setting will not
leak out of the function.


Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite



Re: Time zone offset in to_char()

From
Alban Hertroys
Date:
> On 11 Jan 2024, at 17:43, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 1/11/24 07:06, Alban Hertroijs wrote:
>> Hi all,
>
>> In the above, I worked around the issue using a couple of user-defined functions in PG. That should give a
reasonableidea 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
isolatedto 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
zoneis now hardcoded into the function definition, while 
>
> I don't think the set_config and SET are acting the way you think they are:
>
> set_config(https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET)
>
> "
> 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
applyduring 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, ‘YYYY-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.

> SET(https://www.postgresql.org/docs/current/sql-set.html)
>
> "If SET (or equivalently SET SESSION) is issued within a transaction that is later aborted, the effects of the SET
commanddisappear when the transaction is rolled back. Once the surrounding transaction is committed, the effects will
persistuntil the end of the session, unless overridden by another SET. 
>
> The effects of SET LOCAL last only till the end of the current transaction, whether committed or not. A special case
isSET followed by SET LOCAL within a single transaction: the SET LOCAL value will be seen until the end of the
transaction,but afterwards (if the transaction is committed) the SET value will take effect. 

It says transaction again here.

> The effects of SET or SET LOCAL are also canceled by rolling back to a savepoint that is earlier than the command.
>
> If SET LOCAL is used within a function that has a SET option for the same variable (see CREATE FUNCTION), the effects
ofthe SET LOCAL command disappear at function exit; that is, the value in effect when the function was called is
restoredanyway. This allows SET LOCAL to be used for dynamic or repeated changes of a parameter within a function,
whilestill having the convenience of using the SET option to save and restore the caller's value. However, a regular
SETcommand overrides any surrounding function's SET option; its effects will persist unless rolled back. 
> "

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(). 

I did manage to apply it to the second function header, which I think behaves such that the time zone change stays
withinfunction scope. Right now I’m not 100% sure that I verified that. More to check tomorrow. 

Frankly, I do hope that you’re right here, that would make my work easier.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.




Re: Time zone offset in to_char()

From
Alban Hertroys
Date:
> On 11 Jan 2024, at 18:27, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 1/11/24 08:48, Adrian Klaver wrote:
>> On 1/11/24 08:04, Alban Hertroijs wrote:
>
>>> 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
preferredsolution would be to just add a few entries to the TDV database-specific capabilities file (as described in my
initialmessage) 
>> Are you referring to?:
>> "It currently have this:
>> ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
>> ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)
>> "
>
> It finally dawned on me, you want to replace the user defined functions above with Postgres builtins only. Try as I
mightI could not come with that solution. 

Exactly. I was having the same problem of finding a solution, quite to my surprise.

>> I thought the issue there was maintaining the two Postgres functions?

Yup, those two functions in fact.

There will be at least 3 separate deployments, while maintenance of the database(-schema) contents is the
responsibilityof the 3rd party application (TDV). PG is used as a caching DB here, we therefore intend to treat the
datain it as volatile; it shouldn’t hurt if we decide to recreate the caches from scratch from source data. Having
customcode in there not under control of the 3rd party application breaks that guideline. 

If they’re necessary, then so be it, but I can’t shake the feeling that we can achieve this without custom code in the
database.

Regards,

Alban Hertroys
--
There is always an exception to always.







Re: Time zone offset in to_char()

From
Adrian Klaver
Date:
On 1/11/24 11:04, Alban Hertroys wrote:
> 

> 
> 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$


test=# begin ;
BEGIN
test=*# select public.tz_fnc();
NOTICE:  +00
  tz_fnc
--------

(1 row)

test=*# show timezone;
  TimeZone
----------
  UTC
(1 row)

test=*# commit;
COMMIT
test=# show timezone;
       TimeZone
---------------------
  America/Los_Angeles
(1 row)





> 
> I did manage to apply it to the second function header, which I think behaves such that the time zone change stays
withinfunction scope. Right now I’m not 100% sure that I verified that. More to check tomorrow.
 
> 
> Frankly, I do hope that you’re right here, that would make my work easier.
> 
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Time zone offset in to_char()

From
Adrian Klaver
Date:
On 1/11/24 11:04, Alban Hertroys wrote:
> 

> I did manage to apply it to the second function header, which I think behaves such that the time zone change stays
withinfunction scope. Right now I’m not 100% sure that I verified that. More to check tomorrow.
 


CREATE OR REPLACE FUNCTION public.tz_fnc2()
  RETURNS void
  LANGUAGE plpgsql
  SET "TimeZone" TO 'UTC'
AS $function$
BEGIN
     RAISE NOTICE '%', to_char(now(), 'OF');
END;
$function$;


test=# begin ;
BEGIN
test=*# select public.tz_fnc2();
NOTICE:  +00
  tz_fnc2
---------

(1 row)

test=*# show timezone;
  TimeZone
----------
  UTC
(1 row)

test=*# commit ;
COMMIT
test=# show timezone;
  TimeZone
----------
  UTC
(1 row)


> 
> Frankly, I do hope that you’re right here, that would make my work easier.
> 
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Time zone offset in to_char()

From
Jim Nasby
Date:
On 1/11/24 9:06 AM, Alban Hertroijs wrote:
> 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...

The issue here is that timestamptz doesn't store the original timezone; 
it always converts whatever is passed in to UTC and stores that. When 
you read the timezone back, by default it will be in the timezone 
specified in the TimeZone GUC. While there's a bunch of ways you can set 
that, for what you're looking to do I don't think any of them are 
appropriate; instead you want to use either AT TIME ZONE or timezone():

create table tstz(tstz timestamptz);
insert into tstz values(now());
SHOW timezone;
  TimeZone
----------
  CST6CDT
(1 row)

select * from tstz ;
              tstz
------------------------------
  2024-01-11 17:29:00.04933-06
(1 row)

select timezone('UTC',tstz) from tstz ;
          timezone
---------------------------
  2024-01-11 23:29:00.04933
(1 row)

select tstz AT TIME ZONE 'UTC' from tstz ;
          timezone
---------------------------
  2024-01-11 23:29:00.04933
(1 row)

-- 
Jim Nasby, Data Architect, Austin TX




Re: Time zone offset in to_char()

From
Adrian Klaver
Date:
On 1/11/24 15:32, Jim Nasby wrote:
> On 1/11/24 9:06 AM, Alban Hertroijs wrote:
>> 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...
> 
> The issue here is that timestamptz doesn't store the original timezone; 
> it always converts whatever is passed in to UTC and stores that. When 
> you read the timezone back, by default it will be in the timezone 
> specified in the TimeZone GUC. While there's a bunch of ways you can set 
> that, for what you're looking to do I don't think any of them are 
> appropriate; instead you want to use either AT TIME ZONE or timezone():
> 
> create table tstz(tstz timestamptz);
> insert into tstz values(now());
> SHOW timezone;
>   TimeZone
> ----------
>   CST6CDT
> (1 row)
> 
> select * from tstz ;
>               tstz
> ------------------------------
>   2024-01-11 17:29:00.04933-06
> (1 row)
> 
> select timezone('UTC',tstz) from tstz ;
>           timezone
> ---------------------------
>   2024-01-11 23:29:00.04933
> (1 row)
> 
> select tstz AT TIME ZONE 'UTC' from tstz ;
>           timezone
> ---------------------------
>   2024-01-11 23:29:00.04933
> (1 row)

The problem with this is as mentioned here:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT


"timestamp with time zone AT TIME ZONE zone → timestamp without time zone

Converts given time stamp with time zone to time stamp without time 
zone, as the time would appear in that zone."

So when you do something like:

test=# set timezone = 'UTC';
SET
test=# select now() AT TIME ZONE 'UTC' ;
           timezone
----------------------------
  2024-01-11 23:44:46.021986
(1 row)

test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', 'YYYY-MM-DD 
HH24:MI:SS.US0 TZH:TZM') ;
               to_char
------------------------------------
  2024-01-12 00:44:57.5421420 +00:00
(1 row)


You end up with string that does not the correct offset as the AT TIME 
ZONE outputs a timestamp not timestamptz value.



-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Time zone offset in to_char()

From
Tom Lane
Date:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', 'YYYY-MM-DD 
> HH24:MI:SS.US0 TZH:TZM') ;
>                to_char
> ------------------------------------
>   2024-01-12 00:44:57.5421420 +00:00
> (1 row)

> You end up with string that does not the correct offset as the AT TIME 
> ZONE outputs a timestamp not timestamptz value.

Yeah.  to_char() does not have any source for the TZ/TZH/TZM fields
other than the prevailing value of the timezone parameter, so you
really have to set that the way you want if you desire to use these
format fields.  As noted upthread, SET LOCAL together with a (dummy)
"SET timezone" clause in the function definition can be used to get
the effect of a function-local setting of the parameter.  I don't
know of another way to achieve that result above the C-code level.

            regards, tom lane



Re: Time zone offset in to_char()

From
Jim Nasby
Date:
On 1/11/24 5:53 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', 'YYYY-MM-DD
>> HH24:MI:SS.US0 TZH:TZM') ;
>>                 to_char
>> ------------------------------------
>>    2024-01-12 00:44:57.5421420 +00:00
>> (1 row)
> 
>> You end up with string that does not the correct offset as the AT TIME
>> ZONE outputs a timestamp not timestamptz value.
> 
> Yeah.  to_char() does not have any source for the TZ/TZH/TZM fields
> other than the prevailing value of the timezone parameter, so you
> really have to set that the way you want if you desire to use these
> format fields.  As noted upthread, SET LOCAL together with a (dummy)
> "SET timezone" clause in the function definition can be used to get
> the effect of a function-local setting of the parameter.  I don't
> know of another way to achieve that result above the C-code level.
> 
>             regards, tom lane

Sorry, I was implying that you could use the generated timestamp without 
timezone as a string and supply the necessary timezone:

select to_char(timestamptz(timezone('UTC',tstz) || ' CST6CDT'), 
'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM') from tstz ;
               to_char
------------------------------------
  2024-01-11 23:29:00.0493300 -06:00
(1 row)

-- 
Jim Nasby, Data Architect, Austin TX




Re: Time zone offset in to_char()

From
Jim Nasby
Date:
On 1/11/24 6:20 PM, Jim Nasby wrote:
> On 1/11/24 5:53 PM, Tom Lane wrote:
>> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>> test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', 'YYYY-MM-DD
>>> HH24:MI:SS.US0 TZH:TZM') ;
>>>                 to_char
>>> ------------------------------------
>>>    2024-01-12 00:44:57.5421420 +00:00
>>> (1 row)
>>
>>> You end up with string that does not the correct offset as the AT TIME
>>> ZONE outputs a timestamp not timestamptz value.
>>
>> Yeah.  to_char() does not have any source for the TZ/TZH/TZM fields
>> other than the prevailing value of the timezone parameter, so you
>> really have to set that the way you want if you desire to use these
>> format fields.  As noted upthread, SET LOCAL together with a (dummy)
>> "SET timezone" clause in the function definition can be used to get
>> the effect of a function-local setting of the parameter.  I don't
>> know of another way to achieve that result above the C-code level.
>>
>>             regards, tom lane
> 
> Sorry, I was implying that you could use the generated timestamp without 
> timezone as a string and supply the necessary timezone:
> 
> select to_char(timestamptz(timezone('UTC',tstz) || ' CST6CDT'), 
> 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM') from tstz ;
>                to_char
> ------------------------------------
>   2024-01-11 23:29:00.0493300 -06:00
> (1 row)

NEVERMIND... I see now that doesn't actually work.

Perhaps we should add a variant of timezone() that handles this use-case...
-- 
Jim Nasby, Data Architect, Austin TX




Re: Time zone offset in to_char()

From
Alban Hertroijs
Date:

> 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, ‘YYYY-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, 'YYYY-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, 'YYYY-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, 'YYYY-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()

From
Alban Hertroijs
Date:
From: Adrian Klaver <adrian.klaver@aklaver.com>

> 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_, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
end;
$$;
CREATE FUNCTION

ciscache=> select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), to_char(current_timestamp, 'YYYY-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()

From
Adrian Klaver
Date:
On 1/12/24 00:51, Alban Hertroijs wrote:

> 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 2^nd  select) into the 
> (same) transaction, whereas the function with the time zone bound to the 
> header does not (the 3^rd  select).
> These are all from a single session, ran consecutively - a straight 
> copy-paste from psql.


Alright I see what you are talking about.

> 
> ciscache=> select ToDatetimeOffset(current_timestamp, 
> 'Europe/Amsterdam'), to_char(current_timestamp, 'YYYY-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, 'YYYY-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, 'YYYY-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.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Time zone offset in to_char()

From
Adrian Klaver
Date:
On 1/12/24 02:01, Alban Hertroijs wrote:

> ciscache=> select ToDatetimeOffset(current_timestamp, 
> 'Europe/Amsterdam'), to_char(current_timestamp, 'YYYY-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.

An example of how to deal with this:

CREATE OR REPLACE FUNCTION public.todatetimeoffset(ts_ timestamp with 
time zone, tz_ text)
  RETURNS character varying
  LANGUAGE plpgsql
AS $function$
begin
         --perform set_config('timezone', tz_, true /* local */);
         EXECUTE 'set local timezone to ' || quote_literal(tz_);
         return to_char(ts_, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
end;
$function$

select ToDatetimeOffset(now(), 'Europe/Amsterdam');
           todatetimeoffset
------------------------------------
  2024-01-12 18:32:02.5486820 +01:00

-- 
Adrian Klaver
adrian.klaver@aklaver.com