Covering the comparison between date and timestamp, tz, type
Hi,
Using the PostgreSQL code coverage report, I found that tests for
comparisons between date and timestamp[tz] are missing. Some of them have
only partial coverage.
Attached patch will cover following functions:
date_eq_timestamp
date_ne_timestamp
date_lt_timestamp
date_gt_timestamp // already covered
date_le_timestamp
date_ge_timestamp
date_eq_timestamptz
date_ne_timestamptz
date_lt_timestamptz // already covered
date_gt_timestamptz // already covered
date_le_timestamptz
date_ge_timestamptz
timestamp_eq_date
timestamp_ne_date
timestamp_lt_date
timestamp_gt_date // already covered
timestamp_le_date
timestamp_ge_date
timestamptz_eq_date
timestamptz_ne_date
timestamptz_lt_date
timestamptz_gt_date // already covered
timestamptz_le_date
timestamptz_ge_date // already covered
This is a minor patch, but I’d like to submit it to enhance test coverage.
Best regards,
Kwangwon Seo
Attachments:
v1-0001-Add-test-for-extra-comparison-between-date-and-timestamp.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Add-test-for-extra-comparison-between-date-and-timestamp.patchDownload
From 6031a064cbfb5581a159d160f3dbb1d8a3d81b6b Mon Sep 17 00:00:00 2001
From: Kwangwon Seo <keiseo@protonmail.com>
Date: Fri, 28 Mar 2025 17:58:49 +0900
Subject: [PATCH v1] Add test for extra comparison between date and
timestamp(tz)
1. Add missing resgression tests for comparison between date and timestamp
---
src/test/regress/expected/horology.out | 160 +++++++++++++++++++++++++
src/test/regress/sql/horology.sql | 52 ++++++++
2 files changed, 212 insertions(+)
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 12eefb09d4d..03ad3a75084 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -2500,6 +2500,166 @@ SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::timestamp as t;
t
(1 row)
+RESET TimeZone;
+--
+-- Comparisons between date and timestamp types
+---
+SELECT '2025-03-28'::date = '2025-03-28 00:00:00'::timestamp as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28'::date <> '2025-03-28 00:00:01'::timestamp as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28'::date < '2025-03-28 00:00:01'::timestamp as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28'::date <= '2025-03-28 00:00:00'::timestamp as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28'::date > '2025-03-27 23:59:59'::timestamp as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28'::date >= '2025-03-28 00:00:00'::timestamp as t;
+ t
+---
+ t
+(1 row)
+
+--
+-- Comparisons between timestamp and date types
+---
+SELECT '2025-03-28 00:00:00'::timestamp = '2025-03-28'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28 00:00:00'::timestamp <> '2025-03-27'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28 00:00:00'::timestamp < '2025-03-29'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28 00:00:00'::timestamp <= '2025-03-28'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28 00:00:00'::timestamp > '2025-03-27'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28 00:00:00'::timestamp >= '2025-03-28'::date as t;
+ t
+---
+ t
+(1 row)
+
+--
+-- Comparisons between date and timestamptz types
+---
+SET TimeZone to 'UTC';
+SELECT '2025-03-28'::date = '2025-03-28 00:00:00'::timestamptz as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28'::date <> '2025-03-28 00:00:01'::timestamptz as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28'::date < '2025-03-28 00:00:01'::timestamptz as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28'::date <= '2025-03-28 00:00:00'::timestamptz as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28'::date > '2025-03-27 23:59:59'::timestamptz as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28'::date >= '2025-03-28 00:00:00'::timestamptz as t;
+ t
+---
+ t
+(1 row)
+
+RESET TimeZone;
+--
+-- Comparisons between timestamptz and date types
+---
+SET TimeZone to 'UTC';
+SELECT '2025-03-28 00:00:00'::timestamptz = '2025-03-28'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28 00:00:00'::timestamptz <> '2025-03-27'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28 00:00:00'::timestamptz < '2025-03-29'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28 00:00:00'::timestamptz <= '2025-03-28'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28 00:00:00'::timestamptz > '2025-03-27'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2025-03-28 00:00:00'::timestamptz >= '2025-03-28'::date as t;
+ t
+---
+ t
+(1 row)
+
RESET TimeZone;
--
-- Tests for BETWEEN
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 86481637223..e85ba6e1b4c 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -376,6 +376,58 @@ SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::timestamp as t;
RESET TimeZone;
+--
+-- Comparisons between date and timestamp types
+---
+
+SELECT '2025-03-28'::date = '2025-03-28 00:00:00'::timestamp as t;
+SELECT '2025-03-28'::date <> '2025-03-28 00:00:01'::timestamp as t;
+SELECT '2025-03-28'::date < '2025-03-28 00:00:01'::timestamp as t;
+SELECT '2025-03-28'::date <= '2025-03-28 00:00:00'::timestamp as t;
+SELECT '2025-03-28'::date > '2025-03-27 23:59:59'::timestamp as t;
+SELECT '2025-03-28'::date >= '2025-03-28 00:00:00'::timestamp as t;
+
+--
+-- Comparisons between timestamp and date types
+---
+
+SELECT '2025-03-28 00:00:00'::timestamp = '2025-03-28'::date as t;
+SELECT '2025-03-28 00:00:00'::timestamp <> '2025-03-27'::date as t;
+SELECT '2025-03-28 00:00:00'::timestamp < '2025-03-29'::date as t;
+SELECT '2025-03-28 00:00:00'::timestamp <= '2025-03-28'::date as t;
+SELECT '2025-03-28 00:00:00'::timestamp > '2025-03-27'::date as t;
+SELECT '2025-03-28 00:00:00'::timestamp >= '2025-03-28'::date as t;
+
+--
+-- Comparisons between date and timestamptz types
+---
+
+SET TimeZone to 'UTC';
+
+SELECT '2025-03-28'::date = '2025-03-28 00:00:00'::timestamptz as t;
+SELECT '2025-03-28'::date <> '2025-03-28 00:00:01'::timestamptz as t;
+SELECT '2025-03-28'::date < '2025-03-28 00:00:01'::timestamptz as t;
+SELECT '2025-03-28'::date <= '2025-03-28 00:00:00'::timestamptz as t;
+SELECT '2025-03-28'::date > '2025-03-27 23:59:59'::timestamptz as t;
+SELECT '2025-03-28'::date >= '2025-03-28 00:00:00'::timestamptz as t;
+
+RESET TimeZone;
+
+--
+-- Comparisons between timestamptz and date types
+---
+
+SET TimeZone to 'UTC';
+
+SELECT '2025-03-28 00:00:00'::timestamptz = '2025-03-28'::date as t;
+SELECT '2025-03-28 00:00:00'::timestamptz <> '2025-03-27'::date as t;
+SELECT '2025-03-28 00:00:00'::timestamptz < '2025-03-29'::date as t;
+SELECT '2025-03-28 00:00:00'::timestamptz <= '2025-03-28'::date as t;
+SELECT '2025-03-28 00:00:00'::timestamptz > '2025-03-27'::date as t;
+SELECT '2025-03-28 00:00:00'::timestamptz >= '2025-03-28'::date as t;
+
+RESET TimeZone;
+
--
-- Tests for BETWEEN
--
--
2.43.0
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed
Hi Kwangwon,
I have reviewed your patch.
Funtions you mention are located at src/backend/utils/adt/date.c
I tested and generated coverage report at fb844b9f06568
lines hit: 888 total: 1209 Coverage: 73.4 %
applied your patch, tested and generated report again
lines hit: 960 total: 1209 Coverage: 79.4 %
all the functions listed are now covered
date_eq_timestamp // covered
date_ne_timestamp // covered
date_lt_timestamp // covered
date_gt_timestamp // already covered
date_le_timestamp // covered
date_ge_timestamp // covered
date_eq_timestamptz // covered
date_ne_timestamptz // covered
date_lt_timestamptz // already covered
date_gt_timestamptz // already covered
date_le_timestamptz // covered
date_ge_timestamptz // covered
timestamp_eq_date // covered
timestamp_ne_date // covered
timestamp_lt_date // covered
timestamp_gt_date // already covered
timestamp_le_date // covered
timestamp_ge_date // covered
timestamptz_eq_date // covered
timestamptz_ne_date // covered
timestamptz_lt_date // covered
timestamptz_gt_date // already covered
timestamptz_le_date // covered
timestamptz_ge_date // already covered
Thank you for the patch, your patch looks good to me!
Regards
Rustam
The new status of this patch is: Ready for Committer
Kwangwon Seo <anchovyseo@gmail.com> writes:
Using the PostgreSQL code coverage report, I found that tests for
comparisons between date and timestamp[tz] are missing. Some of them have
only partial coverage.
Attached patch will cover following functions:
...
I'm really not very excited about adding regression test cycles
forevermore just to make these functions show as covered in the
coverage report. They are all trivial wrappers around some
"internal" comparison function, and IMO what's important to
cover is that internal function. We don't necessarily need to
check every one of the wrappers, so long as the internal function
is fully exercised.
There are other functions in these files that'd be more worthy of
dedicated tests, because they are not just trivial wrappers around
something else. For instance, it doesn't look like date_decrement,
date_increment, date_skipsupport are reached at all.
regards, tom lane