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@pinpointresearch.com> writes:

On Tue, Oct 3, 2023 at 7:33 PM Lincoln Swaine-Moore <lswainemoore@gmail.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