John Naylor <john.nay...@enterprisedb.com> writes:
> On Wed, Sep 1, 2021 at 5:32 AM Aleksander Alekseev <aleksan...@timescale.com>
> wrote:
>> By looking at timestamptz_bin() implementation I don't see why it
>> should be STABLE. Its return value depends only on the input values.
>> It doesn't look at the session parameters. timestamptz_in() and
>> timestamptz_out() are STABLE, that's true, but this is no concern of
>> timestamptz_bin().
> I'm not quite willing to bet the answer couldn't change if the timezone
> changes, but it's possible I'm the one missing something.
After playing with it for awhile, it seems like the behavior is indeed
not TZ-dependent, but the real question is should it be?
As an example,
regression=# set timezone to 'America/New_York';
SET
regression=# select date_bin('1 day', '2021-11-01 00:00 +00'::timestamptz,
'2021-09-01 00:00 -04'::timestamptz);
date_bin
------------------------
2021-10-31 00:00:00-04
(1 row)
regression=# select date_bin('1 day', '2021-11-10 00:00 +00'::timestamptz,
'2021-09-01 00:00 -04'::timestamptz);
date_bin
------------------------
2021-11-08 23:00:00-05
(1 row)
I see that these two answers are both exactly multiples of 24 hours away
from the given origin. But if I'm binning on the basis of "days" or
larger units, I would sort of expect to get local midnight, and I'm not
getting that once I cross a DST boundary.
If this is indeed the behavior we want, I concur with Aleksander
that date_bin isn't TZ-sensitive and needn't be marked STABLE.
regards, tom lane
