Fix for edge case in date_bin() function

From: Moaaz Assali <ma5679(at)nyu(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Fix for edge case in date_bin() function
Date: 2024-02-27 08:42:26
Message-ID: CALkF+nvtuas-2kydG-WfofbRSJpyODAJWun==W-yO5j2R4meqA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

The date_bin() function has a bug where it returns an incorrect binned date
when both of the following are true:
1) the origin timestamp is before the source timestamp
2) the origin timestamp is exactly equivalent to some valid binned date in
the set of binned dates that date_bin() can return given a specific stride
and source timestamp.

For example, consider the following function call:
date_bin('30 minutes'::interval, '2024-01-01 15:00:00'::timestamp,
'2024-01-01 17:00:00'::timestamp);

This function call will return '2024-01-01 14:30:00' instead of '2024-01-01
15:00:00' despite '2024-01-01 15:00:00' being the valid binned date for the
timestamp '2024-01-01 15:00:00'. This commit fixes that by editing the
timestamp_bin() function in timestamp.c file.

The reason this happens is that the code in timestamp_bin() that allows for
correct date binning when source timestamp < origin timestamp subtracts one
stride in all cases.
However, that is not valid for this case when the source timestamp is
exactly equivalent to a valid binned date as in the example mentioned above.

To account for this edge, we simply add another condition in the if
statement to not perform the subtraction by one stride interval if the time
difference is divisible by the stride.

Best regards,
Moaaz Assali

Attachment Content-Type Size
v1-0001-Fix-for-edge-case-in-date_bin-function.patch application/octet-stream 1.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Gustafsson 2024-02-27 08:48:12 Re: Fix for edge case in date_bin() function
Previous Message Hayato Kuroda (Fujitsu) 2024-02-27 08:37:34 RE: Have pg_basebackup write "dbname" in "primary_conninfo"?