SV: where clauses including timstamptz and intervals

>-----Oprindelig meddelelse-----
>Fra: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>Emne: Re: where clauses including timstamptz and intervals
>
>Ron <ronljohnsonjr(at)gmail(dot)com> writes:
>> On 4/9/21 5:24 AM, hubert depesz lubaczewski wrote:
>>> For example, in my timezone:
>>>
>>> (1 row)
>>>
>>> Please note that there is 1 hour difference.
>>> The reason is that somewhere in there we change time due to daylight
>>> savings.
>
>> That looks like a flaw in how "month" is calculated. Whether or not
>> October is 744 hours (31 days x 24 hours/day) or 743 hours
>> (subtracting the "fall back" hour), one month from 01-Oct-2020 is still 01-Nov-2020.
>
>No, it's more likely because the calculation was done in some time zone other than UTC, which (somewhat confusingly) is what the starting value was specified in. Assuming Hubert meant EU rules, we have
>
>regression=# set timezone = 'Europe/Paris'; SET regression=# select '2020-10-01 00:00:00+00'::timestamptz;
> timestamptz
>------------------------
> 2020-10-01 02:00:00+02
>(1 row)
>
>regression=# select '2020-10-01 00:00:00+00'::timestamptz + interval '1 month';
> ?column?
>------------------------
> 2020-11-01 02:00:00+01
>(1 row)
>
>which looks to me like the addition did exactly what it's supposed to, ie same local time 1 month later.
>
> regards, tom lane
>

I hope this sums it up.

select ('2020-10-01 00:00:00+00'::timestamptz at time zone 'utc' + interval '1 month') as c1,
'2020-11-01 00:00:00+00'::timestamptz at time zone 'utc' as c2,
('2020-10-01 00:00:00+00'::timestamptz at time zone 'utc' + interval '1 month') =
('2020-11-01 00:00:00+00'::timestamptz at time zone 'utc') as c3,
('2020-10-01 00:00:00+00'::timestamptz at time zone 'Europe/Brussels' + interval '1 month') as c4,
'2020-11-01 00:00:00+00'::timestamptz at time zone 'Europe/Brussels' as c5,
('2020-10-01 00:00:00+00'::timestamptz at time zone 'Europe/Brussels' + interval '1 month') =
('2020-11-01 00:00:00+00'::timestamptz at time zone 'Europe/Brussels') as c6;

returns

c1 | c2 | c3 | c4 | c5 | c6
---------------------+---------------------+----+---------------------+---------------------+----
2020-11-01 00:00:00 | 2020-11-01 00:00:00 | t | 2020-11-01 02:00:00 | 2020-11-01 01:00:00 | f
(1 row)

The answer to my original problem behind my original question which is how to query using utc timing in order to hit exactly the (utc created) partition, without changing the session timezone, would be like this:

select f.* from f
where f.r_time at time zone 'utc' >= '2020-10-01 00:00:00+00'::timestamptz at time zone 'utc'
and f.r_time at time zone 'utc' < ('2020-10-01 00:00:00+00'::timestamptz at time zone 'utc' + interval '1 month');

I think maybe it's better to just change the session timezone when this need arises.

Thank you all for feedback.

Regards Niels