Horton

Re: truncating timestamps on arbitrary intervals

John Naylor <john.naylor@enterprisedb.com>

View thread View on postgr.es
From: John Naylor <john.naylor@enterprisedb.com>
To: Bauyrzhan Sakhariyev <baurzhansahariev@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2021-07-22T17:28:38Z
Lists: pgsql-hackers 
On Thu, Jul 22, 2021 at 12:24 PM Bauyrzhan Sakhariyev <
baurzhansahariev@gmail.com> wrote:
>
> Is date_bin supposed to return the beginning of the bin?

Thanks for testing! And yes.

> And does the sign of an interval define the "direction" of the bin?

No, the boundary is intentionally the earlier one:

/*
 * Make sure the returned timestamp is at the start of the bin, even if
 * the origin is in the future.
 */
if (origin > timestamp && stride_usecs > 1)
    tm_delta -= stride_usecs;

I wonder if we should just disallow negative intervals here.

--
John Naylor
EDB: http://www.enterprisedb.com

Commits

  1. Disallow negative strides in date_bin()

  2. Improve behavior of date_bin with origin in the future

  3. doc: Additional documentation for date_bin

  4. Add date_bin function