you group by both the UTC timestamp and the local one, you
might end up, say, dividing an hour-long bucket in two for time zones with
half-hour-based offsets, no?
Thanks for the detailed writeup! Definitely helpful to learn more about
what people are using in production to handle this sort of thing
> Really UTC (not that it matters for calculation purposes)
Sorry, yes--I just meant that literally when I run `show timezone` on the
server in question, I get `GMT`.
> As to "other places" in your (or others) code, provided that you are
storing and manipulating your data as timestamp WITH time z
> If you mean that your input will always include an explicit zone
> specification, then this doesn't affect you. What I was thinking
> about was that
>
> select generate_series('2023-10-04 13:30', ...)
> is going to mean different things depending on the zone setting
> that prevails when
> Yeah. One thing to keep in mind is that that might have different
> behavior in terms of the evaluation of the arguments to the function,
> ie which timezone setting is your input parsed according to.
I see. You mean, in the event that it doesn't conform to an entry in
`pg_timezone_names`? I do
4 PM Tom Lane wrote:
> Lincoln Swaine-Moore writes:
> >>> create function generate_series(timestamptz, timestamptz, interval,
> text)
> >>> returns setof timestamptz
> >>> strict immutable language plpgsql as
> >>> $$
> >>> begin
> >&
I guess the
3rd parameter to set_config is `is_local` (based on
https://pgpedia.info/s/set_config.html). 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? Apologies if these are naive questions.
Thanks again for all the help.
Best
0:00+00
2023-11-05 04:00:00+00 | 2023-11-06 04:00:00+00
2023-11-06 04:00:00+00 | 2023-11-07 04:00:00+00
[etc.]
set timezone to 'America/New_York';
[same as above]
start_time |end_time
----+----
2023-11-03 00:00:00-04 | 2023-11-04 00:00:00-04
2023-11-04 00:00:00-04 | 2023-11-05 00:00:00-04
2023-11-05 00:00:00-04 | 2023-11-06 00:00:00-05
2023-11-06 00:00:00-05 | 2023-11-07 00:00:00-05
[etc.]
```
The latter being correct for these purposes, but not seeming super
reliable/practical (see: point 5).
Can anyone think of any other approaches to this problem? I'd be thrilled
if I could manage it without resorting to manually post-processing in
pandas or something, because I have a number of datapoints, and each
individual one is quite wide.
Apologies for the length of this message--just trying to be thorough. I
sincerely appreciate any help or pointers!
Best,
Lincoln
--
Lincoln Swaine-Moore
