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

Am 04.10.2023 um 16:11 schrieb Tom Lane:
> Steve Crawford<scrawford(at)pinpointresearch(dot)com> writes:
>> On Tue, Oct 3, 2023 at 7:33 PM Lincoln Swaine-Moore<lswainemoore(at)gmail(dot)com>
>> wrote:
>>> 5) Ideally, the solution would not involve messing with the
>>> server/connection's value of timezone. (Though I would be interested if
>>> there was a solution that relaxed this constraint and was relatively
>>> safe/compatible with transactions and psycopg2.)
>> Note that setting the time zone is a client/connection setting so if you
>> set it within a transaction, it will stay set when the transaction
>> concludes. But time manipulation is tricky and trying to DIY reinvent the
>> wheel is painful and often buggy. Let PostgreSQL do the work for you.
> Expanding on that philosophy: you should be able to set the timezone
> locally within a function, so that it wouldn't be that hard to make a
> wrapper for generate_series that emulates the 4-argument version added
> in v16.
>
> Rather than messing with manually saving and restoring the prevailing
> zone, I'd let the function SET infrastructure do it for me. Sadly,
> that SET clause only takes literal constant arguments, so it'd go
> roughly like this:
>
> 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';
>
> Setting the zone to UTC is a useless step, but that triggers
> restoring the previous zone when the function exits; simpler
> and probably faster than coding the save/restore explicitly.
>
> Side note: whether this is really "immutable" is a matter for
> debate, since time zone definitions tend to change over time.
> But we chose to mark the new 4-argument version that way,
> so you might as well do so too.
>
> regards, tom lane
>
>

As far as Lincoln describes it, the series is not the real problem here,
but is just intended to be a simplified example of his actual data.The
consideration that you can use the time zone using a function should
apply here...The following SELECT should show as an example that the
desired result can be achieved (TIMEZONE set to 'Etc/UTC'). Variations
of date_bin for 15 minutes or 1 hour should work similarly...

SELECT
    sub.gs AS ts_in_utc
    ,sub.gs AT TIME ZONE 'America/New_York' AS ts_in_local_tz
    ,date_bin('1 days', sub.gs AT TIME ZONE 'America/New_York',
'2023-01-01')
FROM (SELECT generate_series('2023-11-03 00:00:00Z'::timestamptz,
'2023-11-07 00:00:00Z'::timestamptz, '5 minutes'::interval) AS gs) AS sub
WHERE
    sub.gs >= '2023-11-05 00:00:00 America/New_York'::timestamptz AND
sub.gs < '2023-11-06 00:00:00 America/New_York'::timestamptz

--
regards, marian wendt