Am 04.10.2023 um 16:11 schrieb Tom
Lane:
Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> writes:On Tue, Oct 3, 2023 at 7:33 PM Lincoln Swaine-Moore <lswainemoore@xxxxxxxxx> 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
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
regards, marian wendt
