public inbox for [email protected]
help / color / mirror / Atom feedFrom: Moaaz Assali <[email protected]>
To: [email protected]
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Fix for edge case in date_bin() function
Date: Tue, 27 Feb 2024 14:27:46 +0400
Message-ID: <CALkF+ns_ghEdVv2_mh=iCuirGnJHowtV0e_wg9_0RgkADo6dJg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CALkF+nvtuas-2kydG-WfofbRSJpyODAJWun==W-yO5j2R4meqA@mail.gmail.com>
<[email protected]>
Hello Daniel,
I have added a test case for this in timestamp.sql and timestamp.out, and
tests pass when using the bug fix patch in the first email.
I have attached a new patch in this email below with the new tests only
(doesn't include the bug fix).
P.S. I forgot to CC the mailing list in my previous reply. This is just a
copy of it.
Best regards,
Moaaz Assali
On Tue, Feb 27, 2024 at 12:48 PM Daniel Gustafsson <[email protected]> wrote:
> > On 27 Feb 2024, at 09:42, Moaaz Assali <[email protected]> wrote:
>
> > 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.
>
> I only skimmed the patch, but I recommend adding a testcase to it to keep
> the
> regression from reappearing. src/test/regress/sql/timestamp.sql might be a
> good candidate testsuite.
>
> --
> Daniel Gustafsson
>
>
Attachments:
[application/octet-stream] v1-0002-Regression-test-in-timestamp.sql-for-date_bin.patch (2.7K, ../CALkF+ns_ghEdVv2_mh=iCuirGnJHowtV0e_wg9_0RgkADo6dJg@mail.gmail.com/3-v1-0002-Regression-test-in-timestamp.sql-for-date_bin.patch)
download | inline diff:
From f6b7f3d8b944c9dd4bd750283c936bc9c6967041 Mon Sep 17 00:00:00 2001
From: Moaaz Assali <[email protected]>
Date: Tue, 27 Feb 2024 09:59:19 +0000
Subject: [PATCH v1] Regression test in timestamp.sql for date_bin() function.
Added the test case for the bug fix that handles the edge case in date_bin() when
source < origin and origin is equivalent to a valid binned date.
Refer to CALkF+nvtuas-2kydG-WfofbRSJpyODAJWun==W-yO5j2R4meqA@mail.gmail.com for the bug fix commit.
Added the test case SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00');
The expected returned value for this test is a single row with Thu Feb 01 15:00:00 2024
Prior to the bug fix, date_bin() would return Thu Feb 01 14:30:00 2024 instead of Thu Feb 01 15:00:00 2024.
This test case ensures the correct return value alongside the bug fix patch.
---
src/test/regress/expected/timestamp.out | 7 +++++++
src/test/regress/sql/timestamp.sql | 3 +++
2 files changed, 10 insertions(+)
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 835f0e5762..34f2cf36b7 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -736,6 +736,13 @@ SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2
Sat Feb 01 00:57:30 2020
(1 row)
+-- test date_bin output is correct when source < origin and source is equivalent to a valid binned date
+SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00');
+ date_bin
+--------------------------
+ Thu Feb 01 15:00:00 2024
+(1 row)
+
-- disallow intervals with months or years
SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
ERROR: timestamps cannot be binned into intervals containing months or years
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index ea12ffd18d..b06afd15db 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -268,6 +268,9 @@ FROM (
-- shift bins using the origin parameter:
SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2020-02-01 00:02:30');
+-- test date_bin output is correct when source < origin and source is equivalent to a valid binned date
+SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00');
+
-- disallow intervals with months or years
SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
SELECT date_bin('5 years'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
--
2.34.1
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Fix for edge case in date_bin() function
In-Reply-To: <CALkF+ns_ghEdVv2_mh=iCuirGnJHowtV0e_wg9_0RgkADo6dJg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox