Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones

> No. The function call mechanism will ensure that timezone goes back
> to its previous state at function exit.

> An actual rollback would undo the effects of set_config, yes. You
> only need this function wrapper to ensure that subsequent operations
> in the same transaction don't see the setting change.

Excellent, thank you. So just to be explicit here, I could either run this
function, or set/run my query/set back, with the same behavior/safety
guarantees as if I was using the generate_series function with timezone
from v16?

On Wed, Oct 4, 2023 at 12:54 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Lincoln Swaine-Moore <lswainemoore(at)gmail(dot)com> writes:
> >>> create function generate_series(timestamptz, timestamptz, interval,
> text)
> >>> returns setof timestamptz
> >>> strict immutable language plpgsql as
> >>> $$
> >>> begin
> >>> perform set_config('timezone', $4, true);
> >>> return query select generate_series($1, $2, $3);
> >>> end
> >>> $$ set timezone = 'UTC';
>
> > This is a nice suggestion, and in fact, it would be fine from my
> > perspective to reset to UTC every time. My concern is only around the
> > safety of the final `set timezone`. Under what circumstances/when can I
> > count on that being set? E.g. if a query using that function was
> cancelled
> > before finishing, would the connection timezone remain as $4?
>
> No. The function call mechanism will ensure that timezone goes back
> to its previous state at function exit. (In the case of an error
> exit, that's actually handled by the transaction abort logic, but the
> result is the same.) Because of that, I think it doesn't really
> matter whether the set_config call says "true" or "false", but saying
> that it's a local setting seems less confusing.
>
> > Does that mean I could run this
> > outside this context of a function, and expect the setting to go back to
> > UTC on a rollback?
>
> An actual rollback would undo the effects of set_config, yes. You
> only need this function wrapper to ensure that subsequent operations
> in the same transaction don't see the setting change.
>
> regards, tom lane
>

--
Lincoln Swaine-Moore