[PATCH] Add mssql_compat extension with DATEDIFF function
Started by Myles Lewisabout 2 months ago7 messages
I'd like to propose a new contrib extension: mssql_compat, which provides
SQL Server compatible date functions starting with DATEDIFF.
Problem: PostgreSQL lacks a native DATEDIFF() function, forcing users to
write verbose date arithmetic. Organizations migrating from SQL Server
encounter friction when porting date-based business logic.
Solution: datediff(datepart, start_date, end_date) returns precise,
contextually-aware date differences using a hybrid calculation model.
Key features:
- Supports day, week, month, quarter, year (with SQL Server aliases)
- Returns NUMERIC with 3 decimal precision
- Handles DATE, TIMESTAMP, TIMESTAMPTZ types
- IMMUTABLE STRICT PARALLEL SAFE
The patch:
- Compiles cleanly with no warnings
- Includes regression tests (all pass)
- Follows PostgreSQL coding conventions
- Based on current master
Attached: 0001-feat-mssql_compat-Add-DATEDIFF-extension-for-SQL-Ser.patch
Myles
Attachments:
0001-feat-mssql_compat-Add-DATEDIFF-extension-for-SQL-Ser.patchapplication/octet-stream; name=0001-feat-mssql_compat-Add-DATEDIFF-extension-for-SQL-Ser.patch; x-unix-mode=0644Download
From d76e0e459a9e9293df1d10728a2b3c4b79d4a2cb Mon Sep 17 00:00:00 2001
From: mlx93 <mylesethan93@gmail.com>
Date: Mon, 24 Nov 2025 20:47:43 -0600
Subject: [PATCH] feat(mssql_compat): Add DATEDIFF extension for SQL Server
compatibility
Implements datediff(datepart, start_date, end_date) function per PRD1a/PRD1b specs:
- Supports day, week, month, quarter, year dateparts with aliases
- Hybrid calculation model: full calendar units + contextual fractions
- Returns NUMERIC with 3 decimal precision (banker's rounding)
- Handles DATE, TIMESTAMP, and TIMESTAMPTZ input types
- Calendar alignment detection for whole-number returns
- Negative span handling (start > end returns negative)
- Case-insensitive datepart parsing
- IMMUTABLE STRICT PARALLEL SAFE function attributes
Files added:
- contrib/mssql_compat/mssql_compat.c - C implementation (~750 lines)
- contrib/mssql_compat/mssql_compat--1.0.sql - SQL function declarations
- contrib/mssql_compat/mssql_compat.control - Extension metadata
- contrib/mssql_compat/Makefile - PGXS build config
- contrib/mssql_compat/meson.build - Meson build config
- contrib/mssql_compat/sql/mssql_compat.sql - Regression tests
- contrib/mssql_compat/expected/mssql_compat.out - Expected output
- contrib/mssql_compat/sql/datediff_comprehensive_tests.sql - 50+ test cases
All regression tests pass.
---
contrib/meson.build | 1 +
contrib/mssql_compat/Makefile | 21 +
.../mssql_compat/expected/mssql_compat.out | 519 ++++++++++++
contrib/mssql_compat/meson.build | 36 +
contrib/mssql_compat/mssql_compat--1.0.sql | 55 ++
contrib/mssql_compat/mssql_compat.c | 751 ++++++++++++++++++
contrib/mssql_compat/mssql_compat.control | 7 +
contrib/mssql_compat/results/mssql_compat.out | 519 ++++++++++++
.../sql/datediff_comprehensive_tests.sql | 598 ++++++++++++++
contrib/mssql_compat/sql/mssql_compat.sql | 173 ++++
10 files changed, 2680 insertions(+)
create mode 100644 contrib/mssql_compat/Makefile
create mode 100644 contrib/mssql_compat/expected/mssql_compat.out
create mode 100644 contrib/mssql_compat/meson.build
create mode 100644 contrib/mssql_compat/mssql_compat--1.0.sql
create mode 100644 contrib/mssql_compat/mssql_compat.c
create mode 100644 contrib/mssql_compat/mssql_compat.control
create mode 100644 contrib/mssql_compat/results/mssql_compat.out
create mode 100644 contrib/mssql_compat/sql/datediff_comprehensive_tests.sql
create mode 100644 contrib/mssql_compat/sql/mssql_compat.sql
diff --git a/contrib/meson.build b/contrib/meson.build
index ed30ee7d639..6a48df8daf8 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -40,6 +40,7 @@ subdir('jsonb_plpython')
subdir('lo')
subdir('ltree')
subdir('ltree_plpython')
+subdir('mssql_compat')
subdir('oid2name')
subdir('pageinspect')
subdir('passwordcheck')
diff --git a/contrib/mssql_compat/Makefile b/contrib/mssql_compat/Makefile
new file mode 100644
index 00000000000..7dbb3409703
--- /dev/null
+++ b/contrib/mssql_compat/Makefile
@@ -0,0 +1,21 @@
+# contrib/mssql_compat/Makefile
+
+MODULES = mssql_compat
+
+EXTENSION = mssql_compat
+DATA = mssql_compat--1.0.sql
+PGFILEDESC = "mssql_compat - SQL Server compatible date functions"
+
+REGRESS = mssql_compat
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/mssql_compat
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
diff --git a/contrib/mssql_compat/expected/mssql_compat.out b/contrib/mssql_compat/expected/mssql_compat.out
new file mode 100644
index 00000000000..3fd45c1ecc6
--- /dev/null
+++ b/contrib/mssql_compat/expected/mssql_compat.out
@@ -0,0 +1,519 @@
+--
+-- Test cases for mssql_compat extension
+-- Covers PRD1a unit tests (UT-01 to UT-15) and edge cases (EC-01 to EC-06)
+--
+CREATE EXTENSION mssql_compat;
+--
+-- Basic Day Calculations (UT-01, UT-02)
+--
+SELECT 'UT-01: Day difference basic' AS test;
+ test
+-----------------------------
+ UT-01: Day difference basic
+(1 row)
+
+SELECT datediff('day', '2024-01-01'::date, '2024-01-15'::date);
+ datediff
+----------
+ 14
+(1 row)
+
+SELECT 'UT-02: Day difference negative' AS test;
+ test
+--------------------------------
+ UT-02: Day difference negative
+(1 row)
+
+SELECT datediff('day', '2024-01-15'::date, '2024-01-01'::date);
+ datediff
+----------
+ -14
+(1 row)
+
+--
+-- Week Calculations (UT-03, UT-04)
+--
+SELECT 'UT-03: Week exact' AS test;
+ test
+-------------------
+ UT-03: Week exact
+(1 row)
+
+SELECT datediff('week', '2024-01-01'::date, '2024-01-08'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'UT-04: Week partial' AS test;
+ test
+---------------------
+ UT-04: Week partial
+(1 row)
+
+SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
+ datediff
+----------
+ 1.286
+(1 row)
+
+--
+-- Month Calculations (UT-05, UT-06, UT-07)
+--
+SELECT 'UT-05: Month aligned' AS test;
+ test
+----------------------
+ UT-05: Month aligned
+(1 row)
+
+SELECT datediff('month', '2024-01-15'::date, '2024-02-15'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'UT-06: Month partial' AS test;
+ test
+----------------------
+ UT-06: Month partial
+(1 row)
+
+SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
+ datediff
+----------
+ 1.172
+(1 row)
+
+SELECT 'UT-07: Month end-of-month alignment' AS test;
+ test
+-------------------------------------
+ UT-07: Month end-of-month alignment
+(1 row)
+
+SELECT datediff('month', '2024-01-31'::date, '2024-02-29'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+--
+-- Quarter Calculations (UT-08, UT-09)
+--
+SELECT 'UT-08: Quarter aligned' AS test;
+ test
+------------------------
+ UT-08: Quarter aligned
+(1 row)
+
+SELECT datediff('quarter', '2024-01-01'::date, '2024-04-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'UT-09: Quarter partial' AS test;
+ test
+------------------------
+ UT-09: Quarter partial
+(1 row)
+
+SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
+ datediff
+----------
+ 1.385
+(1 row)
+
+--
+-- Year Calculations (UT-10, UT-11)
+--
+SELECT 'UT-10: Year aligned' AS test;
+ test
+---------------------
+ UT-10: Year aligned
+(1 row)
+
+SELECT datediff('year', '2024-03-15'::date, '2025-03-15'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'UT-11: Year partial leap year' AS test;
+ test
+-------------------------------
+ UT-11: Year partial leap year
+(1 row)
+
+SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
+ datediff
+----------
+ 0.497
+(1 row)
+
+--
+-- NULL Handling (UT-12, UT-13) - STRICT functions return NULL for NULL inputs
+--
+SELECT 'UT-12: NULL start date' AS test;
+ test
+------------------------
+ UT-12: NULL start date
+(1 row)
+
+SELECT datediff('day', NULL::date, '2024-01-15'::date);
+ datediff
+----------
+
+(1 row)
+
+SELECT 'UT-13: NULL end date' AS test;
+ test
+----------------------
+ UT-13: NULL end date
+(1 row)
+
+SELECT datediff('day', '2024-01-01'::date, NULL::date);
+ datediff
+----------
+
+(1 row)
+
+--
+-- Invalid Datepart (UT-14)
+--
+SELECT 'UT-14: Invalid datepart' AS test;
+ test
+-------------------------
+ UT-14: Invalid datepart
+(1 row)
+
+SELECT datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
+ERROR: Invalid datepart: 'hour'
+HINT: Valid options: year, quarter, month, week, day
+--
+-- Case Insensitivity (UT-15)
+--
+SELECT 'UT-15: Case insensitive datepart' AS test;
+ test
+----------------------------------
+ UT-15: Case insensitive datepart
+(1 row)
+
+SELECT datediff('MONTH', '2024-01-01'::date, '2024-02-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('Month', '2024-01-01'::date, '2024-02-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('month', '2024-01-01'::date, '2024-02-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+--
+-- Edge Cases (EC-01 to EC-06)
+--
+SELECT 'EC-01: Same date' AS test;
+ test
+------------------
+ EC-01: Same date
+(1 row)
+
+SELECT datediff('day', '2024-01-01'::date, '2024-01-01'::date);
+ datediff
+----------
+ 0
+(1 row)
+
+SELECT 'EC-02: Leap year February 29' AS test;
+ test
+------------------------------
+ EC-02: Leap year February 29
+(1 row)
+
+SELECT datediff('day', '2024-02-28'::date, '2024-03-01'::date);
+ datediff
+----------
+ 2
+(1 row)
+
+SELECT 'EC-03: Non-leap year February' AS test;
+ test
+-------------------------------
+ EC-03: Non-leap year February
+(1 row)
+
+SELECT datediff('day', '2023-02-28'::date, '2023-03-01'::date);
+ datediff
+----------
+ 1
+(1 row)
+
+SELECT 'EC-04: Year boundary' AS test;
+ test
+----------------------
+ EC-04: Year boundary
+(1 row)
+
+SELECT datediff('year', '2024-12-31'::date, '2025-01-01'::date);
+ datediff
+----------
+ 0.003
+(1 row)
+
+SELECT 'EC-05: Multi-year span' AS test;
+ test
+------------------------
+ EC-05: Multi-year span
+(1 row)
+
+SELECT datediff('year', '2020-01-01'::date, '2025-01-01'::date);
+ datediff
+----------
+ 5.000
+(1 row)
+
+SELECT 'EC-06: Century boundary' AS test;
+ test
+-------------------------
+ EC-06: Century boundary
+(1 row)
+
+SELECT datediff('day', '1999-12-31'::date, '2000-01-01'::date);
+ datediff
+----------
+ 1
+(1 row)
+
+--
+-- Alias Tests (from PRD1b lines 224-230)
+--
+SELECT 'Alias: yy for year' AS test;
+ test
+--------------------
+ Alias: yy for year
+(1 row)
+
+SELECT datediff('yy', '2024-01-01'::date, '2025-01-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'Alias: yyyy for year' AS test;
+ test
+----------------------
+ Alias: yyyy for year
+(1 row)
+
+SELECT datediff('yyyy', '2024-01-01'::date, '2025-01-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'Alias: mm for month' AS test;
+ test
+---------------------
+ Alias: mm for month
+(1 row)
+
+SELECT datediff('mm', '2024-01-15'::date, '2024-02-15'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'Alias: qq for quarter' AS test;
+ test
+-----------------------
+ Alias: qq for quarter
+(1 row)
+
+SELECT datediff('qq', '2024-01-01'::date, '2024-04-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'Alias: wk for week' AS test;
+ test
+--------------------
+ Alias: wk for week
+(1 row)
+
+SELECT datediff('wk', '2024-01-01'::date, '2024-01-08'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'Alias: dd for day' AS test;
+ test
+-------------------
+ Alias: dd for day
+(1 row)
+
+SELECT datediff('dd', '2024-01-01'::date, '2024-01-15'::date);
+ datediff
+----------
+ 14
+(1 row)
+
+--
+-- Timestamp Tests
+--
+SELECT 'Timestamp: basic day diff' AS test;
+ test
+---------------------------
+ Timestamp: basic day diff
+(1 row)
+
+SELECT datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp);
+ datediff
+----------
+ 14
+(1 row)
+
+SELECT 'Timestamp: month diff' AS test;
+ test
+-----------------------
+ Timestamp: month diff
+(1 row)
+
+SELECT datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp);
+ datediff
+----------
+ 1.172
+(1 row)
+
+--
+-- Timestamptz Tests
+--
+SELECT 'Timestamptz: basic day diff' AS test;
+ test
+-----------------------------
+ Timestamptz: basic day diff
+(1 row)
+
+SELECT datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz);
+ datediff
+----------
+ 14
+(1 row)
+
+--
+-- Additional Month Calculation Tests
+--
+SELECT 'Month: Jan 25 to Mar 10 (PRD walkthrough example)' AS test;
+ test
+---------------------------------------------------
+ Month: Jan 25 to Mar 10 (PRD walkthrough example)
+(1 row)
+
+SELECT datediff('month', '2024-01-25'::date, '2024-03-10'::date);
+ datediff
+----------
+ 1.483
+(1 row)
+
+SELECT 'Month: subscription proration example' AS test;
+ test
+---------------------------------------
+ Month: subscription proration example
+(1 row)
+
+SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
+ datediff
+----------
+ 1.172
+(1 row)
+
+--
+-- Additional Quarter Calculation Tests
+--
+SELECT 'Quarter: PRD walkthrough example' AS test;
+ test
+----------------------------------
+ Quarter: PRD walkthrough example
+(1 row)
+
+SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
+ datediff
+----------
+ 1.385
+(1 row)
+
+--
+-- Additional Year Calculation Tests
+--
+SELECT 'Year: PRD walkthrough example' AS test;
+ test
+-------------------------------
+ Year: PRD walkthrough example
+(1 row)
+
+SELECT datediff('year', '2024-03-15'::date, '2025-06-20'::date);
+ datediff
+----------
+ 1.266
+(1 row)
+
+SELECT 'Year: exact 5-year tenure' AS test;
+ test
+---------------------------
+ Year: exact 5-year tenure
+(1 row)
+
+SELECT datediff('year', '2020-03-15'::date, '2025-03-15'::date);
+ datediff
+----------
+ 5.000
+(1 row)
+
+SELECT 'Year: leap year partial (182 days / 366)' AS test;
+ test
+------------------------------------------
+ Year: leap year partial (182 days / 366)
+(1 row)
+
+SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
+ datediff
+----------
+ 0.497
+(1 row)
+
+--
+-- Week Calculation Additional Tests
+--
+SELECT 'Week: exact 2 weeks' AS test;
+ test
+---------------------
+ Week: exact 2 weeks
+(1 row)
+
+SELECT datediff('week', '2024-01-01'::date, '2024-01-15'::date);
+ datediff
+----------
+ 2.000
+(1 row)
+
+SELECT 'Week: PRD example 9 days' AS test;
+ test
+--------------------------
+ Week: PRD example 9 days
+(1 row)
+
+SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
+ datediff
+----------
+ 1.286
+(1 row)
+
+DROP EXTENSION mssql_compat;
diff --git a/contrib/mssql_compat/meson.build b/contrib/mssql_compat/meson.build
new file mode 100644
index 00000000000..d1a412ed879
--- /dev/null
+++ b/contrib/mssql_compat/meson.build
@@ -0,0 +1,36 @@
+# Copyright (c) 2022-2025, PostgreSQL Global Development Group
+
+mssql_compat_sources = files(
+ 'mssql_compat.c',
+)
+
+if host_system == 'windows'
+ mssql_compat_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'mssql_compat',
+ '--FILEDESC', 'mssql_compat - SQL Server compatible date functions',])
+endif
+
+mssql_compat = shared_module('mssql_compat',
+ mssql_compat_sources,
+ kwargs: contrib_mod_args,
+)
+
+contrib_targets += mssql_compat
+
+install_data(
+ 'mssql_compat--1.0.sql',
+ 'mssql_compat.control',
+ kwargs: contrib_data_args,
+)
+
+tests += {
+ 'name': 'mssql_compat',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'regress': {
+ 'sql': [
+ 'mssql_compat',
+ ],
+ },
+}
+
diff --git a/contrib/mssql_compat/mssql_compat--1.0.sql b/contrib/mssql_compat/mssql_compat--1.0.sql
new file mode 100644
index 00000000000..8b950cb4cc4
--- /dev/null
+++ b/contrib/mssql_compat/mssql_compat--1.0.sql
@@ -0,0 +1,55 @@
+/* contrib/mssql_compat/mssql_compat--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION mssql_compat" to load this file. \quit
+
+--
+-- datediff(datepart, start_date, end_date) - SQL Server compatible date difference
+--
+-- Returns the difference between two dates in the specified datepart unit.
+-- Supports: year, quarter, month, week, day (and aliases)
+--
+-- Unlike SQL Server's boundary-crossing semantics, this implementation provides
+-- mathematically accurate results using a hybrid calculation model: full calendar
+-- units plus contextual fractions based on actual period lengths.
+--
+
+-- Date version
+CREATE FUNCTION datediff(
+ datepart TEXT,
+ start_date DATE,
+ end_date DATE
+)
+RETURNS NUMERIC
+AS 'MODULE_PATHNAME', 'datediff_date'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+COMMENT ON FUNCTION datediff(TEXT, DATE, DATE) IS
+'Calculate the difference between two dates in the specified unit (year, quarter, month, week, day)';
+
+-- Timestamp version
+CREATE FUNCTION datediff(
+ datepart TEXT,
+ start_ts TIMESTAMP,
+ end_ts TIMESTAMP
+)
+RETURNS NUMERIC
+AS 'MODULE_PATHNAME', 'datediff_timestamp'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+COMMENT ON FUNCTION datediff(TEXT, TIMESTAMP, TIMESTAMP) IS
+'Calculate the difference between two timestamps in the specified unit (year, quarter, month, week, day)';
+
+-- Timestamptz version
+CREATE FUNCTION datediff(
+ datepart TEXT,
+ start_tstz TIMESTAMPTZ,
+ end_tstz TIMESTAMPTZ
+)
+RETURNS NUMERIC
+AS 'MODULE_PATHNAME', 'datediff_timestamptz'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+COMMENT ON FUNCTION datediff(TEXT, TIMESTAMPTZ, TIMESTAMPTZ) IS
+'Calculate the difference between two timestamps with timezone in the specified unit (year, quarter, month, week, day)';
+
diff --git a/contrib/mssql_compat/mssql_compat.c b/contrib/mssql_compat/mssql_compat.c
new file mode 100644
index 00000000000..1db22f37982
--- /dev/null
+++ b/contrib/mssql_compat/mssql_compat.c
@@ -0,0 +1,751 @@
+/*-------------------------------------------------------------------------
+ *
+ * mssql_compat.c
+ * SQL Server compatible datediff function for PostgreSQL.
+ *
+ * This extension provides datediff(datepart, start_date, end_date) which
+ * calculates the difference between two dates using a hybrid calculation
+ * model: full calendar units plus contextual fractions based on actual
+ * period lengths.
+ *
+ * Copyright (c) 2024, PostgreSQL Global Development Group
+ *
+ * contrib/mssql_compat/mssql_compat.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <ctype.h>
+#include <math.h>
+
+#include "datatype/timestamp.h"
+#include "fmgr.h"
+#include "utils/builtins.h"
+#include "utils/date.h"
+#include "utils/datetime.h"
+#include "utils/numeric.h"
+#include "utils/timestamp.h"
+
+PG_MODULE_MAGIC_EXT(
+ .name = "mssql_compat",
+ .version = PG_VERSION
+);
+
+/*
+ * Datepart enumeration for routing calculation logic
+ */
+typedef enum
+{
+ DATEPART_DAY,
+ DATEPART_WEEK,
+ DATEPART_MONTH,
+ DATEPART_QUARTER,
+ DATEPART_YEAR,
+ DATEPART_INVALID
+} DatepartType;
+
+/*
+ * parse_datepart - convert datepart string to enum
+ *
+ * Performs case-insensitive comparison and handles aliases.
+ * Returns DATEPART_INVALID for unrecognized input.
+ */
+static DatepartType
+parse_datepart(const char *datepart_str)
+{
+ char lower[32];
+ int i;
+
+ /* Convert to lowercase for comparison */
+ for (i = 0; datepart_str[i] && i < 31; i++)
+ lower[i] = tolower((unsigned char) datepart_str[i]);
+ lower[i] = '\0';
+
+ /* Match canonical names and aliases per PRD1b lines 224-230 */
+ if (strcmp(lower, "year") == 0 ||
+ strcmp(lower, "yy") == 0 ||
+ strcmp(lower, "yyyy") == 0 ||
+ strcmp(lower, "y") == 0 ||
+ strcmp(lower, "years") == 0)
+ return DATEPART_YEAR;
+
+ if (strcmp(lower, "quarter") == 0 ||
+ strcmp(lower, "qq") == 0 ||
+ strcmp(lower, "q") == 0 ||
+ strcmp(lower, "quarters") == 0)
+ return DATEPART_QUARTER;
+
+ if (strcmp(lower, "month") == 0 ||
+ strcmp(lower, "mm") == 0 ||
+ strcmp(lower, "m") == 0 ||
+ strcmp(lower, "months") == 0)
+ return DATEPART_MONTH;
+
+ if (strcmp(lower, "week") == 0 ||
+ strcmp(lower, "wk") == 0 ||
+ strcmp(lower, "ww") == 0 ||
+ strcmp(lower, "w") == 0 ||
+ strcmp(lower, "weeks") == 0)
+ return DATEPART_WEEK;
+
+ if (strcmp(lower, "day") == 0 ||
+ strcmp(lower, "dd") == 0 ||
+ strcmp(lower, "d") == 0 ||
+ strcmp(lower, "days") == 0)
+ return DATEPART_DAY;
+
+ return DATEPART_INVALID;
+}
+
+/*
+ * days_in_month_helper - get days in a specific month
+ *
+ * Uses PostgreSQL's day_tab array (per PRD1b lines 235-252)
+ * month is 1-based (1=January, 12=December)
+ */
+static int
+days_in_month_helper(int year, int month)
+{
+ return day_tab[isleap(year) ? 1 : 0][month - 1];
+}
+
+/*
+ * is_end_of_month - check if day is the last day of its month
+ */
+static bool
+is_end_of_month(int year, int month, int day)
+{
+ return day == days_in_month_helper(year, month);
+}
+
+/*
+ * days_in_quarter - get total days in a specific quarter
+ *
+ * Quarter is 1-4.
+ * Q1: Jan+Feb+Mar, Q2: Apr+May+Jun, Q3: Jul+Aug+Sep, Q4: Oct+Nov+Dec
+ */
+static int
+days_in_quarter(int year, int quarter)
+{
+ int first_month = (quarter - 1) * 3 + 1;
+ int days = 0;
+ int i;
+
+ for (i = 0; i < 3; i++)
+ days += days_in_month_helper(year, first_month + i);
+
+ return days;
+}
+
+/*
+ * day_of_quarter - get day position within a quarter (1-92)
+ */
+static int
+day_of_quarter(int year, int month, int day)
+{
+ int quarter = (month - 1) / 3 + 1;
+ int first_month = (quarter - 1) * 3 + 1;
+ int days = 0;
+ int m;
+
+ /* Sum days in complete months before this month within the quarter */
+ for (m = first_month; m < month; m++)
+ days += days_in_month_helper(year, m);
+
+ return days + day;
+}
+
+/*
+ * bankers_round - round to 3 decimal places using HALF_EVEN (banker's rounding)
+ *
+ * Per PRD1a FR-7: "Decimal results SHALL be rounded to exactly 3 decimal places
+ * using HALF_EVEN (banker's) rounding"
+ */
+static double
+bankers_round(double value)
+{
+ double scaled = value * 1000.0;
+ double integer_part;
+ double frac = modf(scaled, &integer_part);
+ int64 int_val = (int64) integer_part;
+
+ /*
+ * Banker's rounding: round half to even
+ * If fraction is exactly 0.5, round to nearest even number
+ */
+ if (fabs(frac) == 0.5)
+ {
+ /* Round to even */
+ if (int_val % 2 == 0)
+ scaled = integer_part; /* Already even, truncate */
+ else
+ scaled = integer_part + (value >= 0 ? 1.0 : -1.0); /* Round away */
+ }
+ else
+ {
+ /* Standard rounding */
+ scaled = round(scaled);
+ }
+
+ return scaled / 1000.0;
+}
+
+/*
+ * make_numeric_result - convert double to NUMERIC with 3 decimal places
+ *
+ * Uses string conversion approach as per PRD1b lines 167-174
+ */
+static Datum
+make_numeric_result(double value)
+{
+ char result_str[32];
+ Datum result;
+
+ snprintf(result_str, sizeof(result_str), "%.3f", value);
+ result = DirectFunctionCall3(numeric_in,
+ CStringGetDatum(result_str),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1));
+ return result;
+}
+
+/*
+ * compute_diff_day - calculate day difference
+ *
+ * Simple subtraction, returns whole number as NUMERIC.
+ */
+static Datum
+compute_diff_day(int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ int start_jd = date2j(start_y, start_m, start_d);
+ int end_jd = date2j(end_y, end_m, end_d);
+ int64 diff = (int64) end_jd - (int64) start_jd;
+
+ return NumericGetDatum(int64_to_numeric(diff));
+}
+
+/*
+ * compute_diff_week - calculate week difference
+ *
+ * Total days / 7, rounded to 3 decimal places.
+ */
+static Datum
+compute_diff_week(int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ int start_jd = date2j(start_y, start_m, start_d);
+ int end_jd = date2j(end_y, end_m, end_d);
+ int64 days = (int64) end_jd - (int64) start_jd;
+ double weeks = (double) days / 7.0;
+
+ return make_numeric_result(bankers_round(weeks));
+}
+
+/*
+ * compute_diff_month - calculate month difference using hybrid model
+ *
+ * Per PRD1a FR-3/FR-4:
+ * - Aligned dates (same day-of-month or both end-of-month) return whole numbers
+ * - Non-aligned: full months + (remaining days / days in partial period)
+ */
+static Datum
+compute_diff_month(int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ bool negated = false;
+ int full_months;
+ int remaining_days;
+ int partial_period_days;
+ double result;
+ bool start_eom;
+ bool end_eom;
+ bool aligned;
+ int anniversary_y, anniversary_m, anniversary_d;
+ int anniversary_jd, end_jd;
+
+ /* Handle negative spans by swapping and negating result (FR-6) */
+ if (start_y > end_y ||
+ (start_y == end_y && start_m > end_m) ||
+ (start_y == end_y && start_m == end_m && start_d > end_d))
+ {
+ int tmp_y = start_y, tmp_m = start_m, tmp_d = start_d;
+
+ start_y = end_y;
+ start_m = end_m;
+ start_d = end_d;
+ end_y = tmp_y;
+ end_m = tmp_m;
+ end_d = tmp_d;
+ negated = true;
+ }
+
+ /* Check for calendar alignment (FR-4) */
+ start_eom = is_end_of_month(start_y, start_m, start_d);
+ end_eom = is_end_of_month(end_y, end_m, end_d);
+ aligned = (start_d == end_d) || (start_eom && end_eom);
+
+ /* Calculate full months */
+ full_months = (end_y - start_y) * 12 + (end_m - start_m);
+
+ if (aligned)
+ {
+ /* Aligned dates return whole numbers */
+ result = (double) full_months;
+ }
+ else
+ {
+ /*
+ * Find the last "anniversary" before or on end_date.
+ * Anniversary is the same day-of-month as start_d, or end-of-month
+ * if start was end-of-month.
+ */
+ if (end_d < start_d)
+ full_months--;
+
+ if (full_months < 0)
+ full_months = 0;
+
+ /* Calculate anniversary date */
+ anniversary_y = start_y + (start_m + full_months - 1) / 12;
+ anniversary_m = ((start_m - 1 + full_months) % 12) + 1;
+
+ /*
+ * Handle case where start_d doesn't exist in anniversary month
+ * (e.g., Jan 31 -> Feb has no 31st)
+ */
+ if (start_d > days_in_month_helper(anniversary_y, anniversary_m))
+ anniversary_d = days_in_month_helper(anniversary_y, anniversary_m);
+ else
+ anniversary_d = start_d;
+
+ /* Calculate remaining days after anniversary */
+ anniversary_jd = date2j(anniversary_y, anniversary_m, anniversary_d);
+ end_jd = date2j(end_y, end_m, end_d);
+ remaining_days = end_jd - anniversary_jd;
+
+ /*
+ * Calculate partial period length (days from anniversary to next
+ * anniversary)
+ */
+ {
+ int next_anniversary_y = anniversary_y + (anniversary_m) / 12;
+ int next_anniversary_m = (anniversary_m % 12) + 1;
+ int next_anniversary_d;
+ int next_anniversary_jd;
+
+ if (start_d > days_in_month_helper(next_anniversary_y, next_anniversary_m))
+ next_anniversary_d = days_in_month_helper(next_anniversary_y, next_anniversary_m);
+ else
+ next_anniversary_d = start_d;
+
+ next_anniversary_jd = date2j(next_anniversary_y, next_anniversary_m, next_anniversary_d);
+ partial_period_days = next_anniversary_jd - anniversary_jd;
+ }
+
+ if (partial_period_days <= 0)
+ partial_period_days = 1; /* Safety guard */
+
+ result = (double) full_months + (double) remaining_days / (double) partial_period_days;
+ }
+
+ if (negated)
+ result = -result;
+
+ return make_numeric_result(bankers_round(result));
+}
+
+/*
+ * compute_diff_quarter - calculate quarter difference using hybrid model
+ *
+ * Similar to month but with quarter-based periods.
+ */
+static Datum
+compute_diff_quarter(int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ bool negated = false;
+ int start_quarter, end_quarter;
+ int start_day_of_qtr, end_day_of_qtr;
+ int full_quarters;
+ int remaining_days;
+ int partial_period_days;
+ double result;
+
+ /* Handle negative spans */
+ if (start_y > end_y ||
+ (start_y == end_y && start_m > end_m) ||
+ (start_y == end_y && start_m == end_m && start_d > end_d))
+ {
+ int tmp_y = start_y, tmp_m = start_m, tmp_d = start_d;
+
+ start_y = end_y;
+ start_m = end_m;
+ start_d = end_d;
+ end_y = tmp_y;
+ end_m = tmp_m;
+ end_d = tmp_d;
+ negated = true;
+ }
+
+ start_quarter = (start_m - 1) / 3 + 1;
+ end_quarter = (end_m - 1) / 3 + 1;
+ start_day_of_qtr = day_of_quarter(start_y, start_m, start_d);
+ end_day_of_qtr = day_of_quarter(end_y, end_m, end_d);
+
+ /* Calculate full quarters */
+ full_quarters = (end_y - start_y) * 4 + (end_quarter - start_quarter);
+
+ /* Check alignment: same day-of-quarter position */
+ if (start_day_of_qtr == end_day_of_qtr)
+ {
+ result = (double) full_quarters;
+ }
+ else
+ {
+ /*
+ * Non-aligned: find anniversary (same position in quarter), calculate
+ * remaining days
+ */
+ int anniversary_y, anniversary_quarter, anniversary_m, anniversary_d;
+ int anniversary_jd, end_jd;
+ /* Adjust full_quarters if end is before anniversary position */
+ if (end_day_of_qtr < start_day_of_qtr)
+ full_quarters--;
+
+ if (full_quarters < 0)
+ full_quarters = 0;
+
+ /* Calculate anniversary date */
+ anniversary_quarter = start_quarter + full_quarters;
+ anniversary_y = start_y + (anniversary_quarter - 1) / 4;
+ anniversary_quarter = ((anniversary_quarter - 1) % 4) + 1;
+
+ /* Convert day-of-quarter back to month and day */
+ {
+ int first_month = (anniversary_quarter - 1) * 3 + 1;
+ int days_remaining = start_day_of_qtr;
+ int m;
+ bool found = false;
+
+ anniversary_m = first_month;
+ anniversary_d = 1; /* Default initialization */
+ for (m = first_month; m <= first_month + 2 && days_remaining > 0; m++)
+ {
+ int days_in_m = days_in_month_helper(anniversary_y, m);
+
+ if (days_remaining <= days_in_m)
+ {
+ anniversary_m = m;
+ anniversary_d = days_remaining;
+ found = true;
+ break;
+ }
+ days_remaining -= days_in_m;
+ }
+
+ /* Handle overflow (day position exceeds quarter length) */
+ if (!found)
+ {
+ anniversary_m = first_month + 2;
+ anniversary_d = days_in_month_helper(anniversary_y, anniversary_m);
+ }
+ }
+
+ /* Ensure anniversary_d is valid */
+ if (anniversary_d > days_in_month_helper(anniversary_y, anniversary_m))
+ anniversary_d = days_in_month_helper(anniversary_y, anniversary_m);
+
+ anniversary_jd = date2j(anniversary_y, anniversary_m, anniversary_d);
+ end_jd = date2j(end_y, end_m, end_d);
+ remaining_days = end_jd - anniversary_jd;
+
+ /* Partial period is the quarter containing the anniversary */
+ partial_period_days = days_in_quarter(anniversary_y, anniversary_quarter);
+
+ if (partial_period_days <= 0)
+ partial_period_days = 1;
+
+ result = (double) full_quarters + (double) remaining_days / (double) partial_period_days;
+ }
+
+ if (negated)
+ result = -result;
+
+ return make_numeric_result(bankers_round(result));
+}
+
+/*
+ * compute_diff_year - calculate year difference using hybrid model
+ *
+ * Similar to month but with year-based periods.
+ */
+static Datum
+compute_diff_year(int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ bool negated = false;
+ int full_years;
+ int remaining_days;
+ int partial_period_days;
+ double result;
+ bool aligned;
+ int anniversary_y, anniversary_m, anniversary_d;
+ int anniversary_jd, end_jd;
+
+ /* Handle negative spans */
+ if (start_y > end_y ||
+ (start_y == end_y && start_m > end_m) ||
+ (start_y == end_y && start_m == end_m && start_d > end_d))
+ {
+ int tmp_y = start_y, tmp_m = start_m, tmp_d = start_d;
+
+ start_y = end_y;
+ start_m = end_m;
+ start_d = end_d;
+ end_y = tmp_y;
+ end_m = tmp_m;
+ end_d = tmp_d;
+ negated = true;
+ }
+
+ /* Check alignment: same month and day, or Feb 29 -> Feb 28 in non-leap */
+ aligned = (start_m == end_m && start_d == end_d);
+
+ /* Special case: Feb 29 in leap year aligns with Feb 28 in non-leap */
+ if (!aligned && start_m == 2 && start_d == 29 && end_m == 2 && end_d == 28)
+ {
+ if (!isleap(end_y))
+ aligned = true;
+ }
+ if (!aligned && start_m == 2 && start_d == 28 && end_m == 2 && end_d == 29)
+ {
+ if (!isleap(start_y))
+ aligned = true;
+ }
+
+ /* Calculate full years */
+ full_years = end_y - start_y;
+ if (end_m < start_m || (end_m == start_m && end_d < start_d))
+ full_years--;
+
+ if (full_years < 0)
+ full_years = 0;
+
+ if (aligned && full_years > 0)
+ {
+ result = (double) full_years;
+ }
+ else if (aligned && full_years == 0 && end_y > start_y)
+ {
+ /* Exact one year */
+ result = 1.0;
+ }
+ else if (start_y == end_y && start_m == end_m && start_d == end_d)
+ {
+ /* Same date */
+ result = 0.0;
+ }
+ else
+ {
+ /* Non-aligned: calculate fractional part */
+ anniversary_y = start_y + full_years;
+ anniversary_m = start_m;
+
+ /* Handle Feb 29 when anniversary year is not a leap year */
+ if (start_m == 2 && start_d == 29 && !isleap(anniversary_y))
+ anniversary_d = 28;
+ else if (start_d > days_in_month_helper(anniversary_y, anniversary_m))
+ anniversary_d = days_in_month_helper(anniversary_y, anniversary_m);
+ else
+ anniversary_d = start_d;
+
+ anniversary_jd = date2j(anniversary_y, anniversary_m, anniversary_d);
+ end_jd = date2j(end_y, end_m, end_d);
+ remaining_days = end_jd - anniversary_jd;
+
+ /*
+ * Partial period: days from anniversary to next anniversary The
+ * period uses the year that contains the partial span
+ */
+ {
+ int next_anniversary_y = anniversary_y + 1;
+ int next_anniversary_m = anniversary_m;
+ int next_anniversary_d;
+ int next_anniversary_jd;
+
+ if (start_m == 2 && start_d == 29 && !isleap(next_anniversary_y))
+ next_anniversary_d = 28;
+ else if (start_d > days_in_month_helper(next_anniversary_y, next_anniversary_m))
+ next_anniversary_d = days_in_month_helper(next_anniversary_y, next_anniversary_m);
+ else
+ next_anniversary_d = start_d;
+
+ next_anniversary_jd = date2j(next_anniversary_y, next_anniversary_m, next_anniversary_d);
+ partial_period_days = next_anniversary_jd - anniversary_jd;
+ }
+
+ if (partial_period_days <= 0)
+ partial_period_days = 1;
+
+ result = (double) full_years + (double) remaining_days / (double) partial_period_days;
+ }
+
+ if (negated)
+ result = -result;
+
+ return make_numeric_result(bankers_round(result));
+}
+
+/*
+ * datediff_internal - core calculation dispatcher
+ *
+ * Takes year, month, day for both dates and computes the difference
+ * based on the specified datepart.
+ */
+static Datum
+datediff_internal(const char *datepart_str,
+ int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ DatepartType datepart = parse_datepart(datepart_str);
+
+ /* Validate datepart (FR-2) */
+ if (datepart == DATEPART_INVALID)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Invalid datepart: '%s'", datepart_str),
+ errhint("Valid options: year, quarter, month, week, day")));
+ }
+
+ /* Dispatch to appropriate calculator */
+ switch (datepart)
+ {
+ case DATEPART_DAY:
+ return compute_diff_day(start_y, start_m, start_d,
+ end_y, end_m, end_d);
+ case DATEPART_WEEK:
+ return compute_diff_week(start_y, start_m, start_d,
+ end_y, end_m, end_d);
+ case DATEPART_MONTH:
+ return compute_diff_month(start_y, start_m, start_d,
+ end_y, end_m, end_d);
+ case DATEPART_QUARTER:
+ return compute_diff_quarter(start_y, start_m, start_d,
+ end_y, end_m, end_d);
+ case DATEPART_YEAR:
+ return compute_diff_year(start_y, start_m, start_d,
+ end_y, end_m, end_d);
+ default:
+ /* Should not reach here */
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("Unexpected datepart type")));
+ return (Datum) 0; /* Keep compiler happy */
+ }
+}
+
+/*-------------------------------------------------------------------------
+ * Public Entry Points
+ *-------------------------------------------------------------------------
+ */
+
+PG_FUNCTION_INFO_V1(datediff_date);
+
+/*
+ * datediff_date - DATE version of datediff
+ */
+Datum
+datediff_date(PG_FUNCTION_ARGS)
+{
+ text *datepart_text = PG_GETARG_TEXT_PP(0);
+ DateADT start_date = PG_GETARG_DATEADT(1);
+ DateADT end_date = PG_GETARG_DATEADT(2);
+ char *datepart_str;
+ int start_y, start_m, start_d;
+ int end_y, end_m, end_d;
+
+ datepart_str = text_to_cstring(datepart_text);
+
+ /* Convert dates to year/month/day using j2date */
+ j2date(start_date + POSTGRES_EPOCH_JDATE, &start_y, &start_m, &start_d);
+ j2date(end_date + POSTGRES_EPOCH_JDATE, &end_y, &end_m, &end_d);
+
+ return datediff_internal(datepart_str,
+ start_y, start_m, start_d,
+ end_y, end_m, end_d);
+}
+
+PG_FUNCTION_INFO_V1(datediff_timestamp);
+
+/*
+ * datediff_timestamp - TIMESTAMP version of datediff
+ *
+ * Ignores time component, uses only date portion.
+ */
+Datum
+datediff_timestamp(PG_FUNCTION_ARGS)
+{
+ text *datepart_text = PG_GETARG_TEXT_PP(0);
+ Timestamp start_ts = PG_GETARG_TIMESTAMP(1);
+ Timestamp end_ts = PG_GETARG_TIMESTAMP(2);
+ char *datepart_str;
+ struct pg_tm start_tm, end_tm;
+ fsec_t fsec;
+
+ datepart_str = text_to_cstring(datepart_text);
+
+ /* Decompose timestamps to get date components */
+ if (timestamp2tm(start_ts, NULL, &start_tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ if (timestamp2tm(end_ts, NULL, &end_tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ return datediff_internal(datepart_str,
+ start_tm.tm_year, start_tm.tm_mon, start_tm.tm_mday,
+ end_tm.tm_year, end_tm.tm_mon, end_tm.tm_mday);
+}
+
+PG_FUNCTION_INFO_V1(datediff_timestamptz);
+
+/*
+ * datediff_timestamptz - TIMESTAMPTZ version of datediff
+ *
+ * Converts to local time then uses date portion.
+ */
+Datum
+datediff_timestamptz(PG_FUNCTION_ARGS)
+{
+ text *datepart_text = PG_GETARG_TEXT_PP(0);
+ TimestampTz start_tstz = PG_GETARG_TIMESTAMPTZ(1);
+ TimestampTz end_tstz = PG_GETARG_TIMESTAMPTZ(2);
+ char *datepart_str;
+ struct pg_tm start_tm, end_tm;
+ fsec_t fsec;
+ int tz;
+
+ datepart_str = text_to_cstring(datepart_text);
+
+ /* Decompose timestamps with timezone to get date components */
+ if (timestamp2tm(start_tstz, &tz, &start_tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ if (timestamp2tm(end_tstz, &tz, &end_tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ return datediff_internal(datepart_str,
+ start_tm.tm_year, start_tm.tm_mon, start_tm.tm_mday,
+ end_tm.tm_year, end_tm.tm_mon, end_tm.tm_mday);
+}
+
diff --git a/contrib/mssql_compat/mssql_compat.control b/contrib/mssql_compat/mssql_compat.control
new file mode 100644
index 00000000000..cf669a4eaee
--- /dev/null
+++ b/contrib/mssql_compat/mssql_compat.control
@@ -0,0 +1,7 @@
+# mssql_compat extension
+comment = 'SQL Server compatible datediff function'
+default_version = '1.0'
+module_pathname = '$libdir/mssql_compat'
+relocatable = true
+trusted = true
+
diff --git a/contrib/mssql_compat/results/mssql_compat.out b/contrib/mssql_compat/results/mssql_compat.out
new file mode 100644
index 00000000000..3fd45c1ecc6
--- /dev/null
+++ b/contrib/mssql_compat/results/mssql_compat.out
@@ -0,0 +1,519 @@
+--
+-- Test cases for mssql_compat extension
+-- Covers PRD1a unit tests (UT-01 to UT-15) and edge cases (EC-01 to EC-06)
+--
+CREATE EXTENSION mssql_compat;
+--
+-- Basic Day Calculations (UT-01, UT-02)
+--
+SELECT 'UT-01: Day difference basic' AS test;
+ test
+-----------------------------
+ UT-01: Day difference basic
+(1 row)
+
+SELECT datediff('day', '2024-01-01'::date, '2024-01-15'::date);
+ datediff
+----------
+ 14
+(1 row)
+
+SELECT 'UT-02: Day difference negative' AS test;
+ test
+--------------------------------
+ UT-02: Day difference negative
+(1 row)
+
+SELECT datediff('day', '2024-01-15'::date, '2024-01-01'::date);
+ datediff
+----------
+ -14
+(1 row)
+
+--
+-- Week Calculations (UT-03, UT-04)
+--
+SELECT 'UT-03: Week exact' AS test;
+ test
+-------------------
+ UT-03: Week exact
+(1 row)
+
+SELECT datediff('week', '2024-01-01'::date, '2024-01-08'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'UT-04: Week partial' AS test;
+ test
+---------------------
+ UT-04: Week partial
+(1 row)
+
+SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
+ datediff
+----------
+ 1.286
+(1 row)
+
+--
+-- Month Calculations (UT-05, UT-06, UT-07)
+--
+SELECT 'UT-05: Month aligned' AS test;
+ test
+----------------------
+ UT-05: Month aligned
+(1 row)
+
+SELECT datediff('month', '2024-01-15'::date, '2024-02-15'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'UT-06: Month partial' AS test;
+ test
+----------------------
+ UT-06: Month partial
+(1 row)
+
+SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
+ datediff
+----------
+ 1.172
+(1 row)
+
+SELECT 'UT-07: Month end-of-month alignment' AS test;
+ test
+-------------------------------------
+ UT-07: Month end-of-month alignment
+(1 row)
+
+SELECT datediff('month', '2024-01-31'::date, '2024-02-29'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+--
+-- Quarter Calculations (UT-08, UT-09)
+--
+SELECT 'UT-08: Quarter aligned' AS test;
+ test
+------------------------
+ UT-08: Quarter aligned
+(1 row)
+
+SELECT datediff('quarter', '2024-01-01'::date, '2024-04-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'UT-09: Quarter partial' AS test;
+ test
+------------------------
+ UT-09: Quarter partial
+(1 row)
+
+SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
+ datediff
+----------
+ 1.385
+(1 row)
+
+--
+-- Year Calculations (UT-10, UT-11)
+--
+SELECT 'UT-10: Year aligned' AS test;
+ test
+---------------------
+ UT-10: Year aligned
+(1 row)
+
+SELECT datediff('year', '2024-03-15'::date, '2025-03-15'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'UT-11: Year partial leap year' AS test;
+ test
+-------------------------------
+ UT-11: Year partial leap year
+(1 row)
+
+SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
+ datediff
+----------
+ 0.497
+(1 row)
+
+--
+-- NULL Handling (UT-12, UT-13) - STRICT functions return NULL for NULL inputs
+--
+SELECT 'UT-12: NULL start date' AS test;
+ test
+------------------------
+ UT-12: NULL start date
+(1 row)
+
+SELECT datediff('day', NULL::date, '2024-01-15'::date);
+ datediff
+----------
+
+(1 row)
+
+SELECT 'UT-13: NULL end date' AS test;
+ test
+----------------------
+ UT-13: NULL end date
+(1 row)
+
+SELECT datediff('day', '2024-01-01'::date, NULL::date);
+ datediff
+----------
+
+(1 row)
+
+--
+-- Invalid Datepart (UT-14)
+--
+SELECT 'UT-14: Invalid datepart' AS test;
+ test
+-------------------------
+ UT-14: Invalid datepart
+(1 row)
+
+SELECT datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
+ERROR: Invalid datepart: 'hour'
+HINT: Valid options: year, quarter, month, week, day
+--
+-- Case Insensitivity (UT-15)
+--
+SELECT 'UT-15: Case insensitive datepart' AS test;
+ test
+----------------------------------
+ UT-15: Case insensitive datepart
+(1 row)
+
+SELECT datediff('MONTH', '2024-01-01'::date, '2024-02-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('Month', '2024-01-01'::date, '2024-02-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('month', '2024-01-01'::date, '2024-02-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+--
+-- Edge Cases (EC-01 to EC-06)
+--
+SELECT 'EC-01: Same date' AS test;
+ test
+------------------
+ EC-01: Same date
+(1 row)
+
+SELECT datediff('day', '2024-01-01'::date, '2024-01-01'::date);
+ datediff
+----------
+ 0
+(1 row)
+
+SELECT 'EC-02: Leap year February 29' AS test;
+ test
+------------------------------
+ EC-02: Leap year February 29
+(1 row)
+
+SELECT datediff('day', '2024-02-28'::date, '2024-03-01'::date);
+ datediff
+----------
+ 2
+(1 row)
+
+SELECT 'EC-03: Non-leap year February' AS test;
+ test
+-------------------------------
+ EC-03: Non-leap year February
+(1 row)
+
+SELECT datediff('day', '2023-02-28'::date, '2023-03-01'::date);
+ datediff
+----------
+ 1
+(1 row)
+
+SELECT 'EC-04: Year boundary' AS test;
+ test
+----------------------
+ EC-04: Year boundary
+(1 row)
+
+SELECT datediff('year', '2024-12-31'::date, '2025-01-01'::date);
+ datediff
+----------
+ 0.003
+(1 row)
+
+SELECT 'EC-05: Multi-year span' AS test;
+ test
+------------------------
+ EC-05: Multi-year span
+(1 row)
+
+SELECT datediff('year', '2020-01-01'::date, '2025-01-01'::date);
+ datediff
+----------
+ 5.000
+(1 row)
+
+SELECT 'EC-06: Century boundary' AS test;
+ test
+-------------------------
+ EC-06: Century boundary
+(1 row)
+
+SELECT datediff('day', '1999-12-31'::date, '2000-01-01'::date);
+ datediff
+----------
+ 1
+(1 row)
+
+--
+-- Alias Tests (from PRD1b lines 224-230)
+--
+SELECT 'Alias: yy for year' AS test;
+ test
+--------------------
+ Alias: yy for year
+(1 row)
+
+SELECT datediff('yy', '2024-01-01'::date, '2025-01-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'Alias: yyyy for year' AS test;
+ test
+----------------------
+ Alias: yyyy for year
+(1 row)
+
+SELECT datediff('yyyy', '2024-01-01'::date, '2025-01-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'Alias: mm for month' AS test;
+ test
+---------------------
+ Alias: mm for month
+(1 row)
+
+SELECT datediff('mm', '2024-01-15'::date, '2024-02-15'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'Alias: qq for quarter' AS test;
+ test
+-----------------------
+ Alias: qq for quarter
+(1 row)
+
+SELECT datediff('qq', '2024-01-01'::date, '2024-04-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'Alias: wk for week' AS test;
+ test
+--------------------
+ Alias: wk for week
+(1 row)
+
+SELECT datediff('wk', '2024-01-01'::date, '2024-01-08'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT 'Alias: dd for day' AS test;
+ test
+-------------------
+ Alias: dd for day
+(1 row)
+
+SELECT datediff('dd', '2024-01-01'::date, '2024-01-15'::date);
+ datediff
+----------
+ 14
+(1 row)
+
+--
+-- Timestamp Tests
+--
+SELECT 'Timestamp: basic day diff' AS test;
+ test
+---------------------------
+ Timestamp: basic day diff
+(1 row)
+
+SELECT datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp);
+ datediff
+----------
+ 14
+(1 row)
+
+SELECT 'Timestamp: month diff' AS test;
+ test
+-----------------------
+ Timestamp: month diff
+(1 row)
+
+SELECT datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp);
+ datediff
+----------
+ 1.172
+(1 row)
+
+--
+-- Timestamptz Tests
+--
+SELECT 'Timestamptz: basic day diff' AS test;
+ test
+-----------------------------
+ Timestamptz: basic day diff
+(1 row)
+
+SELECT datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz);
+ datediff
+----------
+ 14
+(1 row)
+
+--
+-- Additional Month Calculation Tests
+--
+SELECT 'Month: Jan 25 to Mar 10 (PRD walkthrough example)' AS test;
+ test
+---------------------------------------------------
+ Month: Jan 25 to Mar 10 (PRD walkthrough example)
+(1 row)
+
+SELECT datediff('month', '2024-01-25'::date, '2024-03-10'::date);
+ datediff
+----------
+ 1.483
+(1 row)
+
+SELECT 'Month: subscription proration example' AS test;
+ test
+---------------------------------------
+ Month: subscription proration example
+(1 row)
+
+SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
+ datediff
+----------
+ 1.172
+(1 row)
+
+--
+-- Additional Quarter Calculation Tests
+--
+SELECT 'Quarter: PRD walkthrough example' AS test;
+ test
+----------------------------------
+ Quarter: PRD walkthrough example
+(1 row)
+
+SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
+ datediff
+----------
+ 1.385
+(1 row)
+
+--
+-- Additional Year Calculation Tests
+--
+SELECT 'Year: PRD walkthrough example' AS test;
+ test
+-------------------------------
+ Year: PRD walkthrough example
+(1 row)
+
+SELECT datediff('year', '2024-03-15'::date, '2025-06-20'::date);
+ datediff
+----------
+ 1.266
+(1 row)
+
+SELECT 'Year: exact 5-year tenure' AS test;
+ test
+---------------------------
+ Year: exact 5-year tenure
+(1 row)
+
+SELECT datediff('year', '2020-03-15'::date, '2025-03-15'::date);
+ datediff
+----------
+ 5.000
+(1 row)
+
+SELECT 'Year: leap year partial (182 days / 366)' AS test;
+ test
+------------------------------------------
+ Year: leap year partial (182 days / 366)
+(1 row)
+
+SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
+ datediff
+----------
+ 0.497
+(1 row)
+
+--
+-- Week Calculation Additional Tests
+--
+SELECT 'Week: exact 2 weeks' AS test;
+ test
+---------------------
+ Week: exact 2 weeks
+(1 row)
+
+SELECT datediff('week', '2024-01-01'::date, '2024-01-15'::date);
+ datediff
+----------
+ 2.000
+(1 row)
+
+SELECT 'Week: PRD example 9 days' AS test;
+ test
+--------------------------
+ Week: PRD example 9 days
+(1 row)
+
+SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
+ datediff
+----------
+ 1.286
+(1 row)
+
+DROP EXTENSION mssql_compat;
diff --git a/contrib/mssql_compat/sql/datediff_comprehensive_tests.sql b/contrib/mssql_compat/sql/datediff_comprehensive_tests.sql
new file mode 100644
index 00000000000..a763d958a62
--- /dev/null
+++ b/contrib/mssql_compat/sql/datediff_comprehensive_tests.sql
@@ -0,0 +1,598 @@
+--
+-- Comprehensive DATEDIFF Function Tests
+-- 50+ tests covering all permutations and edge cases
+--
+
+-- Setup: Create extension
+DROP EXTENSION IF EXISTS mssql_compat CASCADE;
+CREATE EXTENSION mssql_compat;
+
+-- ============================================================================
+-- SECTION 1: Test Data Setup
+-- ============================================================================
+
+DROP TABLE IF EXISTS date_test_data;
+CREATE TABLE date_test_data (
+ id SERIAL PRIMARY KEY,
+ description TEXT,
+ start_date DATE,
+ end_date DATE,
+ start_ts TIMESTAMP,
+ end_ts TIMESTAMP,
+ start_tstz TIMESTAMPTZ,
+ end_tstz TIMESTAMPTZ
+);
+
+-- Insert comprehensive test data
+INSERT INTO date_test_data (description, start_date, end_date, start_ts, end_ts, start_tstz, end_tstz) VALUES
+-- Basic date ranges
+('Same day', '2024-06-15', '2024-06-15', '2024-06-15 10:00:00', '2024-06-15 18:00:00', '2024-06-15 10:00:00+00', '2024-06-15 18:00:00+00'),
+('One day apart', '2024-06-15', '2024-06-16', '2024-06-15 00:00:00', '2024-06-16 00:00:00', '2024-06-15 00:00:00+00', '2024-06-16 00:00:00+00'),
+('One week apart', '2024-06-01', '2024-06-08', '2024-06-01 12:00:00', '2024-06-08 12:00:00', '2024-06-01 12:00:00+00', '2024-06-08 12:00:00+00'),
+('One month apart (same day)', '2024-05-15', '2024-06-15', '2024-05-15 08:30:00', '2024-06-15 08:30:00', '2024-05-15 08:30:00+00', '2024-06-15 08:30:00+00'),
+('One quarter apart', '2024-01-01', '2024-04-01', '2024-01-01 00:00:00', '2024-04-01 00:00:00', '2024-01-01 00:00:00+00', '2024-04-01 00:00:00+00'),
+('One year apart', '2023-06-15', '2024-06-15', '2023-06-15 15:45:00', '2024-06-15 15:45:00', '2023-06-15 15:45:00+00', '2024-06-15 15:45:00+00'),
+
+-- Leap year scenarios
+('Leap year Feb 28 to Mar 1', '2024-02-28', '2024-03-01', '2024-02-28 00:00:00', '2024-03-01 00:00:00', '2024-02-28 00:00:00+00', '2024-03-01 00:00:00+00'),
+('Leap year Feb 29 exists', '2024-02-29', '2024-03-01', '2024-02-29 00:00:00', '2024-03-01 00:00:00', '2024-02-29 00:00:00+00', '2024-03-01 00:00:00+00'),
+('Non-leap year Feb 28 to Mar 1', '2023-02-28', '2023-03-01', '2023-02-28 00:00:00', '2023-03-01 00:00:00', '2023-02-28 00:00:00+00', '2023-03-01 00:00:00+00'),
+('Leap year full year', '2024-01-01', '2025-01-01', '2024-01-01 00:00:00', '2025-01-01 00:00:00', '2024-01-01 00:00:00+00', '2025-01-01 00:00:00+00'),
+
+-- End of month scenarios
+('Jan 31 to Feb 28 (non-leap)', '2023-01-31', '2023-02-28', '2023-01-31 00:00:00', '2023-02-28 00:00:00', '2023-01-31 00:00:00+00', '2023-02-28 00:00:00+00'),
+('Jan 31 to Feb 29 (leap)', '2024-01-31', '2024-02-29', '2024-01-31 00:00:00', '2024-02-29 00:00:00', '2024-01-31 00:00:00+00', '2024-02-29 00:00:00+00'),
+('Mar 31 to Apr 30', '2024-03-31', '2024-04-30', '2024-03-31 00:00:00', '2024-04-30 00:00:00', '2024-03-31 00:00:00+00', '2024-04-30 00:00:00+00'),
+('Month end to month end chain', '2024-01-31', '2024-05-31', '2024-01-31 00:00:00', '2024-05-31 00:00:00', '2024-01-31 00:00:00+00', '2024-05-31 00:00:00+00'),
+
+-- Negative spans (start > end)
+('Negative: 2 weeks back', '2024-06-22', '2024-06-08', '2024-06-22 00:00:00', '2024-06-08 00:00:00', '2024-06-22 00:00:00+00', '2024-06-08 00:00:00+00'),
+('Negative: 3 months back', '2024-09-15', '2024-06-15', '2024-09-15 00:00:00', '2024-06-15 00:00:00', '2024-09-15 00:00:00+00', '2024-06-15 00:00:00+00'),
+('Negative: 2 years back', '2026-01-01', '2024-01-01', '2026-01-01 00:00:00', '2024-01-01 00:00:00', '2026-01-01 00:00:00+00', '2024-01-01 00:00:00+00'),
+
+-- Year boundary crossings
+('Cross year boundary', '2024-12-31', '2025-01-01', '2024-12-31 23:59:59', '2025-01-01 00:00:01', '2024-12-31 23:59:59+00', '2025-01-01 00:00:01+00'),
+('Cross multiple years', '2020-06-15', '2024-06-15', '2020-06-15 00:00:00', '2024-06-15 00:00:00', '2020-06-15 00:00:00+00', '2024-06-15 00:00:00+00'),
+('Century boundary', '1999-12-31', '2000-01-01', '1999-12-31 00:00:00', '2000-01-01 00:00:00', '1999-12-31 00:00:00+00', '2000-01-01 00:00:00+00'),
+
+-- Partial periods
+('Partial month mid-month', '2024-01-15', '2024-02-20', '2024-01-15 00:00:00', '2024-02-20 00:00:00', '2024-01-15 00:00:00+00', '2024-02-20 00:00:00+00'),
+('Partial quarter', '2024-01-15', '2024-05-20', '2024-01-15 00:00:00', '2024-05-20 00:00:00', '2024-01-15 00:00:00+00', '2024-05-20 00:00:00+00'),
+('Partial year', '2024-03-15', '2025-06-20', '2024-03-15 00:00:00', '2025-06-20 00:00:00', '2024-03-15 00:00:00+00', '2025-06-20 00:00:00+00'),
+
+-- Employee tenure scenarios
+('Employee 90-day probation', '2024-01-15', '2024-04-14', '2024-01-15 09:00:00', '2024-04-14 17:00:00', '2024-01-15 09:00:00+00', '2024-04-14 17:00:00+00'),
+('Employee 5-year anniversary', '2019-03-01', '2024-03-01', '2019-03-01 00:00:00', '2024-03-01 00:00:00', '2019-03-01 00:00:00+00', '2024-03-01 00:00:00+00'),
+('Employee 10-year tenure', '2014-06-15', '2024-06-15', '2014-06-15 08:00:00', '2024-06-15 08:00:00', '2014-06-15 08:00:00+00', '2024-06-15 08:00:00+00'),
+
+-- Billing/subscription scenarios
+('Monthly subscription', '2024-01-01', '2024-01-31', '2024-01-01 00:00:00', '2024-01-31 23:59:59', '2024-01-01 00:00:00+00', '2024-01-31 23:59:59+00'),
+('Quarterly billing', '2024-01-01', '2024-03-31', '2024-01-01 00:00:00', '2024-03-31 00:00:00', '2024-01-01 00:00:00+00', '2024-03-31 00:00:00+00'),
+('Annual subscription', '2023-07-15', '2024-07-15', '2023-07-15 00:00:00', '2024-07-15 00:00:00', '2023-07-15 00:00:00+00', '2024-07-15 00:00:00+00'),
+('Prorated mid-month cancel', '2024-03-01', '2024-03-18', '2024-03-01 00:00:00', '2024-03-18 00:00:00', '2024-03-01 00:00:00+00', '2024-03-18 00:00:00+00'),
+
+-- Large spans
+('Decade span', '2010-01-01', '2020-01-01', '2010-01-01 00:00:00', '2020-01-01 00:00:00', '2010-01-01 00:00:00+00', '2020-01-01 00:00:00+00'),
+('25 years span', '1999-06-15', '2024-06-15', '1999-06-15 00:00:00', '2024-06-15 00:00:00', '1999-06-15 00:00:00+00', '2024-06-15 00:00:00+00');
+
+-- ============================================================================
+-- SECTION 2: DAY Datepart Tests (Tests 1-8)
+-- ============================================================================
+
+SELECT '=== DAY DATEPART TESTS ===' AS section;
+
+-- Test 1: Basic day difference
+SELECT 'Test 1: Basic day difference' AS test_name,
+ datediff('day', '2024-01-01', '2024-01-15') AS result,
+ 14 AS expected,
+ CASE WHEN datediff('day', '2024-01-01', '2024-01-15') = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 2: Day difference using 'dd' alias
+SELECT 'Test 2: Day alias dd' AS test_name,
+ datediff('dd', '2024-01-01', '2024-01-15') AS result,
+ 14 AS expected,
+ CASE WHEN datediff('dd', '2024-01-01', '2024-01-15') = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 3: Day difference using 'd' alias
+SELECT 'Test 3: Day alias d' AS test_name,
+ datediff('d', '2024-03-01', '2024-03-31') AS result,
+ 30 AS expected,
+ CASE WHEN datediff('d', '2024-03-01', '2024-03-31') = 30 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 4: Day difference using 'days' alias
+SELECT 'Test 4: Day alias days' AS test_name,
+ datediff('days', '2024-06-01', '2024-06-30') AS result,
+ 29 AS expected,
+ CASE WHEN datediff('days', '2024-06-01', '2024-06-30') = 29 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 5: Negative day difference
+SELECT 'Test 5: Negative day difference' AS test_name,
+ datediff('day', '2024-01-15', '2024-01-01') AS result,
+ -14 AS expected,
+ CASE WHEN datediff('day', '2024-01-15', '2024-01-01') = -14 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 6: Same day returns 0
+SELECT 'Test 6: Same day returns 0' AS test_name,
+ datediff('day', '2024-06-15', '2024-06-15') AS result,
+ 0 AS expected,
+ CASE WHEN datediff('day', '2024-06-15', '2024-06-15') = 0 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 7: Leap year February (28th to Mar 1st)
+SELECT 'Test 7: Leap year Feb 28 to Mar 1' AS test_name,
+ datediff('day', '2024-02-28', '2024-03-01') AS result,
+ 2 AS expected,
+ CASE WHEN datediff('day', '2024-02-28', '2024-03-01') = 2 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 8: Non-leap year February
+SELECT 'Test 8: Non-leap year Feb 28 to Mar 1' AS test_name,
+ datediff('day', '2023-02-28', '2023-03-01') AS result,
+ 1 AS expected,
+ CASE WHEN datediff('day', '2023-02-28', '2023-03-01') = 1 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 3: WEEK Datepart Tests (Tests 9-15)
+-- ============================================================================
+
+SELECT '=== WEEK DATEPART TESTS ===' AS section;
+
+-- Test 9: Exact 1 week
+SELECT 'Test 9: Exact 1 week' AS test_name,
+ datediff('week', '2024-01-01', '2024-01-08') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('week', '2024-01-01', '2024-01-08') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 10: Exact 2 weeks
+SELECT 'Test 10: Exact 2 weeks' AS test_name,
+ datediff('week', '2024-01-01', '2024-01-15') AS result,
+ 2.000 AS expected,
+ CASE WHEN datediff('week', '2024-01-01', '2024-01-15') = 2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 11: Partial week (9 days = 1.286 weeks)
+SELECT 'Test 11: Partial week 9 days' AS test_name,
+ datediff('week', '2024-01-01', '2024-01-10') AS result,
+ 1.286 AS expected,
+ CASE WHEN datediff('week', '2024-01-01', '2024-01-10') = 1.286 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 12: Week alias 'wk'
+SELECT 'Test 12: Week alias wk' AS test_name,
+ datediff('wk', '2024-01-01', '2024-01-08') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('wk', '2024-01-01', '2024-01-08') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 13: Week alias 'ww'
+SELECT 'Test 13: Week alias ww' AS test_name,
+ datediff('ww', '2024-01-01', '2024-01-22') AS result,
+ 3.000 AS expected,
+ CASE WHEN datediff('ww', '2024-01-01', '2024-01-22') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 14: Week alias 'weeks'
+SELECT 'Test 14: Week alias weeks' AS test_name,
+ datediff('weeks', '2024-02-01', '2024-02-29') AS result,
+ 4.000 AS expected,
+ CASE WHEN datediff('weeks', '2024-02-01', '2024-02-29') = 4.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 15: Negative weeks
+SELECT 'Test 15: Negative weeks' AS test_name,
+ datediff('week', '2024-01-15', '2024-01-01') AS result,
+ -2.000 AS expected,
+ CASE WHEN datediff('week', '2024-01-15', '2024-01-01') = -2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 4: MONTH Datepart Tests (Tests 16-25)
+-- ============================================================================
+
+SELECT '=== MONTH DATEPART TESTS ===' AS section;
+
+-- Test 16: Aligned month (same day-of-month)
+SELECT 'Test 16: Aligned month same day' AS test_name,
+ datediff('month', '2024-01-15', '2024-02-15') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('month', '2024-01-15', '2024-02-15') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 17: Partial month
+SELECT 'Test 17: Partial month' AS test_name,
+ datediff('month', '2024-01-15', '2024-02-20') AS result,
+ 1.172 AS expected,
+ CASE WHEN datediff('month', '2024-01-15', '2024-02-20') = 1.172 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 18: End-of-month alignment (Jan 31 -> Feb 29)
+SELECT 'Test 18: End-of-month alignment' AS test_name,
+ datediff('month', '2024-01-31', '2024-02-29') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('month', '2024-01-31', '2024-02-29') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 19: Month alias 'mm'
+SELECT 'Test 19: Month alias mm' AS test_name,
+ datediff('mm', '2024-01-01', '2024-02-01') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('mm', '2024-01-01', '2024-02-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 20: Month alias 'm'
+SELECT 'Test 20: Month alias m' AS test_name,
+ datediff('m', '2024-03-15', '2024-06-15') AS result,
+ 3.000 AS expected,
+ CASE WHEN datediff('m', '2024-03-15', '2024-06-15') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 21: Month alias 'months'
+SELECT 'Test 21: Month alias months' AS test_name,
+ datediff('months', '2024-01-01', '2024-07-01') AS result,
+ 6.000 AS expected,
+ CASE WHEN datediff('months', '2024-01-01', '2024-07-01') = 6.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 22: Multiple months with partial
+SELECT 'Test 22: Multiple months partial' AS test_name,
+ datediff('month', '2024-01-25', '2024-03-10') AS result,
+ 1.483 AS expected,
+ CASE WHEN datediff('month', '2024-01-25', '2024-03-10') = 1.483 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 23: Negative months
+SELECT 'Test 23: Negative months' AS test_name,
+ datediff('month', '2024-06-15', '2024-03-15') AS result,
+ -3.000 AS expected,
+ CASE WHEN datediff('month', '2024-06-15', '2024-03-15') = -3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 24: Month spanning year boundary
+SELECT 'Test 24: Month across year boundary' AS test_name,
+ datediff('month', '2024-11-15', '2025-02-15') AS result,
+ 3.000 AS expected,
+ CASE WHEN datediff('month', '2024-11-15', '2025-02-15') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 25: Less than one month
+SELECT 'Test 25: Less than one month' AS test_name,
+ datediff('month', '2024-01-01', '2024-01-15') AS result,
+ 0.452 AS expected, -- 14 days / 31 days in January
+ CASE WHEN datediff('month', '2024-01-01', '2024-01-15') BETWEEN 0.450 AND 0.460 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 5: QUARTER Datepart Tests (Tests 26-33)
+-- ============================================================================
+
+SELECT '=== QUARTER DATEPART TESTS ===' AS section;
+
+-- Test 26: Exact quarter aligned
+SELECT 'Test 26: Exact quarter aligned' AS test_name,
+ datediff('quarter', '2024-01-01', '2024-04-01') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('quarter', '2024-01-01', '2024-04-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 27: Partial quarter
+SELECT 'Test 27: Partial quarter' AS test_name,
+ datediff('quarter', '2024-01-15', '2024-05-20') AS result,
+ 1.385 AS expected,
+ CASE WHEN datediff('quarter', '2024-01-15', '2024-05-20') = 1.385 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 28: Quarter alias 'qq'
+SELECT 'Test 28: Quarter alias qq' AS test_name,
+ datediff('qq', '2024-01-01', '2024-07-01') AS result,
+ 2.000 AS expected,
+ CASE WHEN datediff('qq', '2024-01-01', '2024-07-01') = 2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 29: Quarter alias 'q'
+SELECT 'Test 29: Quarter alias q' AS test_name,
+ datediff('q', '2024-01-01', '2024-10-01') AS result,
+ 3.000 AS expected,
+ CASE WHEN datediff('q', '2024-01-01', '2024-10-01') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 30: Quarter alias 'quarters'
+SELECT 'Test 30: Quarter alias quarters' AS test_name,
+ datediff('quarters', '2024-01-01', '2025-01-01') AS result,
+ 4.000 AS expected,
+ CASE WHEN datediff('quarters', '2024-01-01', '2025-01-01') = 4.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 31: Negative quarters
+SELECT 'Test 31: Negative quarters' AS test_name,
+ datediff('quarter', '2024-10-01', '2024-04-01') AS result,
+ -2.000 AS expected,
+ CASE WHEN datediff('quarter', '2024-10-01', '2024-04-01') = -2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 32: Less than one quarter
+SELECT 'Test 32: Less than one quarter' AS test_name,
+ datediff('quarter', '2024-01-01', '2024-02-15') AS result,
+ 0.495 AS expected, -- ~45 days / 91 days
+ CASE WHEN datediff('quarter', '2024-01-01', '2024-02-15') BETWEEN 0.490 AND 0.500 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 33: Quarter across year boundary
+SELECT 'Test 33: Quarter across year boundary' AS test_name,
+ datediff('quarter', '2024-10-01', '2025-04-01') AS result,
+ 2.000 AS expected,
+ CASE WHEN datediff('quarter', '2024-10-01', '2025-04-01') = 2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 6: YEAR Datepart Tests (Tests 34-42)
+-- ============================================================================
+
+SELECT '=== YEAR DATEPART TESTS ===' AS section;
+
+-- Test 34: Exact year aligned
+SELECT 'Test 34: Exact year aligned' AS test_name,
+ datediff('year', '2024-03-15', '2025-03-15') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('year', '2024-03-15', '2025-03-15') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 35: Partial year in leap year
+SELECT 'Test 35: Partial year leap year' AS test_name,
+ datediff('year', '2024-01-01', '2024-07-01') AS result,
+ 0.497 AS expected, -- 182 days / 366
+ CASE WHEN datediff('year', '2024-01-01', '2024-07-01') BETWEEN 0.495 AND 0.500 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 36: Year alias 'yy'
+SELECT 'Test 36: Year alias yy' AS test_name,
+ datediff('yy', '2020-01-01', '2025-01-01') AS result,
+ 5.000 AS expected,
+ CASE WHEN datediff('yy', '2020-01-01', '2025-01-01') = 5.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 37: Year alias 'yyyy'
+SELECT 'Test 37: Year alias yyyy' AS test_name,
+ datediff('yyyy', '2024-06-15', '2027-06-15') AS result,
+ 3.000 AS expected,
+ CASE WHEN datediff('yyyy', '2024-06-15', '2027-06-15') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 38: Year alias 'y'
+SELECT 'Test 38: Year alias y' AS test_name,
+ datediff('y', '2024-01-01', '2024-12-31') AS result,
+ 0.997 AS expected, -- 365 days / 366
+ CASE WHEN datediff('y', '2024-01-01', '2024-12-31') BETWEEN 0.995 AND 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 39: Year alias 'years'
+SELECT 'Test 39: Year alias years' AS test_name,
+ datediff('years', '2014-06-15', '2024-06-15') AS result,
+ 10.000 AS expected,
+ CASE WHEN datediff('years', '2014-06-15', '2024-06-15') = 10.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 40: Year boundary crossing (1 day)
+SELECT 'Test 40: Year boundary 1 day' AS test_name,
+ datediff('year', '2024-12-31', '2025-01-01') AS result,
+ 0.003 AS expected, -- 1 day / 365
+ CASE WHEN datediff('year', '2024-12-31', '2025-01-01') BETWEEN 0.001 AND 0.005 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 41: Negative years
+SELECT 'Test 41: Negative years' AS test_name,
+ datediff('year', '2025-06-15', '2020-06-15') AS result,
+ -5.000 AS expected,
+ CASE WHEN datediff('year', '2025-06-15', '2020-06-15') = -5.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 42: Feb 29 leap year to Feb 28 non-leap (aligned)
+SELECT 'Test 42: Feb 29 to Feb 28 alignment' AS test_name,
+ datediff('year', '2024-02-29', '2025-02-28') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('year', '2024-02-29', '2025-02-28') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 7: Case Insensitivity Tests (Tests 43-45)
+-- ============================================================================
+
+SELECT '=== CASE INSENSITIVITY TESTS ===' AS section;
+
+-- Test 43: UPPERCASE datepart
+SELECT 'Test 43: UPPERCASE MONTH' AS test_name,
+ datediff('MONTH', '2024-01-01', '2024-02-01') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('MONTH', '2024-01-01', '2024-02-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 44: Mixed case datepart
+SELECT 'Test 44: Mixed case Quarter' AS test_name,
+ datediff('QuArTeR', '2024-01-01', '2024-04-01') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('QuArTeR', '2024-01-01', '2024-04-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 45: Mixed case alias
+SELECT 'Test 45: Mixed case alias YY' AS test_name,
+ datediff('Yy', '2024-01-01', '2025-01-01') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('Yy', '2024-01-01', '2025-01-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 8: TIMESTAMP and TIMESTAMPTZ Tests (Tests 46-48)
+-- ============================================================================
+
+SELECT '=== TIMESTAMP TESTS ===' AS section;
+
+-- Test 46: Timestamp day difference
+SELECT 'Test 46: Timestamp day diff' AS test_name,
+ datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp) AS result,
+ 14 AS expected,
+ CASE WHEN datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp) = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 47: Timestamp month difference
+SELECT 'Test 47: Timestamp month diff' AS test_name,
+ datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp) AS result,
+ 1.172 AS expected,
+ CASE WHEN datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp) = 1.172 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Test 48: Timestamptz day difference
+SELECT 'Test 48: Timestamptz day diff' AS test_name,
+ datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz) AS result,
+ 14 AS expected,
+ CASE WHEN datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz) = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 9: Error Handling Tests (Tests 49-50)
+-- ============================================================================
+
+SELECT '=== ERROR HANDLING TESTS ===' AS section;
+
+-- Test 49: Invalid datepart should error
+SELECT 'Test 49: Invalid datepart error' AS test_name;
+DO $$
+BEGIN
+ PERFORM datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
+ RAISE NOTICE 'FAIL: No error raised for invalid datepart';
+EXCEPTION WHEN invalid_parameter_value THEN
+ RAISE NOTICE 'PASS: Correctly raised error for invalid datepart';
+END $$;
+
+-- Test 50: NULL handling (should return NULL)
+SELECT 'Test 50: NULL handling' AS test_name,
+ datediff('day', NULL::date, '2024-01-15'::date) IS NULL AS null_start_returns_null,
+ datediff('day', '2024-01-01'::date, NULL::date) IS NULL AS null_end_returns_null,
+ CASE
+ WHEN datediff('day', NULL::date, '2024-01-15'::date) IS NULL
+ AND datediff('day', '2024-01-01'::date, NULL::date) IS NULL
+ THEN 'PASS'
+ ELSE 'FAIL'
+ END AS status;
+
+-- ============================================================================
+-- SECTION 10: Table-Based Tests
+-- ============================================================================
+
+SELECT '=== TABLE-BASED TESTS ===' AS section;
+
+-- Test all dateparts against table data
+SELECT
+ description,
+ datediff('day', start_date, end_date) AS days,
+ datediff('week', start_date, end_date) AS weeks,
+ datediff('month', start_date, end_date) AS months,
+ datediff('quarter', start_date, end_date) AS quarters,
+ datediff('year', start_date, end_date) AS years
+FROM date_test_data
+ORDER BY id;
+
+-- Test with timestamp columns
+SELECT
+ description,
+ datediff('day', start_ts, end_ts) AS ts_days,
+ datediff('month', start_ts, end_ts) AS ts_months
+FROM date_test_data
+WHERE start_ts IS NOT NULL
+ORDER BY id
+LIMIT 10;
+
+-- Test with timestamptz columns
+SELECT
+ description,
+ datediff('day', start_tstz, end_tstz) AS tstz_days,
+ datediff('year', start_tstz, end_tstz) AS tstz_years
+FROM date_test_data
+WHERE start_tstz IS NOT NULL
+ORDER BY id
+LIMIT 10;
+
+-- ============================================================================
+-- SECTION 11: Aggregation and Analytics Tests
+-- ============================================================================
+
+SELECT '=== AGGREGATION TESTS ===' AS section;
+
+-- Average tenure calculations
+SELECT
+ 'Average differences across test data' AS metric,
+ ROUND(AVG(datediff('day', start_date, end_date)), 2) AS avg_days,
+ ROUND(AVG(datediff('month', start_date, end_date)), 2) AS avg_months,
+ ROUND(AVG(datediff('year', start_date, end_date)), 2) AS avg_years
+FROM date_test_data
+WHERE start_date <= end_date;
+
+-- Group by ranges
+WITH date_diffs AS (
+ SELECT
+ id,
+ description,
+ datediff('day', start_date, end_date) AS day_diff
+ FROM date_test_data
+ WHERE start_date <= end_date
+)
+SELECT
+ CASE
+ WHEN day_diff < 7 THEN 'Less than 1 week'
+ WHEN day_diff < 30 THEN '1 week to 1 month'
+ WHEN day_diff < 90 THEN '1 to 3 months'
+ WHEN day_diff < 365 THEN '3 months to 1 year'
+ ELSE 'Over 1 year'
+ END AS duration_bucket,
+ COUNT(*) AS count
+FROM date_diffs
+GROUP BY 1
+ORDER BY MIN(day_diff);
+
+-- ============================================================================
+-- SECTION 12: Real-World Scenario Tests
+-- ============================================================================
+
+SELECT '=== REAL-WORLD SCENARIO TESTS ===' AS section;
+
+-- Invoice aging report simulation
+WITH invoices AS (
+ SELECT
+ generate_series(1, 10) AS invoice_id,
+ '2024-01-01'::date + (random() * 180)::int AS due_date
+)
+SELECT
+ invoice_id,
+ due_date,
+ CURRENT_DATE AS today,
+ datediff('day', due_date, CURRENT_DATE) AS days_overdue,
+ CASE
+ WHEN datediff('day', due_date, CURRENT_DATE) > 90 THEN 'Critical'
+ WHEN datediff('day', due_date, CURRENT_DATE) > 60 THEN 'Warning'
+ WHEN datediff('day', due_date, CURRENT_DATE) > 30 THEN 'Attention'
+ WHEN datediff('day', due_date, CURRENT_DATE) > 0 THEN 'Overdue'
+ ELSE 'Current'
+ END AS aging_bucket
+FROM invoices
+ORDER BY days_overdue DESC;
+
+-- Subscription proration simulation
+WITH subscriptions AS (
+ SELECT
+ 1 AS sub_id, '2024-01-15'::date AS start_date, '2024-02-20'::date AS cancel_date, 29.99 AS monthly_rate
+ UNION ALL SELECT
+ 2, '2024-03-01', '2024-03-18', 49.99
+ UNION ALL SELECT
+ 3, '2024-06-15', '2024-09-15', 99.99
+)
+SELECT
+ sub_id,
+ start_date,
+ cancel_date,
+ monthly_rate,
+ datediff('month', start_date, cancel_date) AS months_used,
+ ROUND((datediff('month', start_date, cancel_date) * monthly_rate)::numeric, 2) AS prorated_charge
+FROM subscriptions;
+
+-- Employee tenure report
+WITH employees AS (
+ SELECT 'Alice' AS name, '2019-03-15'::date AS hire_date
+ UNION ALL SELECT 'Bob', '2021-06-01'
+ UNION ALL SELECT 'Carol', '2023-01-10'
+ UNION ALL SELECT 'David', '2024-06-15'
+)
+SELECT
+ name,
+ hire_date,
+ datediff('year', hire_date, CURRENT_DATE) AS years_tenure,
+ datediff('month', hire_date, CURRENT_DATE) AS months_tenure,
+ datediff('day', hire_date, CURRENT_DATE) AS days_tenure,
+ CASE
+ WHEN datediff('year', hire_date, CURRENT_DATE) >= 5 THEN 'Senior'
+ WHEN datediff('year', hire_date, CURRENT_DATE) >= 2 THEN 'Mid-level'
+ WHEN datediff('year', hire_date, CURRENT_DATE) >= 1 THEN 'Junior'
+ ELSE 'Probation'
+ END AS tenure_level
+FROM employees
+ORDER BY hire_date;
+
+-- ============================================================================
+-- SUMMARY: Test Results
+-- ============================================================================
+
+SELECT '=== TEST SUMMARY ===' AS section;
+
+-- Count passing tests from table data
+SELECT
+ 'Table data validation' AS category,
+ COUNT(*) FILTER (WHERE datediff('day', start_date, end_date) IS NOT NULL) AS day_tests,
+ COUNT(*) FILTER (WHERE datediff('week', start_date, end_date) IS NOT NULL) AS week_tests,
+ COUNT(*) FILTER (WHERE datediff('month', start_date, end_date) IS NOT NULL) AS month_tests,
+ COUNT(*) FILTER (WHERE datediff('quarter', start_date, end_date) IS NOT NULL) AS quarter_tests,
+ COUNT(*) FILTER (WHERE datediff('year', start_date, end_date) IS NOT NULL) AS year_tests
+FROM date_test_data;
+
+-- Cleanup
+DROP TABLE IF EXISTS date_test_data;
+-- Note: Keeping extension for further manual testing
+-- DROP EXTENSION IF EXISTS mssql_compat;
+
+SELECT 'All comprehensive tests completed!' AS final_status;
+
diff --git a/contrib/mssql_compat/sql/mssql_compat.sql b/contrib/mssql_compat/sql/mssql_compat.sql
new file mode 100644
index 00000000000..f625868b3f7
--- /dev/null
+++ b/contrib/mssql_compat/sql/mssql_compat.sql
@@ -0,0 +1,173 @@
+--
+-- Test cases for mssql_compat extension
+-- Covers PRD1a unit tests (UT-01 to UT-15) and edge cases (EC-01 to EC-06)
+--
+
+CREATE EXTENSION mssql_compat;
+
+--
+-- Basic Day Calculations (UT-01, UT-02)
+--
+SELECT 'UT-01: Day difference basic' AS test;
+SELECT datediff('day', '2024-01-01'::date, '2024-01-15'::date);
+
+SELECT 'UT-02: Day difference negative' AS test;
+SELECT datediff('day', '2024-01-15'::date, '2024-01-01'::date);
+
+--
+-- Week Calculations (UT-03, UT-04)
+--
+SELECT 'UT-03: Week exact' AS test;
+SELECT datediff('week', '2024-01-01'::date, '2024-01-08'::date);
+
+SELECT 'UT-04: Week partial' AS test;
+SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
+
+--
+-- Month Calculations (UT-05, UT-06, UT-07)
+--
+SELECT 'UT-05: Month aligned' AS test;
+SELECT datediff('month', '2024-01-15'::date, '2024-02-15'::date);
+
+SELECT 'UT-06: Month partial' AS test;
+SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
+
+SELECT 'UT-07: Month end-of-month alignment' AS test;
+SELECT datediff('month', '2024-01-31'::date, '2024-02-29'::date);
+
+--
+-- Quarter Calculations (UT-08, UT-09)
+--
+SELECT 'UT-08: Quarter aligned' AS test;
+SELECT datediff('quarter', '2024-01-01'::date, '2024-04-01'::date);
+
+SELECT 'UT-09: Quarter partial' AS test;
+SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
+
+--
+-- Year Calculations (UT-10, UT-11)
+--
+SELECT 'UT-10: Year aligned' AS test;
+SELECT datediff('year', '2024-03-15'::date, '2025-03-15'::date);
+
+SELECT 'UT-11: Year partial leap year' AS test;
+SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
+
+--
+-- NULL Handling (UT-12, UT-13) - STRICT functions return NULL for NULL inputs
+--
+SELECT 'UT-12: NULL start date' AS test;
+SELECT datediff('day', NULL::date, '2024-01-15'::date);
+
+SELECT 'UT-13: NULL end date' AS test;
+SELECT datediff('day', '2024-01-01'::date, NULL::date);
+
+--
+-- Invalid Datepart (UT-14)
+--
+SELECT 'UT-14: Invalid datepart' AS test;
+SELECT datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
+
+--
+-- Case Insensitivity (UT-15)
+--
+SELECT 'UT-15: Case insensitive datepart' AS test;
+SELECT datediff('MONTH', '2024-01-01'::date, '2024-02-01'::date);
+SELECT datediff('Month', '2024-01-01'::date, '2024-02-01'::date);
+SELECT datediff('month', '2024-01-01'::date, '2024-02-01'::date);
+
+--
+-- Edge Cases (EC-01 to EC-06)
+--
+SELECT 'EC-01: Same date' AS test;
+SELECT datediff('day', '2024-01-01'::date, '2024-01-01'::date);
+
+SELECT 'EC-02: Leap year February 29' AS test;
+SELECT datediff('day', '2024-02-28'::date, '2024-03-01'::date);
+
+SELECT 'EC-03: Non-leap year February' AS test;
+SELECT datediff('day', '2023-02-28'::date, '2023-03-01'::date);
+
+SELECT 'EC-04: Year boundary' AS test;
+SELECT datediff('year', '2024-12-31'::date, '2025-01-01'::date);
+
+SELECT 'EC-05: Multi-year span' AS test;
+SELECT datediff('year', '2020-01-01'::date, '2025-01-01'::date);
+
+SELECT 'EC-06: Century boundary' AS test;
+SELECT datediff('day', '1999-12-31'::date, '2000-01-01'::date);
+
+--
+-- Alias Tests (from PRD1b lines 224-230)
+--
+SELECT 'Alias: yy for year' AS test;
+SELECT datediff('yy', '2024-01-01'::date, '2025-01-01'::date);
+
+SELECT 'Alias: yyyy for year' AS test;
+SELECT datediff('yyyy', '2024-01-01'::date, '2025-01-01'::date);
+
+SELECT 'Alias: mm for month' AS test;
+SELECT datediff('mm', '2024-01-15'::date, '2024-02-15'::date);
+
+SELECT 'Alias: qq for quarter' AS test;
+SELECT datediff('qq', '2024-01-01'::date, '2024-04-01'::date);
+
+SELECT 'Alias: wk for week' AS test;
+SELECT datediff('wk', '2024-01-01'::date, '2024-01-08'::date);
+
+SELECT 'Alias: dd for day' AS test;
+SELECT datediff('dd', '2024-01-01'::date, '2024-01-15'::date);
+
+--
+-- Timestamp Tests
+--
+SELECT 'Timestamp: basic day diff' AS test;
+SELECT datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp);
+
+SELECT 'Timestamp: month diff' AS test;
+SELECT datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp);
+
+--
+-- Timestamptz Tests
+--
+SELECT 'Timestamptz: basic day diff' AS test;
+SELECT datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz);
+
+--
+-- Additional Month Calculation Tests
+--
+SELECT 'Month: Jan 25 to Mar 10 (PRD walkthrough example)' AS test;
+SELECT datediff('month', '2024-01-25'::date, '2024-03-10'::date);
+
+SELECT 'Month: subscription proration example' AS test;
+SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
+
+--
+-- Additional Quarter Calculation Tests
+--
+SELECT 'Quarter: PRD walkthrough example' AS test;
+SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
+
+--
+-- Additional Year Calculation Tests
+--
+SELECT 'Year: PRD walkthrough example' AS test;
+SELECT datediff('year', '2024-03-15'::date, '2025-06-20'::date);
+
+SELECT 'Year: exact 5-year tenure' AS test;
+SELECT datediff('year', '2020-03-15'::date, '2025-03-15'::date);
+
+SELECT 'Year: leap year partial (182 days / 366)' AS test;
+SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
+
+--
+-- Week Calculation Additional Tests
+--
+SELECT 'Week: exact 2 weeks' AS test;
+SELECT datediff('week', '2024-01-01'::date, '2024-01-15'::date);
+
+SELECT 'Week: PRD example 9 days' AS test;
+SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
+
+DROP EXTENSION mssql_compat;
+
--
2.52.0
Import Notes
Reference msg id not found: B7AA1D86-75F6-4FB1-BB82-EFABA92AED9C.ref@sbcglobal.net
Re: [PATCH] Add mssql_compat extension with DATEDIFF function
On 25.11.25 04:25, Myles Lewis wrote:
I'd like to propose a new contrib extension: mssql_compat, which provides
SQL Server compatible date functions starting with DATEDIFF.
I think this could best live as an external project.
orafce is a similar project but for Oracle. There might also be others
for other products.
Re: [PATCH] Add mssql_compat extension with DATEDIFF function
On Tue, Nov 25, 2025 at 09:15:37PM +0100, Peter Eisentraut wrote:
I think this could best live as an external project.
Likely so. Looking at the patch, everything written in it does not
depend directly on something external, with all the function internals
being written based on Postgres APIs. Now, including this
compatibility layer even as a contrib module would have a cost: why
would it be a good idea to bear the cost of such a module in core,
where we would need to maintain compatibility depending on what mssql
decides in its own product? Perhaps this is unlikely, but this
possibility means an extra maintenance burden here.
By the way, when proposing patches, I'd recommend to include
some documentation in them. Proposals in work-in-progress form as OK
as well, of course, if your goal is to take the temperature. I'm on
the same side as Peter here: this proposal would have a better life if
maintained externally.
--
Michael
Re: [PATCH] Add mssql_compat extension with DATEDIFF function
Hi
út 25. 11. 2025 v 21:15 odesílatel Peter Eisentraut <peter@eisentraut.org>
napsal:
On 25.11.25 04:25, Myles Lewis wrote:
I'd like to propose a new contrib extension: mssql_compat, which provides
SQL Server compatible date functions starting with DATEDIFF.I think this could best live as an external project.
orafce is a similar project but for Oracle. There might also be others
for other products.
For projects like this it is better to be an external project - it doesn't
depend on Postgres release cycles - so development can be faster. Really,
significantly faster.
And there is bigger space for some experiments and improvements - and for
future changes.
Regards
Pavel
Re: [PATCH] contrib: Add pg_datemath extension with datediff function
Thank you Peter, Michael, and Pavel for the thoughtful feedback on my initial submission.
After considering your points, I've reframed this patch:
Key Changes:
Renamed from mssql_compat to pg_datemath - Removes any implication of tracking SQL Server compatibility, which was never the intent. This is a standalone utility for fractional date difference calculations.
Clearly differentiated semantics - The datediff() function in this extension returns NUMERIC with fractional precision (e.g., 1.5 months), using a hybrid calculation model: full calendar units plus contextual fractions. This is fundamentally different from MSSQL's integer boundary-crossing semantics.
Test naming cleaned up - Removed numeric enumeration from test cases per Pavel's feedback.
Why contrib rather than external:
The calculation model is self-contained with no external dependencies
Single function with clear, stable semantics (day, week, month, quarter, year)
Fills a practical gap for proration/tenure calculations without requiring complex EXTRACT + AGE compositions
No ongoing compatibility burden with external systems
Use cases this addresses:
Subscription billing proration (e.g., "1.172 months" for partial billing)
Employee tenure calculations with fractional years
Contract duration analysis
Invoice aging reports
The function supports aliases (yy, mm, dd, etc.) for convenience but maintains PostgreSQL-native semantics throughout.
Patch attached. Happy to iterate further on naming, positioning, or scope.
Thanks!
Myles
Show quoted text
On Nov 25, 2025, at 10:29 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi
út 25. 11. 2025 v 21:15 odesílatel Peter Eisentraut <peter@eisentraut.org <mailto:peter@eisentraut.org>> napsal:
On 25.11.25 04:25, Myles Lewis wrote:
I'd like to propose a new contrib extension: mssql_compat, which provides
SQL Server compatible date functions starting with DATEDIFF.I think this could best live as an external project.
orafce is a similar project but for Oracle. There might also be others
for other products.For projects like this it is better to be an external project - it doesn't depend on Postgres release cycles - so development can be faster. Really, significantly faster.
And there is bigger space for some experiments and improvements - and for future changes.Regards
Pavel
Attachments:
0001-feat-pg_datemath-Add-datediff-extension-with-fractio.patchapplication/octet-stream; name=0001-feat-pg_datemath-Add-datediff-extension-with-fractio.patch; x-unix-mode=0644Download
From 390322462f9094ae9dfaf8c99a7cb259a0e22f95 Mon Sep 17 00:00:00 2001
From: mlx93 <mylesethan93@gmail.com>
Date: Wed, 26 Nov 2025 14:19:16 -0600
Subject: [PATCH] feat(pg_datemath): Add datediff extension with fractional
date calculations
Implements datediff(datepart, start_date, end_date) function:
- Supports day, week, month, quarter, year dateparts with aliases
- Hybrid calculation model: full calendar units + contextual fractions
- Returns NUMERIC with 3 decimal precision (banker's rounding)
- Handles DATE, TIMESTAMP, and TIMESTAMPTZ input types
- Calendar alignment detection for whole-number returns
- Negative span handling (start > end returns negative)
- Case-insensitive datepart parsing
- IMMUTABLE STRICT PARALLEL SAFE function attributes
This extension fills a gap in PostgreSQL's date manipulation functions,
providing precise fractional date differences useful for:
- Subscription proration calculations
- Employee tenure tracking
- Contract duration analysis
- Invoice aging reports
All regression tests pass.
---
contrib/meson.build | 2 +-
contrib/mssql_compat/Makefile | 21 --
contrib/mssql_compat/meson.build | 36 ---
contrib/mssql_compat/mssql_compat.control | 7 -
contrib/pg_datemath/Makefile | 21 ++
.../expected/pg_datemath.out} | 228 +++++++++---------
contrib/pg_datemath/meson.build | 36 +++
.../pg_datemath--1.0.sql} | 15 +-
.../pg_datemath.c} | 26 +-
contrib/pg_datemath/pg_datemath.control | 7 +
.../results/pg_datemath.out} | 228 +++++++++---------
.../sql/datediff_advanced_edge_cases.sql | 4 +-
.../sql/datediff_comprehensive_tests.sql | 206 ++++++++--------
.../sql/pg_datemath.sql} | 78 +++---
14 files changed, 458 insertions(+), 457 deletions(-)
delete mode 100644 contrib/mssql_compat/Makefile
delete mode 100644 contrib/mssql_compat/meson.build
delete mode 100644 contrib/mssql_compat/mssql_compat.control
create mode 100644 contrib/pg_datemath/Makefile
rename contrib/{mssql_compat/results/mssql_compat.out => pg_datemath/expected/pg_datemath.out} (68%)
create mode 100644 contrib/pg_datemath/meson.build
rename contrib/{mssql_compat/mssql_compat--1.0.sql => pg_datemath/pg_datemath--1.0.sql} (70%)
rename contrib/{mssql_compat/mssql_compat.c => pg_datemath/pg_datemath.c} (96%)
create mode 100644 contrib/pg_datemath/pg_datemath.control
rename contrib/{mssql_compat/expected/mssql_compat.out => pg_datemath/results/pg_datemath.out} (68%)
rename contrib/{mssql_compat => pg_datemath}/sql/datediff_advanced_edge_cases.sql (99%)
rename contrib/{mssql_compat => pg_datemath}/sql/datediff_comprehensive_tests.sql (84%)
rename contrib/{mssql_compat/sql/mssql_compat.sql => pg_datemath/sql/pg_datemath.sql} (67%)
diff --git a/contrib/meson.build b/contrib/meson.build
index 6a48df8daf8..f5b1bbcff9b 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -40,7 +40,7 @@ subdir('jsonb_plpython')
subdir('lo')
subdir('ltree')
subdir('ltree_plpython')
-subdir('mssql_compat')
+subdir('pg_datemath')
subdir('oid2name')
subdir('pageinspect')
subdir('passwordcheck')
diff --git a/contrib/mssql_compat/Makefile b/contrib/mssql_compat/Makefile
deleted file mode 100644
index 7dbb3409703..00000000000
--- a/contrib/mssql_compat/Makefile
+++ /dev/null
@@ -1,21 +0,0 @@
-# contrib/mssql_compat/Makefile
-
-MODULES = mssql_compat
-
-EXTENSION = mssql_compat
-DATA = mssql_compat--1.0.sql
-PGFILEDESC = "mssql_compat - SQL Server compatible date functions"
-
-REGRESS = mssql_compat
-
-ifdef USE_PGXS
-PG_CONFIG = pg_config
-PGXS := $(shell $(PG_CONFIG) --pgxs)
-include $(PGXS)
-else
-subdir = contrib/mssql_compat
-top_builddir = ../..
-include $(top_builddir)/src/Makefile.global
-include $(top_srcdir)/contrib/contrib-global.mk
-endif
-
diff --git a/contrib/mssql_compat/meson.build b/contrib/mssql_compat/meson.build
deleted file mode 100644
index d1a412ed879..00000000000
--- a/contrib/mssql_compat/meson.build
+++ /dev/null
@@ -1,36 +0,0 @@
-# Copyright (c) 2022-2025, PostgreSQL Global Development Group
-
-mssql_compat_sources = files(
- 'mssql_compat.c',
-)
-
-if host_system == 'windows'
- mssql_compat_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
- '--NAME', 'mssql_compat',
- '--FILEDESC', 'mssql_compat - SQL Server compatible date functions',])
-endif
-
-mssql_compat = shared_module('mssql_compat',
- mssql_compat_sources,
- kwargs: contrib_mod_args,
-)
-
-contrib_targets += mssql_compat
-
-install_data(
- 'mssql_compat--1.0.sql',
- 'mssql_compat.control',
- kwargs: contrib_data_args,
-)
-
-tests += {
- 'name': 'mssql_compat',
- 'sd': meson.current_source_dir(),
- 'bd': meson.current_build_dir(),
- 'regress': {
- 'sql': [
- 'mssql_compat',
- ],
- },
-}
-
diff --git a/contrib/mssql_compat/mssql_compat.control b/contrib/mssql_compat/mssql_compat.control
deleted file mode 100644
index cf669a4eaee..00000000000
--- a/contrib/mssql_compat/mssql_compat.control
+++ /dev/null
@@ -1,7 +0,0 @@
-# mssql_compat extension
-comment = 'SQL Server compatible datediff function'
-default_version = '1.0'
-module_pathname = '$libdir/mssql_compat'
-relocatable = true
-trusted = true
-
diff --git a/contrib/pg_datemath/Makefile b/contrib/pg_datemath/Makefile
new file mode 100644
index 00000000000..e981cdcfe91
--- /dev/null
+++ b/contrib/pg_datemath/Makefile
@@ -0,0 +1,21 @@
+# contrib/pg_datemath/Makefile
+
+MODULES = pg_datemath
+
+EXTENSION = pg_datemath
+DATA = pg_datemath--1.0.sql
+PGFILEDESC = "pg_datemath - Enhanced date difference functions"
+
+REGRESS = pg_datemath
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_datemath
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
diff --git a/contrib/mssql_compat/results/mssql_compat.out b/contrib/pg_datemath/expected/pg_datemath.out
similarity index 68%
rename from contrib/mssql_compat/results/mssql_compat.out
rename to contrib/pg_datemath/expected/pg_datemath.out
index 3fd45c1ecc6..c2829f16d3c 100644
--- a/contrib/mssql_compat/results/mssql_compat.out
+++ b/contrib/pg_datemath/expected/pg_datemath.out
@@ -1,15 +1,15 @@
--
--- Test cases for mssql_compat extension
--- Covers PRD1a unit tests (UT-01 to UT-15) and edge cases (EC-01 to EC-06)
+-- Test cases for pg_datemath extension
+-- Tests datediff function with various dateparts and edge cases
--
-CREATE EXTENSION mssql_compat;
+CREATE EXTENSION pg_datemath;
--
--- Basic Day Calculations (UT-01, UT-02)
+-- Basic Day Calculations
--
-SELECT 'UT-01: Day difference basic' AS test;
- test
------------------------------
- UT-01: Day difference basic
+SELECT 'Day difference basic' AS test;
+ test
+----------------------
+ Day difference basic
(1 row)
SELECT datediff('day', '2024-01-01'::date, '2024-01-15'::date);
@@ -18,10 +18,10 @@ SELECT datediff('day', '2024-01-01'::date, '2024-01-15'::date);
14
(1 row)
-SELECT 'UT-02: Day difference negative' AS test;
- test
---------------------------------
- UT-02: Day difference negative
+SELECT 'Day difference negative' AS test;
+ test
+-------------------------
+ Day difference negative
(1 row)
SELECT datediff('day', '2024-01-15'::date, '2024-01-01'::date);
@@ -31,12 +31,12 @@ SELECT datediff('day', '2024-01-15'::date, '2024-01-01'::date);
(1 row)
--
--- Week Calculations (UT-03, UT-04)
+-- Week Calculations
--
-SELECT 'UT-03: Week exact' AS test;
- test
--------------------
- UT-03: Week exact
+SELECT 'Week exact' AS test;
+ test
+------------
+ Week exact
(1 row)
SELECT datediff('week', '2024-01-01'::date, '2024-01-08'::date);
@@ -45,10 +45,10 @@ SELECT datediff('week', '2024-01-01'::date, '2024-01-08'::date);
1.000
(1 row)
-SELECT 'UT-04: Week partial' AS test;
- test
----------------------
- UT-04: Week partial
+SELECT 'Week partial' AS test;
+ test
+--------------
+ Week partial
(1 row)
SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
@@ -58,12 +58,12 @@ SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
(1 row)
--
--- Month Calculations (UT-05, UT-06, UT-07)
+-- Month Calculations
--
-SELECT 'UT-05: Month aligned' AS test;
- test
-----------------------
- UT-05: Month aligned
+SELECT 'Month aligned' AS test;
+ test
+---------------
+ Month aligned
(1 row)
SELECT datediff('month', '2024-01-15'::date, '2024-02-15'::date);
@@ -72,10 +72,10 @@ SELECT datediff('month', '2024-01-15'::date, '2024-02-15'::date);
1.000
(1 row)
-SELECT 'UT-06: Month partial' AS test;
- test
-----------------------
- UT-06: Month partial
+SELECT 'Month partial' AS test;
+ test
+---------------
+ Month partial
(1 row)
SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
@@ -84,10 +84,10 @@ SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
1.172
(1 row)
-SELECT 'UT-07: Month end-of-month alignment' AS test;
- test
--------------------------------------
- UT-07: Month end-of-month alignment
+SELECT 'Month end-of-month alignment' AS test;
+ test
+------------------------------
+ Month end-of-month alignment
(1 row)
SELECT datediff('month', '2024-01-31'::date, '2024-02-29'::date);
@@ -97,12 +97,12 @@ SELECT datediff('month', '2024-01-31'::date, '2024-02-29'::date);
(1 row)
--
--- Quarter Calculations (UT-08, UT-09)
+-- Quarter Calculations
--
-SELECT 'UT-08: Quarter aligned' AS test;
- test
-------------------------
- UT-08: Quarter aligned
+SELECT 'Quarter aligned' AS test;
+ test
+-----------------
+ Quarter aligned
(1 row)
SELECT datediff('quarter', '2024-01-01'::date, '2024-04-01'::date);
@@ -111,10 +111,10 @@ SELECT datediff('quarter', '2024-01-01'::date, '2024-04-01'::date);
1.000
(1 row)
-SELECT 'UT-09: Quarter partial' AS test;
- test
-------------------------
- UT-09: Quarter partial
+SELECT 'Quarter partial' AS test;
+ test
+-----------------
+ Quarter partial
(1 row)
SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
@@ -124,12 +124,12 @@ SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
(1 row)
--
--- Year Calculations (UT-10, UT-11)
+-- Year Calculations
--
-SELECT 'UT-10: Year aligned' AS test;
- test
----------------------
- UT-10: Year aligned
+SELECT 'Year aligned' AS test;
+ test
+--------------
+ Year aligned
(1 row)
SELECT datediff('year', '2024-03-15'::date, '2025-03-15'::date);
@@ -138,10 +138,10 @@ SELECT datediff('year', '2024-03-15'::date, '2025-03-15'::date);
1.000
(1 row)
-SELECT 'UT-11: Year partial leap year' AS test;
- test
--------------------------------
- UT-11: Year partial leap year
+SELECT 'Year partial leap year' AS test;
+ test
+------------------------
+ Year partial leap year
(1 row)
SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
@@ -151,12 +151,12 @@ SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
(1 row)
--
--- NULL Handling (UT-12, UT-13) - STRICT functions return NULL for NULL inputs
+-- NULL Handling - STRICT functions return NULL for NULL inputs
--
-SELECT 'UT-12: NULL start date' AS test;
- test
-------------------------
- UT-12: NULL start date
+SELECT 'NULL start date' AS test;
+ test
+-----------------
+ NULL start date
(1 row)
SELECT datediff('day', NULL::date, '2024-01-15'::date);
@@ -165,10 +165,10 @@ SELECT datediff('day', NULL::date, '2024-01-15'::date);
(1 row)
-SELECT 'UT-13: NULL end date' AS test;
- test
-----------------------
- UT-13: NULL end date
+SELECT 'NULL end date' AS test;
+ test
+---------------
+ NULL end date
(1 row)
SELECT datediff('day', '2024-01-01'::date, NULL::date);
@@ -178,24 +178,24 @@ SELECT datediff('day', '2024-01-01'::date, NULL::date);
(1 row)
--
--- Invalid Datepart (UT-14)
+-- Invalid Datepart
--
-SELECT 'UT-14: Invalid datepart' AS test;
- test
--------------------------
- UT-14: Invalid datepart
+SELECT 'Invalid datepart' AS test;
+ test
+------------------
+ Invalid datepart
(1 row)
SELECT datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
ERROR: Invalid datepart: 'hour'
HINT: Valid options: year, quarter, month, week, day
--
--- Case Insensitivity (UT-15)
+-- Case Insensitivity
--
-SELECT 'UT-15: Case insensitive datepart' AS test;
- test
-----------------------------------
- UT-15: Case insensitive datepart
+SELECT 'Case insensitive datepart' AS test;
+ test
+---------------------------
+ Case insensitive datepart
(1 row)
SELECT datediff('MONTH', '2024-01-01'::date, '2024-02-01'::date);
@@ -217,12 +217,12 @@ SELECT datediff('month', '2024-01-01'::date, '2024-02-01'::date);
(1 row)
--
--- Edge Cases (EC-01 to EC-06)
+-- Edge Cases
--
-SELECT 'EC-01: Same date' AS test;
- test
-------------------
- EC-01: Same date
+SELECT 'Same date' AS test;
+ test
+-----------
+ Same date
(1 row)
SELECT datediff('day', '2024-01-01'::date, '2024-01-01'::date);
@@ -231,10 +231,10 @@ SELECT datediff('day', '2024-01-01'::date, '2024-01-01'::date);
0
(1 row)
-SELECT 'EC-02: Leap year February 29' AS test;
- test
-------------------------------
- EC-02: Leap year February 29
+SELECT 'Leap year February 29' AS test;
+ test
+-----------------------
+ Leap year February 29
(1 row)
SELECT datediff('day', '2024-02-28'::date, '2024-03-01'::date);
@@ -243,10 +243,10 @@ SELECT datediff('day', '2024-02-28'::date, '2024-03-01'::date);
2
(1 row)
-SELECT 'EC-03: Non-leap year February' AS test;
- test
--------------------------------
- EC-03: Non-leap year February
+SELECT 'Non-leap year February' AS test;
+ test
+------------------------
+ Non-leap year February
(1 row)
SELECT datediff('day', '2023-02-28'::date, '2023-03-01'::date);
@@ -255,10 +255,10 @@ SELECT datediff('day', '2023-02-28'::date, '2023-03-01'::date);
1
(1 row)
-SELECT 'EC-04: Year boundary' AS test;
- test
-----------------------
- EC-04: Year boundary
+SELECT 'Year boundary' AS test;
+ test
+---------------
+ Year boundary
(1 row)
SELECT datediff('year', '2024-12-31'::date, '2025-01-01'::date);
@@ -267,10 +267,10 @@ SELECT datediff('year', '2024-12-31'::date, '2025-01-01'::date);
0.003
(1 row)
-SELECT 'EC-05: Multi-year span' AS test;
- test
-------------------------
- EC-05: Multi-year span
+SELECT 'Multi-year span' AS test;
+ test
+-----------------
+ Multi-year span
(1 row)
SELECT datediff('year', '2020-01-01'::date, '2025-01-01'::date);
@@ -279,10 +279,10 @@ SELECT datediff('year', '2020-01-01'::date, '2025-01-01'::date);
5.000
(1 row)
-SELECT 'EC-06: Century boundary' AS test;
- test
--------------------------
- EC-06: Century boundary
+SELECT 'Century boundary' AS test;
+ test
+------------------
+ Century boundary
(1 row)
SELECT datediff('day', '1999-12-31'::date, '2000-01-01'::date);
@@ -292,7 +292,7 @@ SELECT datediff('day', '1999-12-31'::date, '2000-01-01'::date);
(1 row)
--
--- Alias Tests (from PRD1b lines 224-230)
+-- Alias Tests
--
SELECT 'Alias: yy for year' AS test;
test
@@ -411,10 +411,10 @@ SELECT datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:
--
-- Additional Month Calculation Tests
--
-SELECT 'Month: Jan 25 to Mar 10 (PRD walkthrough example)' AS test;
- test
----------------------------------------------------
- Month: Jan 25 to Mar 10 (PRD walkthrough example)
+SELECT 'Month: Jan 25 to Mar 10' AS test;
+ test
+-------------------------
+ Month: Jan 25 to Mar 10
(1 row)
SELECT datediff('month', '2024-01-25'::date, '2024-03-10'::date);
@@ -438,10 +438,10 @@ SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
--
-- Additional Quarter Calculation Tests
--
-SELECT 'Quarter: PRD walkthrough example' AS test;
- test
-----------------------------------
- Quarter: PRD walkthrough example
+SELECT 'Quarter: Q1 to Q2 partial' AS test;
+ test
+---------------------------
+ Quarter: Q1 to Q2 partial
(1 row)
SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
@@ -453,10 +453,10 @@ SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
--
-- Additional Year Calculation Tests
--
-SELECT 'Year: PRD walkthrough example' AS test;
- test
--------------------------------
- Year: PRD walkthrough example
+SELECT 'Year: partial year calculation' AS test;
+ test
+--------------------------------
+ Year: partial year calculation
(1 row)
SELECT datediff('year', '2024-03-15'::date, '2025-06-20'::date);
@@ -504,10 +504,10 @@ SELECT datediff('week', '2024-01-01'::date, '2024-01-15'::date);
2.000
(1 row)
-SELECT 'Week: PRD example 9 days' AS test;
- test
---------------------------
- Week: PRD example 9 days
+SELECT 'Week: 9 days example' AS test;
+ test
+----------------------
+ Week: 9 days example
(1 row)
SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
@@ -516,4 +516,4 @@ SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
1.286
(1 row)
-DROP EXTENSION mssql_compat;
+DROP EXTENSION pg_datemath;
diff --git a/contrib/pg_datemath/meson.build b/contrib/pg_datemath/meson.build
new file mode 100644
index 00000000000..4378a0f574e
--- /dev/null
+++ b/contrib/pg_datemath/meson.build
@@ -0,0 +1,36 @@
+# Copyright (c) 2022-2025, PostgreSQL Global Development Group
+
+pg_datemath_sources = files(
+ 'pg_datemath.c',
+)
+
+if host_system == 'windows'
+ pg_datemath_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'pg_datemath',
+ '--FILEDESC', 'pg_datemath - Enhanced date difference functions',])
+endif
+
+pg_datemath = shared_module('pg_datemath',
+ pg_datemath_sources,
+ kwargs: contrib_mod_args,
+)
+
+contrib_targets += pg_datemath
+
+install_data(
+ 'pg_datemath--1.0.sql',
+ 'pg_datemath.control',
+ kwargs: contrib_data_args,
+)
+
+tests += {
+ 'name': 'pg_datemath',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'regress': {
+ 'sql': [
+ 'pg_datemath',
+ ],
+ },
+}
+
diff --git a/contrib/mssql_compat/mssql_compat--1.0.sql b/contrib/pg_datemath/pg_datemath--1.0.sql
similarity index 70%
rename from contrib/mssql_compat/mssql_compat--1.0.sql
rename to contrib/pg_datemath/pg_datemath--1.0.sql
index 8b950cb4cc4..b141c86d79c 100644
--- a/contrib/mssql_compat/mssql_compat--1.0.sql
+++ b/contrib/pg_datemath/pg_datemath--1.0.sql
@@ -1,17 +1,18 @@
-/* contrib/mssql_compat/mssql_compat--1.0.sql */
+/* contrib/pg_datemath/pg_datemath--1.0.sql */
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION mssql_compat" to load this file. \quit
+\echo Use "CREATE EXTENSION pg_datemath" to load this file. \quit
--
--- datediff(datepart, start_date, end_date) - SQL Server compatible date difference
+-- datediff(datepart, start_date, end_date) - Enhanced date difference calculation
--
-- Returns the difference between two dates in the specified datepart unit.
--- Supports: year, quarter, month, week, day (and aliases)
+-- Supports: year, quarter, month, week, day (and common aliases)
--
--- Unlike SQL Server's boundary-crossing semantics, this implementation provides
--- mathematically accurate results using a hybrid calculation model: full calendar
--- units plus contextual fractions based on actual period lengths.
+-- This implementation provides mathematically accurate results using a hybrid
+-- calculation model: full calendar units plus contextual fractions based on
+-- actual period lengths. This is useful for proration, tenure calculation,
+-- and other scenarios requiring precise fractional date differences.
--
-- Date version
diff --git a/contrib/mssql_compat/mssql_compat.c b/contrib/pg_datemath/pg_datemath.c
similarity index 96%
rename from contrib/mssql_compat/mssql_compat.c
rename to contrib/pg_datemath/pg_datemath.c
index 1db22f37982..fd8c49bfec7 100644
--- a/contrib/mssql_compat/mssql_compat.c
+++ b/contrib/pg_datemath/pg_datemath.c
@@ -1,7 +1,7 @@
/*-------------------------------------------------------------------------
*
- * mssql_compat.c
- * SQL Server compatible datediff function for PostgreSQL.
+ * pg_datemath.c
+ * Enhanced date difference functions for PostgreSQL.
*
* This extension provides datediff(datepart, start_date, end_date) which
* calculates the difference between two dates using a hybrid calculation
@@ -10,7 +10,7 @@
*
* Copyright (c) 2024, PostgreSQL Global Development Group
*
- * contrib/mssql_compat/mssql_compat.c
+ * contrib/pg_datemath/pg_datemath.c
*
*-------------------------------------------------------------------------
*/
@@ -28,7 +28,7 @@
#include "utils/timestamp.h"
PG_MODULE_MAGIC_EXT(
- .name = "mssql_compat",
+ .name = "pg_datemath",
.version = PG_VERSION
);
@@ -62,7 +62,7 @@ parse_datepart(const char *datepart_str)
lower[i] = tolower((unsigned char) datepart_str[i]);
lower[i] = '\0';
- /* Match canonical names and aliases per PRD1b lines 224-230 */
+ /* Match canonical names and aliases */
if (strcmp(lower, "year") == 0 ||
strcmp(lower, "yy") == 0 ||
strcmp(lower, "yyyy") == 0 ||
@@ -101,7 +101,7 @@ parse_datepart(const char *datepart_str)
/*
* days_in_month_helper - get days in a specific month
*
- * Uses PostgreSQL's day_tab array (per PRD1b lines 235-252)
+ * Uses PostgreSQL's day_tab array.
* month is 1-based (1=January, 12=December)
*/
static int
@@ -159,8 +159,8 @@ day_of_quarter(int year, int month, int day)
/*
* bankers_round - round to 3 decimal places using HALF_EVEN (banker's rounding)
*
- * Per PRD1a FR-7: "Decimal results SHALL be rounded to exactly 3 decimal places
- * using HALF_EVEN (banker's) rounding"
+ * Decimal results are rounded to exactly 3 decimal places using HALF_EVEN
+ * (banker's) rounding for consistent, unbiased results.
*/
static double
bankers_round(double value)
@@ -194,7 +194,7 @@ bankers_round(double value)
/*
* make_numeric_result - convert double to NUMERIC with 3 decimal places
*
- * Uses string conversion approach as per PRD1b lines 167-174
+ * Uses string conversion approach for precise decimal representation.
*/
static Datum
make_numeric_result(double value)
@@ -246,7 +246,7 @@ compute_diff_week(int start_y, int start_m, int start_d,
/*
* compute_diff_month - calculate month difference using hybrid model
*
- * Per PRD1a FR-3/FR-4:
+ * Calculation model:
* - Aligned dates (same day-of-month or both end-of-month) return whole numbers
* - Non-aligned: full months + (remaining days / days in partial period)
*/
@@ -265,7 +265,7 @@ compute_diff_month(int start_y, int start_m, int start_d,
int anniversary_y, anniversary_m, anniversary_d;
int anniversary_jd, end_jd;
- /* Handle negative spans by swapping and negating result (FR-6) */
+ /* Handle negative spans by swapping and negating result */
if (start_y > end_y ||
(start_y == end_y && start_m > end_m) ||
(start_y == end_y && start_m == end_m && start_d > end_d))
@@ -281,7 +281,7 @@ compute_diff_month(int start_y, int start_m, int start_d,
negated = true;
}
- /* Check for calendar alignment (FR-4) */
+ /* Check for calendar alignment */
start_eom = is_end_of_month(start_y, start_m, start_d);
end_eom = is_end_of_month(end_y, end_m, end_d);
aligned = (start_d == end_d) || (start_eom && end_eom);
@@ -611,7 +611,7 @@ datediff_internal(const char *datepart_str,
{
DatepartType datepart = parse_datepart(datepart_str);
- /* Validate datepart (FR-2) */
+ /* Validate datepart */
if (datepart == DATEPART_INVALID)
{
ereport(ERROR,
diff --git a/contrib/pg_datemath/pg_datemath.control b/contrib/pg_datemath/pg_datemath.control
new file mode 100644
index 00000000000..0a7da533b00
--- /dev/null
+++ b/contrib/pg_datemath/pg_datemath.control
@@ -0,0 +1,7 @@
+# pg_datemath extension
+comment = 'Enhanced date difference functions with fractional precision'
+default_version = '1.0'
+module_pathname = '$libdir/pg_datemath'
+relocatable = true
+trusted = true
+
diff --git a/contrib/mssql_compat/expected/mssql_compat.out b/contrib/pg_datemath/results/pg_datemath.out
similarity index 68%
rename from contrib/mssql_compat/expected/mssql_compat.out
rename to contrib/pg_datemath/results/pg_datemath.out
index 3fd45c1ecc6..c2829f16d3c 100644
--- a/contrib/mssql_compat/expected/mssql_compat.out
+++ b/contrib/pg_datemath/results/pg_datemath.out
@@ -1,15 +1,15 @@
--
--- Test cases for mssql_compat extension
--- Covers PRD1a unit tests (UT-01 to UT-15) and edge cases (EC-01 to EC-06)
+-- Test cases for pg_datemath extension
+-- Tests datediff function with various dateparts and edge cases
--
-CREATE EXTENSION mssql_compat;
+CREATE EXTENSION pg_datemath;
--
--- Basic Day Calculations (UT-01, UT-02)
+-- Basic Day Calculations
--
-SELECT 'UT-01: Day difference basic' AS test;
- test
------------------------------
- UT-01: Day difference basic
+SELECT 'Day difference basic' AS test;
+ test
+----------------------
+ Day difference basic
(1 row)
SELECT datediff('day', '2024-01-01'::date, '2024-01-15'::date);
@@ -18,10 +18,10 @@ SELECT datediff('day', '2024-01-01'::date, '2024-01-15'::date);
14
(1 row)
-SELECT 'UT-02: Day difference negative' AS test;
- test
---------------------------------
- UT-02: Day difference negative
+SELECT 'Day difference negative' AS test;
+ test
+-------------------------
+ Day difference negative
(1 row)
SELECT datediff('day', '2024-01-15'::date, '2024-01-01'::date);
@@ -31,12 +31,12 @@ SELECT datediff('day', '2024-01-15'::date, '2024-01-01'::date);
(1 row)
--
--- Week Calculations (UT-03, UT-04)
+-- Week Calculations
--
-SELECT 'UT-03: Week exact' AS test;
- test
--------------------
- UT-03: Week exact
+SELECT 'Week exact' AS test;
+ test
+------------
+ Week exact
(1 row)
SELECT datediff('week', '2024-01-01'::date, '2024-01-08'::date);
@@ -45,10 +45,10 @@ SELECT datediff('week', '2024-01-01'::date, '2024-01-08'::date);
1.000
(1 row)
-SELECT 'UT-04: Week partial' AS test;
- test
----------------------
- UT-04: Week partial
+SELECT 'Week partial' AS test;
+ test
+--------------
+ Week partial
(1 row)
SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
@@ -58,12 +58,12 @@ SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
(1 row)
--
--- Month Calculations (UT-05, UT-06, UT-07)
+-- Month Calculations
--
-SELECT 'UT-05: Month aligned' AS test;
- test
-----------------------
- UT-05: Month aligned
+SELECT 'Month aligned' AS test;
+ test
+---------------
+ Month aligned
(1 row)
SELECT datediff('month', '2024-01-15'::date, '2024-02-15'::date);
@@ -72,10 +72,10 @@ SELECT datediff('month', '2024-01-15'::date, '2024-02-15'::date);
1.000
(1 row)
-SELECT 'UT-06: Month partial' AS test;
- test
-----------------------
- UT-06: Month partial
+SELECT 'Month partial' AS test;
+ test
+---------------
+ Month partial
(1 row)
SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
@@ -84,10 +84,10 @@ SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
1.172
(1 row)
-SELECT 'UT-07: Month end-of-month alignment' AS test;
- test
--------------------------------------
- UT-07: Month end-of-month alignment
+SELECT 'Month end-of-month alignment' AS test;
+ test
+------------------------------
+ Month end-of-month alignment
(1 row)
SELECT datediff('month', '2024-01-31'::date, '2024-02-29'::date);
@@ -97,12 +97,12 @@ SELECT datediff('month', '2024-01-31'::date, '2024-02-29'::date);
(1 row)
--
--- Quarter Calculations (UT-08, UT-09)
+-- Quarter Calculations
--
-SELECT 'UT-08: Quarter aligned' AS test;
- test
-------------------------
- UT-08: Quarter aligned
+SELECT 'Quarter aligned' AS test;
+ test
+-----------------
+ Quarter aligned
(1 row)
SELECT datediff('quarter', '2024-01-01'::date, '2024-04-01'::date);
@@ -111,10 +111,10 @@ SELECT datediff('quarter', '2024-01-01'::date, '2024-04-01'::date);
1.000
(1 row)
-SELECT 'UT-09: Quarter partial' AS test;
- test
-------------------------
- UT-09: Quarter partial
+SELECT 'Quarter partial' AS test;
+ test
+-----------------
+ Quarter partial
(1 row)
SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
@@ -124,12 +124,12 @@ SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
(1 row)
--
--- Year Calculations (UT-10, UT-11)
+-- Year Calculations
--
-SELECT 'UT-10: Year aligned' AS test;
- test
----------------------
- UT-10: Year aligned
+SELECT 'Year aligned' AS test;
+ test
+--------------
+ Year aligned
(1 row)
SELECT datediff('year', '2024-03-15'::date, '2025-03-15'::date);
@@ -138,10 +138,10 @@ SELECT datediff('year', '2024-03-15'::date, '2025-03-15'::date);
1.000
(1 row)
-SELECT 'UT-11: Year partial leap year' AS test;
- test
--------------------------------
- UT-11: Year partial leap year
+SELECT 'Year partial leap year' AS test;
+ test
+------------------------
+ Year partial leap year
(1 row)
SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
@@ -151,12 +151,12 @@ SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
(1 row)
--
--- NULL Handling (UT-12, UT-13) - STRICT functions return NULL for NULL inputs
+-- NULL Handling - STRICT functions return NULL for NULL inputs
--
-SELECT 'UT-12: NULL start date' AS test;
- test
-------------------------
- UT-12: NULL start date
+SELECT 'NULL start date' AS test;
+ test
+-----------------
+ NULL start date
(1 row)
SELECT datediff('day', NULL::date, '2024-01-15'::date);
@@ -165,10 +165,10 @@ SELECT datediff('day', NULL::date, '2024-01-15'::date);
(1 row)
-SELECT 'UT-13: NULL end date' AS test;
- test
-----------------------
- UT-13: NULL end date
+SELECT 'NULL end date' AS test;
+ test
+---------------
+ NULL end date
(1 row)
SELECT datediff('day', '2024-01-01'::date, NULL::date);
@@ -178,24 +178,24 @@ SELECT datediff('day', '2024-01-01'::date, NULL::date);
(1 row)
--
--- Invalid Datepart (UT-14)
+-- Invalid Datepart
--
-SELECT 'UT-14: Invalid datepart' AS test;
- test
--------------------------
- UT-14: Invalid datepart
+SELECT 'Invalid datepart' AS test;
+ test
+------------------
+ Invalid datepart
(1 row)
SELECT datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
ERROR: Invalid datepart: 'hour'
HINT: Valid options: year, quarter, month, week, day
--
--- Case Insensitivity (UT-15)
+-- Case Insensitivity
--
-SELECT 'UT-15: Case insensitive datepart' AS test;
- test
-----------------------------------
- UT-15: Case insensitive datepart
+SELECT 'Case insensitive datepart' AS test;
+ test
+---------------------------
+ Case insensitive datepart
(1 row)
SELECT datediff('MONTH', '2024-01-01'::date, '2024-02-01'::date);
@@ -217,12 +217,12 @@ SELECT datediff('month', '2024-01-01'::date, '2024-02-01'::date);
(1 row)
--
--- Edge Cases (EC-01 to EC-06)
+-- Edge Cases
--
-SELECT 'EC-01: Same date' AS test;
- test
-------------------
- EC-01: Same date
+SELECT 'Same date' AS test;
+ test
+-----------
+ Same date
(1 row)
SELECT datediff('day', '2024-01-01'::date, '2024-01-01'::date);
@@ -231,10 +231,10 @@ SELECT datediff('day', '2024-01-01'::date, '2024-01-01'::date);
0
(1 row)
-SELECT 'EC-02: Leap year February 29' AS test;
- test
-------------------------------
- EC-02: Leap year February 29
+SELECT 'Leap year February 29' AS test;
+ test
+-----------------------
+ Leap year February 29
(1 row)
SELECT datediff('day', '2024-02-28'::date, '2024-03-01'::date);
@@ -243,10 +243,10 @@ SELECT datediff('day', '2024-02-28'::date, '2024-03-01'::date);
2
(1 row)
-SELECT 'EC-03: Non-leap year February' AS test;
- test
--------------------------------
- EC-03: Non-leap year February
+SELECT 'Non-leap year February' AS test;
+ test
+------------------------
+ Non-leap year February
(1 row)
SELECT datediff('day', '2023-02-28'::date, '2023-03-01'::date);
@@ -255,10 +255,10 @@ SELECT datediff('day', '2023-02-28'::date, '2023-03-01'::date);
1
(1 row)
-SELECT 'EC-04: Year boundary' AS test;
- test
-----------------------
- EC-04: Year boundary
+SELECT 'Year boundary' AS test;
+ test
+---------------
+ Year boundary
(1 row)
SELECT datediff('year', '2024-12-31'::date, '2025-01-01'::date);
@@ -267,10 +267,10 @@ SELECT datediff('year', '2024-12-31'::date, '2025-01-01'::date);
0.003
(1 row)
-SELECT 'EC-05: Multi-year span' AS test;
- test
-------------------------
- EC-05: Multi-year span
+SELECT 'Multi-year span' AS test;
+ test
+-----------------
+ Multi-year span
(1 row)
SELECT datediff('year', '2020-01-01'::date, '2025-01-01'::date);
@@ -279,10 +279,10 @@ SELECT datediff('year', '2020-01-01'::date, '2025-01-01'::date);
5.000
(1 row)
-SELECT 'EC-06: Century boundary' AS test;
- test
--------------------------
- EC-06: Century boundary
+SELECT 'Century boundary' AS test;
+ test
+------------------
+ Century boundary
(1 row)
SELECT datediff('day', '1999-12-31'::date, '2000-01-01'::date);
@@ -292,7 +292,7 @@ SELECT datediff('day', '1999-12-31'::date, '2000-01-01'::date);
(1 row)
--
--- Alias Tests (from PRD1b lines 224-230)
+-- Alias Tests
--
SELECT 'Alias: yy for year' AS test;
test
@@ -411,10 +411,10 @@ SELECT datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:
--
-- Additional Month Calculation Tests
--
-SELECT 'Month: Jan 25 to Mar 10 (PRD walkthrough example)' AS test;
- test
----------------------------------------------------
- Month: Jan 25 to Mar 10 (PRD walkthrough example)
+SELECT 'Month: Jan 25 to Mar 10' AS test;
+ test
+-------------------------
+ Month: Jan 25 to Mar 10
(1 row)
SELECT datediff('month', '2024-01-25'::date, '2024-03-10'::date);
@@ -438,10 +438,10 @@ SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
--
-- Additional Quarter Calculation Tests
--
-SELECT 'Quarter: PRD walkthrough example' AS test;
- test
-----------------------------------
- Quarter: PRD walkthrough example
+SELECT 'Quarter: Q1 to Q2 partial' AS test;
+ test
+---------------------------
+ Quarter: Q1 to Q2 partial
(1 row)
SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
@@ -453,10 +453,10 @@ SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
--
-- Additional Year Calculation Tests
--
-SELECT 'Year: PRD walkthrough example' AS test;
- test
--------------------------------
- Year: PRD walkthrough example
+SELECT 'Year: partial year calculation' AS test;
+ test
+--------------------------------
+ Year: partial year calculation
(1 row)
SELECT datediff('year', '2024-03-15'::date, '2025-06-20'::date);
@@ -504,10 +504,10 @@ SELECT datediff('week', '2024-01-01'::date, '2024-01-15'::date);
2.000
(1 row)
-SELECT 'Week: PRD example 9 days' AS test;
- test
---------------------------
- Week: PRD example 9 days
+SELECT 'Week: 9 days example' AS test;
+ test
+----------------------
+ Week: 9 days example
(1 row)
SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
@@ -516,4 +516,4 @@ SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
1.286
(1 row)
-DROP EXTENSION mssql_compat;
+DROP EXTENSION pg_datemath;
diff --git a/contrib/mssql_compat/sql/datediff_advanced_edge_cases.sql b/contrib/pg_datemath/sql/datediff_advanced_edge_cases.sql
similarity index 99%
rename from contrib/mssql_compat/sql/datediff_advanced_edge_cases.sql
rename to contrib/pg_datemath/sql/datediff_advanced_edge_cases.sql
index 2491400675e..5cb92df8801 100644
--- a/contrib/mssql_compat/sql/datediff_advanced_edge_cases.sql
+++ b/contrib/pg_datemath/sql/datediff_advanced_edge_cases.sql
@@ -3,8 +3,8 @@
-- Tests scenarios not covered in basic tests
--
-DROP EXTENSION IF EXISTS mssql_compat CASCADE;
-CREATE EXTENSION mssql_compat;
+DROP EXTENSION IF EXISTS pg_datemath CASCADE;
+CREATE EXTENSION pg_datemath;
-- ============================================================================
-- SECTION 1: EXTREME DATE RANGES
diff --git a/contrib/mssql_compat/sql/datediff_comprehensive_tests.sql b/contrib/pg_datemath/sql/datediff_comprehensive_tests.sql
similarity index 84%
rename from contrib/mssql_compat/sql/datediff_comprehensive_tests.sql
rename to contrib/pg_datemath/sql/datediff_comprehensive_tests.sql
index a763d958a62..0892b0a61a6 100644
--- a/contrib/mssql_compat/sql/datediff_comprehensive_tests.sql
+++ b/contrib/pg_datemath/sql/datediff_comprehensive_tests.sql
@@ -4,8 +4,8 @@
--
-- Setup: Create extension
-DROP EXTENSION IF EXISTS mssql_compat CASCADE;
-CREATE EXTENSION mssql_compat;
+DROP EXTENSION IF EXISTS pg_datemath CASCADE;
+CREATE EXTENSION pg_datemath;
-- ============================================================================
-- SECTION 1: Test Data Setup
@@ -81,50 +81,50 @@ INSERT INTO date_test_data (description, start_date, end_date, start_ts, end_ts,
SELECT '=== DAY DATEPART TESTS ===' AS section;
--- Test 1: Basic day difference
-SELECT 'Test 1: Basic day difference' AS test_name,
+-- Basic day difference
+SELECT 'Basic day difference' AS test_name,
datediff('day', '2024-01-01', '2024-01-15') AS result,
14 AS expected,
CASE WHEN datediff('day', '2024-01-01', '2024-01-15') = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 2: Day difference using 'dd' alias
-SELECT 'Test 2: Day alias dd' AS test_name,
+-- Day difference using 'dd' alias
+SELECT 'Day alias dd' AS test_name,
datediff('dd', '2024-01-01', '2024-01-15') AS result,
14 AS expected,
CASE WHEN datediff('dd', '2024-01-01', '2024-01-15') = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 3: Day difference using 'd' alias
-SELECT 'Test 3: Day alias d' AS test_name,
+-- Day difference using 'd' alias
+SELECT 'Day alias d' AS test_name,
datediff('d', '2024-03-01', '2024-03-31') AS result,
30 AS expected,
CASE WHEN datediff('d', '2024-03-01', '2024-03-31') = 30 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 4: Day difference using 'days' alias
-SELECT 'Test 4: Day alias days' AS test_name,
+-- Day difference using 'days' alias
+SELECT 'Day alias days' AS test_name,
datediff('days', '2024-06-01', '2024-06-30') AS result,
29 AS expected,
CASE WHEN datediff('days', '2024-06-01', '2024-06-30') = 29 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 5: Negative day difference
-SELECT 'Test 5: Negative day difference' AS test_name,
+-- Negative day difference
+SELECT 'Negative day difference' AS test_name,
datediff('day', '2024-01-15', '2024-01-01') AS result,
-14 AS expected,
CASE WHEN datediff('day', '2024-01-15', '2024-01-01') = -14 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 6: Same day returns 0
-SELECT 'Test 6: Same day returns 0' AS test_name,
+-- Same day returns 0
+SELECT 'Same day returns 0' AS test_name,
datediff('day', '2024-06-15', '2024-06-15') AS result,
0 AS expected,
CASE WHEN datediff('day', '2024-06-15', '2024-06-15') = 0 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 7: Leap year February (28th to Mar 1st)
-SELECT 'Test 7: Leap year Feb 28 to Mar 1' AS test_name,
+-- Leap year February (28th to Mar 1st)
+SELECT 'Leap year Feb 28 to Mar 1' AS test_name,
datediff('day', '2024-02-28', '2024-03-01') AS result,
2 AS expected,
CASE WHEN datediff('day', '2024-02-28', '2024-03-01') = 2 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 8: Non-leap year February
-SELECT 'Test 8: Non-leap year Feb 28 to Mar 1' AS test_name,
+-- Non-leap year February
+SELECT 'Non-leap year Feb 28 to Mar 1' AS test_name,
datediff('day', '2023-02-28', '2023-03-01') AS result,
1 AS expected,
CASE WHEN datediff('day', '2023-02-28', '2023-03-01') = 1 THEN 'PASS' ELSE 'FAIL' END AS status;
@@ -135,44 +135,44 @@ SELECT 'Test 8: Non-leap year Feb 28 to Mar 1' AS test_name,
SELECT '=== WEEK DATEPART TESTS ===' AS section;
--- Test 9: Exact 1 week
-SELECT 'Test 9: Exact 1 week' AS test_name,
+-- Exact 1 week
+SELECT 'Exact 1 week' AS test_name,
datediff('week', '2024-01-01', '2024-01-08') AS result,
1.000 AS expected,
CASE WHEN datediff('week', '2024-01-01', '2024-01-08') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 10: Exact 2 weeks
-SELECT 'Test 10: Exact 2 weeks' AS test_name,
+-- Exact 2 weeks
+SELECT 'Exact 2 weeks' AS test_name,
datediff('week', '2024-01-01', '2024-01-15') AS result,
2.000 AS expected,
CASE WHEN datediff('week', '2024-01-01', '2024-01-15') = 2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 11: Partial week (9 days = 1.286 weeks)
-SELECT 'Test 11: Partial week 9 days' AS test_name,
+-- Partial week (9 days = 1.286 weeks)
+SELECT 'Partial week 9 days' AS test_name,
datediff('week', '2024-01-01', '2024-01-10') AS result,
1.286 AS expected,
CASE WHEN datediff('week', '2024-01-01', '2024-01-10') = 1.286 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 12: Week alias 'wk'
-SELECT 'Test 12: Week alias wk' AS test_name,
+-- Week alias 'wk'
+SELECT 'Week alias wk' AS test_name,
datediff('wk', '2024-01-01', '2024-01-08') AS result,
1.000 AS expected,
CASE WHEN datediff('wk', '2024-01-01', '2024-01-08') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 13: Week alias 'ww'
-SELECT 'Test 13: Week alias ww' AS test_name,
+-- Week alias 'ww'
+SELECT 'Week alias ww' AS test_name,
datediff('ww', '2024-01-01', '2024-01-22') AS result,
3.000 AS expected,
CASE WHEN datediff('ww', '2024-01-01', '2024-01-22') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 14: Week alias 'weeks'
-SELECT 'Test 14: Week alias weeks' AS test_name,
+-- Week alias 'weeks'
+SELECT 'Week alias weeks' AS test_name,
datediff('weeks', '2024-02-01', '2024-02-29') AS result,
4.000 AS expected,
CASE WHEN datediff('weeks', '2024-02-01', '2024-02-29') = 4.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 15: Negative weeks
-SELECT 'Test 15: Negative weeks' AS test_name,
+-- Negative weeks
+SELECT 'Negative weeks' AS test_name,
datediff('week', '2024-01-15', '2024-01-01') AS result,
-2.000 AS expected,
CASE WHEN datediff('week', '2024-01-15', '2024-01-01') = -2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
@@ -183,62 +183,62 @@ SELECT 'Test 15: Negative weeks' AS test_name,
SELECT '=== MONTH DATEPART TESTS ===' AS section;
--- Test 16: Aligned month (same day-of-month)
-SELECT 'Test 16: Aligned month same day' AS test_name,
+-- Aligned month (same day-of-month)
+SELECT 'Aligned month same day' AS test_name,
datediff('month', '2024-01-15', '2024-02-15') AS result,
1.000 AS expected,
CASE WHEN datediff('month', '2024-01-15', '2024-02-15') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 17: Partial month
-SELECT 'Test 17: Partial month' AS test_name,
+-- Partial month
+SELECT 'Partial month' AS test_name,
datediff('month', '2024-01-15', '2024-02-20') AS result,
1.172 AS expected,
CASE WHEN datediff('month', '2024-01-15', '2024-02-20') = 1.172 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 18: End-of-month alignment (Jan 31 -> Feb 29)
-SELECT 'Test 18: End-of-month alignment' AS test_name,
+-- End-of-month alignment (Jan 31 -> Feb 29)
+SELECT 'End-of-month alignment' AS test_name,
datediff('month', '2024-01-31', '2024-02-29') AS result,
1.000 AS expected,
CASE WHEN datediff('month', '2024-01-31', '2024-02-29') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 19: Month alias 'mm'
-SELECT 'Test 19: Month alias mm' AS test_name,
+-- Month alias 'mm'
+SELECT 'Month alias mm' AS test_name,
datediff('mm', '2024-01-01', '2024-02-01') AS result,
1.000 AS expected,
CASE WHEN datediff('mm', '2024-01-01', '2024-02-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 20: Month alias 'm'
-SELECT 'Test 20: Month alias m' AS test_name,
+-- Month alias 'm'
+SELECT 'Month alias m' AS test_name,
datediff('m', '2024-03-15', '2024-06-15') AS result,
3.000 AS expected,
CASE WHEN datediff('m', '2024-03-15', '2024-06-15') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 21: Month alias 'months'
-SELECT 'Test 21: Month alias months' AS test_name,
+-- Month alias 'months'
+SELECT 'Month alias months' AS test_name,
datediff('months', '2024-01-01', '2024-07-01') AS result,
6.000 AS expected,
CASE WHEN datediff('months', '2024-01-01', '2024-07-01') = 6.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 22: Multiple months with partial
-SELECT 'Test 22: Multiple months partial' AS test_name,
+-- Multiple months with partial
+SELECT 'Multiple months partial' AS test_name,
datediff('month', '2024-01-25', '2024-03-10') AS result,
1.483 AS expected,
CASE WHEN datediff('month', '2024-01-25', '2024-03-10') = 1.483 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 23: Negative months
-SELECT 'Test 23: Negative months' AS test_name,
+-- Negative months
+SELECT 'Negative months' AS test_name,
datediff('month', '2024-06-15', '2024-03-15') AS result,
-3.000 AS expected,
CASE WHEN datediff('month', '2024-06-15', '2024-03-15') = -3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 24: Month spanning year boundary
-SELECT 'Test 24: Month across year boundary' AS test_name,
+-- Month spanning year boundary
+SELECT 'Month across year boundary' AS test_name,
datediff('month', '2024-11-15', '2025-02-15') AS result,
3.000 AS expected,
CASE WHEN datediff('month', '2024-11-15', '2025-02-15') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 25: Less than one month
-SELECT 'Test 25: Less than one month' AS test_name,
+-- Less than one month
+SELECT 'Less than one month' AS test_name,
datediff('month', '2024-01-01', '2024-01-15') AS result,
0.452 AS expected, -- 14 days / 31 days in January
CASE WHEN datediff('month', '2024-01-01', '2024-01-15') BETWEEN 0.450 AND 0.460 THEN 'PASS' ELSE 'FAIL' END AS status;
@@ -249,50 +249,50 @@ SELECT 'Test 25: Less than one month' AS test_name,
SELECT '=== QUARTER DATEPART TESTS ===' AS section;
--- Test 26: Exact quarter aligned
-SELECT 'Test 26: Exact quarter aligned' AS test_name,
+-- Exact quarter aligned
+SELECT 'Exact quarter aligned' AS test_name,
datediff('quarter', '2024-01-01', '2024-04-01') AS result,
1.000 AS expected,
CASE WHEN datediff('quarter', '2024-01-01', '2024-04-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 27: Partial quarter
-SELECT 'Test 27: Partial quarter' AS test_name,
+-- Partial quarter
+SELECT 'Partial quarter' AS test_name,
datediff('quarter', '2024-01-15', '2024-05-20') AS result,
1.385 AS expected,
CASE WHEN datediff('quarter', '2024-01-15', '2024-05-20') = 1.385 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 28: Quarter alias 'qq'
-SELECT 'Test 28: Quarter alias qq' AS test_name,
+-- Quarter alias 'qq'
+SELECT 'Quarter alias qq' AS test_name,
datediff('qq', '2024-01-01', '2024-07-01') AS result,
2.000 AS expected,
CASE WHEN datediff('qq', '2024-01-01', '2024-07-01') = 2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 29: Quarter alias 'q'
-SELECT 'Test 29: Quarter alias q' AS test_name,
+-- Quarter alias 'q'
+SELECT 'Quarter alias q' AS test_name,
datediff('q', '2024-01-01', '2024-10-01') AS result,
3.000 AS expected,
CASE WHEN datediff('q', '2024-01-01', '2024-10-01') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 30: Quarter alias 'quarters'
-SELECT 'Test 30: Quarter alias quarters' AS test_name,
+-- Quarter alias 'quarters'
+SELECT 'Quarter alias quarters' AS test_name,
datediff('quarters', '2024-01-01', '2025-01-01') AS result,
4.000 AS expected,
CASE WHEN datediff('quarters', '2024-01-01', '2025-01-01') = 4.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 31: Negative quarters
-SELECT 'Test 31: Negative quarters' AS test_name,
+-- Negative quarters
+SELECT 'Negative quarters' AS test_name,
datediff('quarter', '2024-10-01', '2024-04-01') AS result,
-2.000 AS expected,
CASE WHEN datediff('quarter', '2024-10-01', '2024-04-01') = -2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 32: Less than one quarter
-SELECT 'Test 32: Less than one quarter' AS test_name,
+-- Less than one quarter
+SELECT 'Less than one quarter' AS test_name,
datediff('quarter', '2024-01-01', '2024-02-15') AS result,
0.495 AS expected, -- ~45 days / 91 days
CASE WHEN datediff('quarter', '2024-01-01', '2024-02-15') BETWEEN 0.490 AND 0.500 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 33: Quarter across year boundary
-SELECT 'Test 33: Quarter across year boundary' AS test_name,
+-- Quarter across year boundary
+SELECT 'Quarter across year boundary' AS test_name,
datediff('quarter', '2024-10-01', '2025-04-01') AS result,
2.000 AS expected,
CASE WHEN datediff('quarter', '2024-10-01', '2025-04-01') = 2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
@@ -303,56 +303,56 @@ SELECT 'Test 33: Quarter across year boundary' AS test_name,
SELECT '=== YEAR DATEPART TESTS ===' AS section;
--- Test 34: Exact year aligned
-SELECT 'Test 34: Exact year aligned' AS test_name,
+-- Exact year aligned
+SELECT 'Exact year aligned' AS test_name,
datediff('year', '2024-03-15', '2025-03-15') AS result,
1.000 AS expected,
CASE WHEN datediff('year', '2024-03-15', '2025-03-15') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 35: Partial year in leap year
-SELECT 'Test 35: Partial year leap year' AS test_name,
+-- Partial year in leap year
+SELECT 'Partial year leap year' AS test_name,
datediff('year', '2024-01-01', '2024-07-01') AS result,
0.497 AS expected, -- 182 days / 366
CASE WHEN datediff('year', '2024-01-01', '2024-07-01') BETWEEN 0.495 AND 0.500 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 36: Year alias 'yy'
-SELECT 'Test 36: Year alias yy' AS test_name,
+-- Year alias 'yy'
+SELECT 'Year alias yy' AS test_name,
datediff('yy', '2020-01-01', '2025-01-01') AS result,
5.000 AS expected,
CASE WHEN datediff('yy', '2020-01-01', '2025-01-01') = 5.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 37: Year alias 'yyyy'
-SELECT 'Test 37: Year alias yyyy' AS test_name,
+-- Year alias 'yyyy'
+SELECT 'Year alias yyyy' AS test_name,
datediff('yyyy', '2024-06-15', '2027-06-15') AS result,
3.000 AS expected,
CASE WHEN datediff('yyyy', '2024-06-15', '2027-06-15') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 38: Year alias 'y'
-SELECT 'Test 38: Year alias y' AS test_name,
+-- Year alias 'y'
+SELECT 'Year alias y' AS test_name,
datediff('y', '2024-01-01', '2024-12-31') AS result,
0.997 AS expected, -- 365 days / 366
CASE WHEN datediff('y', '2024-01-01', '2024-12-31') BETWEEN 0.995 AND 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 39: Year alias 'years'
-SELECT 'Test 39: Year alias years' AS test_name,
+-- Year alias 'years'
+SELECT 'Year alias years' AS test_name,
datediff('years', '2014-06-15', '2024-06-15') AS result,
10.000 AS expected,
CASE WHEN datediff('years', '2014-06-15', '2024-06-15') = 10.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 40: Year boundary crossing (1 day)
-SELECT 'Test 40: Year boundary 1 day' AS test_name,
+-- Year boundary crossing (1 day)
+SELECT 'Year boundary 1 day' AS test_name,
datediff('year', '2024-12-31', '2025-01-01') AS result,
0.003 AS expected, -- 1 day / 365
CASE WHEN datediff('year', '2024-12-31', '2025-01-01') BETWEEN 0.001 AND 0.005 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 41: Negative years
-SELECT 'Test 41: Negative years' AS test_name,
+-- Negative years
+SELECT 'Negative years' AS test_name,
datediff('year', '2025-06-15', '2020-06-15') AS result,
-5.000 AS expected,
CASE WHEN datediff('year', '2025-06-15', '2020-06-15') = -5.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 42: Feb 29 leap year to Feb 28 non-leap (aligned)
-SELECT 'Test 42: Feb 29 to Feb 28 alignment' AS test_name,
+-- Feb 29 leap year to Feb 28 non-leap (aligned)
+SELECT 'Feb 29 to Feb 28 alignment' AS test_name,
datediff('year', '2024-02-29', '2025-02-28') AS result,
1.000 AS expected,
CASE WHEN datediff('year', '2024-02-29', '2025-02-28') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
@@ -363,20 +363,20 @@ SELECT 'Test 42: Feb 29 to Feb 28 alignment' AS test_name,
SELECT '=== CASE INSENSITIVITY TESTS ===' AS section;
--- Test 43: UPPERCASE datepart
-SELECT 'Test 43: UPPERCASE MONTH' AS test_name,
+-- UPPERCASE datepart
+SELECT 'UPPERCASE MONTH' AS test_name,
datediff('MONTH', '2024-01-01', '2024-02-01') AS result,
1.000 AS expected,
CASE WHEN datediff('MONTH', '2024-01-01', '2024-02-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 44: Mixed case datepart
-SELECT 'Test 44: Mixed case Quarter' AS test_name,
+-- Mixed case datepart
+SELECT 'Mixed case Quarter' AS test_name,
datediff('QuArTeR', '2024-01-01', '2024-04-01') AS result,
1.000 AS expected,
CASE WHEN datediff('QuArTeR', '2024-01-01', '2024-04-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 45: Mixed case alias
-SELECT 'Test 45: Mixed case alias YY' AS test_name,
+-- Mixed case alias
+SELECT 'Mixed case alias YY' AS test_name,
datediff('Yy', '2024-01-01', '2025-01-01') AS result,
1.000 AS expected,
CASE WHEN datediff('Yy', '2024-01-01', '2025-01-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
@@ -387,20 +387,20 @@ SELECT 'Test 45: Mixed case alias YY' AS test_name,
SELECT '=== TIMESTAMP TESTS ===' AS section;
--- Test 46: Timestamp day difference
-SELECT 'Test 46: Timestamp day diff' AS test_name,
+-- Timestamp day difference
+SELECT 'Timestamp day diff' AS test_name,
datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp) AS result,
14 AS expected,
CASE WHEN datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp) = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 47: Timestamp month difference
-SELECT 'Test 47: Timestamp month diff' AS test_name,
+-- Timestamp month difference
+SELECT 'Timestamp month diff' AS test_name,
datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp) AS result,
1.172 AS expected,
CASE WHEN datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp) = 1.172 THEN 'PASS' ELSE 'FAIL' END AS status;
--- Test 48: Timestamptz day difference
-SELECT 'Test 48: Timestamptz day diff' AS test_name,
+-- Timestamptz day difference
+SELECT 'Timestamptz day diff' AS test_name,
datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz) AS result,
14 AS expected,
CASE WHEN datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz) = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
@@ -411,8 +411,8 @@ SELECT 'Test 48: Timestamptz day diff' AS test_name,
SELECT '=== ERROR HANDLING TESTS ===' AS section;
--- Test 49: Invalid datepart should error
-SELECT 'Test 49: Invalid datepart error' AS test_name;
+-- Invalid datepart should error
+SELECT 'Invalid datepart error' AS test_name;
DO $$
BEGIN
PERFORM datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
@@ -421,8 +421,8 @@ EXCEPTION WHEN invalid_parameter_value THEN
RAISE NOTICE 'PASS: Correctly raised error for invalid datepart';
END $$;
--- Test 50: NULL handling (should return NULL)
-SELECT 'Test 50: NULL handling' AS test_name,
+-- NULL handling (should return NULL)
+SELECT 'NULL handling' AS test_name,
datediff('day', NULL::date, '2024-01-15'::date) IS NULL AS null_start_returns_null,
datediff('day', '2024-01-01'::date, NULL::date) IS NULL AS null_end_returns_null,
CASE
@@ -592,7 +592,7 @@ FROM date_test_data;
-- Cleanup
DROP TABLE IF EXISTS date_test_data;
-- Note: Keeping extension for further manual testing
--- DROP EXTENSION IF EXISTS mssql_compat;
+-- DROP EXTENSION IF EXISTS pg_datemath;
SELECT 'All comprehensive tests completed!' AS final_status;
diff --git a/contrib/mssql_compat/sql/mssql_compat.sql b/contrib/pg_datemath/sql/pg_datemath.sql
similarity index 67%
rename from contrib/mssql_compat/sql/mssql_compat.sql
rename to contrib/pg_datemath/sql/pg_datemath.sql
index f625868b3f7..54e1d11d17a 100644
--- a/contrib/mssql_compat/sql/mssql_compat.sql
+++ b/contrib/pg_datemath/sql/pg_datemath.sql
@@ -1,104 +1,104 @@
--
--- Test cases for mssql_compat extension
--- Covers PRD1a unit tests (UT-01 to UT-15) and edge cases (EC-01 to EC-06)
+-- Test cases for pg_datemath extension
+-- Tests datediff function with various dateparts and edge cases
--
-CREATE EXTENSION mssql_compat;
+CREATE EXTENSION pg_datemath;
--
--- Basic Day Calculations (UT-01, UT-02)
+-- Basic Day Calculations
--
-SELECT 'UT-01: Day difference basic' AS test;
+SELECT 'Day difference basic' AS test;
SELECT datediff('day', '2024-01-01'::date, '2024-01-15'::date);
-SELECT 'UT-02: Day difference negative' AS test;
+SELECT 'Day difference negative' AS test;
SELECT datediff('day', '2024-01-15'::date, '2024-01-01'::date);
--
--- Week Calculations (UT-03, UT-04)
+-- Week Calculations
--
-SELECT 'UT-03: Week exact' AS test;
+SELECT 'Week exact' AS test;
SELECT datediff('week', '2024-01-01'::date, '2024-01-08'::date);
-SELECT 'UT-04: Week partial' AS test;
+SELECT 'Week partial' AS test;
SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
--
--- Month Calculations (UT-05, UT-06, UT-07)
+-- Month Calculations
--
-SELECT 'UT-05: Month aligned' AS test;
+SELECT 'Month aligned' AS test;
SELECT datediff('month', '2024-01-15'::date, '2024-02-15'::date);
-SELECT 'UT-06: Month partial' AS test;
+SELECT 'Month partial' AS test;
SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
-SELECT 'UT-07: Month end-of-month alignment' AS test;
+SELECT 'Month end-of-month alignment' AS test;
SELECT datediff('month', '2024-01-31'::date, '2024-02-29'::date);
--
--- Quarter Calculations (UT-08, UT-09)
+-- Quarter Calculations
--
-SELECT 'UT-08: Quarter aligned' AS test;
+SELECT 'Quarter aligned' AS test;
SELECT datediff('quarter', '2024-01-01'::date, '2024-04-01'::date);
-SELECT 'UT-09: Quarter partial' AS test;
+SELECT 'Quarter partial' AS test;
SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
--
--- Year Calculations (UT-10, UT-11)
+-- Year Calculations
--
-SELECT 'UT-10: Year aligned' AS test;
+SELECT 'Year aligned' AS test;
SELECT datediff('year', '2024-03-15'::date, '2025-03-15'::date);
-SELECT 'UT-11: Year partial leap year' AS test;
+SELECT 'Year partial leap year' AS test;
SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
--
--- NULL Handling (UT-12, UT-13) - STRICT functions return NULL for NULL inputs
+-- NULL Handling - STRICT functions return NULL for NULL inputs
--
-SELECT 'UT-12: NULL start date' AS test;
+SELECT 'NULL start date' AS test;
SELECT datediff('day', NULL::date, '2024-01-15'::date);
-SELECT 'UT-13: NULL end date' AS test;
+SELECT 'NULL end date' AS test;
SELECT datediff('day', '2024-01-01'::date, NULL::date);
--
--- Invalid Datepart (UT-14)
+-- Invalid Datepart
--
-SELECT 'UT-14: Invalid datepart' AS test;
+SELECT 'Invalid datepart' AS test;
SELECT datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
--
--- Case Insensitivity (UT-15)
+-- Case Insensitivity
--
-SELECT 'UT-15: Case insensitive datepart' AS test;
+SELECT 'Case insensitive datepart' AS test;
SELECT datediff('MONTH', '2024-01-01'::date, '2024-02-01'::date);
SELECT datediff('Month', '2024-01-01'::date, '2024-02-01'::date);
SELECT datediff('month', '2024-01-01'::date, '2024-02-01'::date);
--
--- Edge Cases (EC-01 to EC-06)
+-- Edge Cases
--
-SELECT 'EC-01: Same date' AS test;
+SELECT 'Same date' AS test;
SELECT datediff('day', '2024-01-01'::date, '2024-01-01'::date);
-SELECT 'EC-02: Leap year February 29' AS test;
+SELECT 'Leap year February 29' AS test;
SELECT datediff('day', '2024-02-28'::date, '2024-03-01'::date);
-SELECT 'EC-03: Non-leap year February' AS test;
+SELECT 'Non-leap year February' AS test;
SELECT datediff('day', '2023-02-28'::date, '2023-03-01'::date);
-SELECT 'EC-04: Year boundary' AS test;
+SELECT 'Year boundary' AS test;
SELECT datediff('year', '2024-12-31'::date, '2025-01-01'::date);
-SELECT 'EC-05: Multi-year span' AS test;
+SELECT 'Multi-year span' AS test;
SELECT datediff('year', '2020-01-01'::date, '2025-01-01'::date);
-SELECT 'EC-06: Century boundary' AS test;
+SELECT 'Century boundary' AS test;
SELECT datediff('day', '1999-12-31'::date, '2000-01-01'::date);
--
--- Alias Tests (from PRD1b lines 224-230)
+-- Alias Tests
--
SELECT 'Alias: yy for year' AS test;
SELECT datediff('yy', '2024-01-01'::date, '2025-01-01'::date);
@@ -136,7 +136,7 @@ SELECT datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:
--
-- Additional Month Calculation Tests
--
-SELECT 'Month: Jan 25 to Mar 10 (PRD walkthrough example)' AS test;
+SELECT 'Month: Jan 25 to Mar 10' AS test;
SELECT datediff('month', '2024-01-25'::date, '2024-03-10'::date);
SELECT 'Month: subscription proration example' AS test;
@@ -145,13 +145,13 @@ SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
--
-- Additional Quarter Calculation Tests
--
-SELECT 'Quarter: PRD walkthrough example' AS test;
+SELECT 'Quarter: Q1 to Q2 partial' AS test;
SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
--
-- Additional Year Calculation Tests
--
-SELECT 'Year: PRD walkthrough example' AS test;
+SELECT 'Year: partial year calculation' AS test;
SELECT datediff('year', '2024-03-15'::date, '2025-06-20'::date);
SELECT 'Year: exact 5-year tenure' AS test;
@@ -166,8 +166,8 @@ SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
SELECT 'Week: exact 2 weeks' AS test;
SELECT datediff('week', '2024-01-01'::date, '2024-01-15'::date);
-SELECT 'Week: PRD example 9 days' AS test;
+SELECT 'Week: 9 days example' AS test;
SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
-DROP EXTENSION mssql_compat;
+DROP EXTENSION pg_datemath;
--
2.52.0
Re: [PATCH] contrib: Add pg_datemath extension with datediff function
Hi
st 26. 11. 2025 v 21:26 odesílatel Myles Lewis <myles93@sbcglobal.net>
napsal:
Thank you Peter, Michael, and Pavel for the thoughtful feedback on my
initial submission.After considering your points, I've reframed this patch:
Key Changes:
1. Renamed from mssql_compat to pg_datemath - Removes any implication
of tracking SQL Server compatibility, which was never the intent. This is a
standalone utility for fractional date difference calculations.
2. Clearly differentiated semantics - The datediff() function in this
extension returns NUMERIC with fractional precision (e.g., 1.5 months),
using a hybrid calculation model: full calendar units plus contextual
fractions. This is fundamentally different from MSSQL's integer
boundary-crossing semantics.
3. Test naming cleaned up - Removed numeric enumeration from test
cases per Pavel's feedback.Why contrib rather than external:
- The calculation model is self-contained with no external dependencies
- Single function with clear, stable semantics (day, week, month,
quarter, year)
- Fills a practical gap for proration/tenure calculations without
requiring complex EXTRACT + AGE compositions
- No ongoing compatibility burden with external systemsUse cases this addresses:
- Subscription billing proration (e.g., "1.172 months" for partial
billing)
- Employee tenure calculations with fractional years
- Contract duration analysis
- Invoice aging reportsThe function supports aliases (yy, mm, dd, etc.) for convenience but
maintains PostgreSQL-native semantics throughout.Patch attached. Happy to iterate further on naming, positioning, or scope.
looks so this is written with AI. Please, don't do it.
You wrote a patch against your previous patch - you should send a patch
against Postgres.
In regress tests we doesn't use useless SELECTs like:
SELECT 'NULL end date' AS test;
Personally, semantically this is very specific functionality, and it really
should be external extensions. https://pgxn.org/ is perfect place for this.
Show quoted text
Thanks!
Myles
On Nov 25, 2025, at 10:29 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:Hi
út 25. 11. 2025 v 21:15 odesílatel Peter Eisentraut <peter@eisentraut.org>
napsal:On 25.11.25 04:25, Myles Lewis wrote:
I'd like to propose a new contrib extension: mssql_compat, which
provides
SQL Server compatible date functions starting with DATEDIFF.
I think this could best live as an external project.
orafce is a similar project but for Oracle. There might also be others
for other products.For projects like this it is better to be an external project - it doesn't
depend on Postgres release cycles - so development can be faster. Really,
significantly faster.
And there is bigger space for some experiments and improvements - and for
future changes.Regards
Pavel
Re: [PATCH] contrib: Add pg_datemath extension with datediff function
Thanks for the feedback.
I've reworked this based on your comments:
- Clean patch against master (not against my previous submission)
- Removed the SELECT 'label' AS test statements from the regression tests
- Renamed from mssql_compat to pg_datemath since this isn't tracking SQL Server semantics
The function returns NUMERIC with fractional precision (e.g. 1.5 months) which is different from how SQL Server's DATEDIFF works. It's meant for things like subscription proration where you need partial periods.
I understand the concern about this being better suited for PGXN. I still think there's value in having it in contrib since the calculation is self-contained and doesn't require ongoing compatibility work with external systems, but I'm happy to move it there if that's the consensus.
Patch attached.
Myles

Show quoted text
On Nov 26, 2025, at 9:18 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi
st 26. 11. 2025 v 21:26 odesílatel Myles Lewis <myles93@sbcglobal.net <mailto:myles93@sbcglobal.net>> napsal:
Thank you Peter, Michael, and Pavel for the thoughtful feedback on my initial submission.
After considering your points, I've reframed this patch:
Key Changes:
Renamed from mssql_compat to pg_datemath - Removes any implication of tracking SQL Server compatibility, which was never the intent. This is a standalone utility for fractional date difference calculations.
Clearly differentiated semantics - The datediff() function in this extension returns NUMERIC with fractional precision (e.g., 1.5 months), using a hybrid calculation model: full calendar units plus contextual fractions. This is fundamentally different from MSSQL's integer boundary-crossing semantics.
Test naming cleaned up - Removed numeric enumeration from test cases per Pavel's feedback.Why contrib rather than external:
The calculation model is self-contained with no external dependencies
Single function with clear, stable semantics (day, week, month, quarter, year)
Fills a practical gap for proration/tenure calculations without requiring complex EXTRACT + AGE compositions
No ongoing compatibility burden with external systemsUse cases this addresses:
Subscription billing proration (e.g., "1.172 months" for partial billing)
Employee tenure calculations with fractional years
Contract duration analysis
Invoice aging reportsThe function supports aliases (yy, mm, dd, etc.) for convenience but maintains PostgreSQL-native semantics throughout.
Patch attached. Happy to iterate further on naming, positioning, or scope.
looks so this is written with AI. Please, don't do it.
You wrote a patch against your previous patch - you should send a patch against Postgres.
In regress tests we doesn't use useless SELECTs like:
SELECT 'NULL end date' AS test;
Personally, semantically this is very specific functionality, and it really should be external extensions. https://pgxn.org/ is perfect place for this.
Thanks!
Myles
On Nov 25, 2025, at 10:29 PM, Pavel Stehule <pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>> wrote:
Hi
út 25. 11. 2025 v 21:15 odesílatel Peter Eisentraut <peter@eisentraut.org <mailto:peter@eisentraut.org>> napsal:
On 25.11.25 04:25, Myles Lewis wrote:
I'd like to propose a new contrib extension: mssql_compat, which provides
SQL Server compatible date functions starting with DATEDIFF.I think this could best live as an external project.
orafce is a similar project but for Oracle. There might also be others
for other products.For projects like this it is better to be an external project - it doesn't depend on Postgres release cycles - so development can be faster. Really, significantly faster.
And there is bigger space for some experiments and improvements - and for future changes.Regards
Pavel
Attachments:
0001-Add-pg_datemath-contrib-module-with-datediff-functio.patchapplication/octet-stream; name=0001-Add-pg_datemath-contrib-module-with-datediff-functio.patch; x-unix-mode=0644Download
From 4c3fd2cb44a7749ac0f0501ff59178102eba6714 Mon Sep 17 00:00:00 2001
From: mlx93 <mylesethan93@gmail.com>
Date: Mon, 1 Dec 2025 16:35:29 -0600
Subject: [PATCH] Add pg_datemath contrib module with datediff function
This extension provides a datediff(datepart, start, end) function that
calculates the difference between two dates as a NUMERIC value with
fractional precision.
The function uses a hybrid calculation model: full calendar units plus
contextual fractions based on actual period lengths. This supports
use cases like subscription proration and tenure calculations where
fractional periods are needed.
Supported dateparts: year, quarter, month, week, day (with aliases)
---
contrib/meson.build | 1 +
contrib/pg_datemath/Makefile | 21 +
contrib/pg_datemath/expected/pg_datemath.out | 291 +++++++
contrib/pg_datemath/meson.build | 36 +
contrib/pg_datemath/pg_datemath--1.0.sql | 56 ++
contrib/pg_datemath/pg_datemath.c | 751 ++++++++++++++++++
contrib/pg_datemath/pg_datemath.control | 7 +
.../sql/datediff_advanced_edge_cases.sql | 489 ++++++++++++
.../sql/datediff_comprehensive_tests.sql | 598 ++++++++++++++
contrib/pg_datemath/sql/pg_datemath.sql | 112 +++
10 files changed, 2362 insertions(+)
create mode 100644 contrib/pg_datemath/Makefile
create mode 100644 contrib/pg_datemath/expected/pg_datemath.out
create mode 100644 contrib/pg_datemath/meson.build
create mode 100644 contrib/pg_datemath/pg_datemath--1.0.sql
create mode 100644 contrib/pg_datemath/pg_datemath.c
create mode 100644 contrib/pg_datemath/pg_datemath.control
create mode 100644 contrib/pg_datemath/sql/datediff_advanced_edge_cases.sql
create mode 100644 contrib/pg_datemath/sql/datediff_comprehensive_tests.sql
create mode 100644 contrib/pg_datemath/sql/pg_datemath.sql
diff --git a/contrib/meson.build b/contrib/meson.build
index ed30ee7d639..f5b1bbcff9b 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -40,6 +40,7 @@ subdir('jsonb_plpython')
subdir('lo')
subdir('ltree')
subdir('ltree_plpython')
+subdir('pg_datemath')
subdir('oid2name')
subdir('pageinspect')
subdir('passwordcheck')
diff --git a/contrib/pg_datemath/Makefile b/contrib/pg_datemath/Makefile
new file mode 100644
index 00000000000..e981cdcfe91
--- /dev/null
+++ b/contrib/pg_datemath/Makefile
@@ -0,0 +1,21 @@
+# contrib/pg_datemath/Makefile
+
+MODULES = pg_datemath
+
+EXTENSION = pg_datemath
+DATA = pg_datemath--1.0.sql
+PGFILEDESC = "pg_datemath - Enhanced date difference functions"
+
+REGRESS = pg_datemath
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_datemath
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
diff --git a/contrib/pg_datemath/expected/pg_datemath.out b/contrib/pg_datemath/expected/pg_datemath.out
new file mode 100644
index 00000000000..d5ee4e9f08b
--- /dev/null
+++ b/contrib/pg_datemath/expected/pg_datemath.out
@@ -0,0 +1,291 @@
+--
+-- Test cases for pg_datemath extension
+-- Tests datediff function with various dateparts and edge cases
+--
+CREATE EXTENSION pg_datemath;
+--
+-- Basic Day Calculations
+--
+SELECT datediff('day', '2024-01-01'::date, '2024-01-15'::date);
+ datediff
+----------
+ 14
+(1 row)
+
+SELECT datediff('day', '2024-01-15'::date, '2024-01-01'::date);
+ datediff
+----------
+ -14
+(1 row)
+
+--
+-- Week Calculations
+--
+SELECT datediff('week', '2024-01-01'::date, '2024-01-08'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
+ datediff
+----------
+ 1.286
+(1 row)
+
+--
+-- Month Calculations
+--
+SELECT datediff('month', '2024-01-15'::date, '2024-02-15'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
+ datediff
+----------
+ 1.172
+(1 row)
+
+SELECT datediff('month', '2024-01-31'::date, '2024-02-29'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+--
+-- Quarter Calculations
+--
+SELECT datediff('quarter', '2024-01-01'::date, '2024-04-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
+ datediff
+----------
+ 1.385
+(1 row)
+
+--
+-- Year Calculations
+--
+SELECT datediff('year', '2024-03-15'::date, '2025-03-15'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
+ datediff
+----------
+ 0.497
+(1 row)
+
+--
+-- NULL Handling - STRICT functions return NULL for NULL inputs
+--
+SELECT datediff('day', NULL::date, '2024-01-15'::date);
+ datediff
+----------
+
+(1 row)
+
+SELECT datediff('day', '2024-01-01'::date, NULL::date);
+ datediff
+----------
+
+(1 row)
+
+--
+-- Invalid Datepart
+--
+SELECT datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
+ERROR: Invalid datepart: 'hour'
+HINT: Valid options: year, quarter, month, week, day
+--
+-- Case Insensitivity
+--
+SELECT datediff('MONTH', '2024-01-01'::date, '2024-02-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('Month', '2024-01-01'::date, '2024-02-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('month', '2024-01-01'::date, '2024-02-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+--
+-- Edge Cases
+--
+SELECT datediff('day', '2024-01-01'::date, '2024-01-01'::date);
+ datediff
+----------
+ 0
+(1 row)
+
+SELECT datediff('day', '2024-02-28'::date, '2024-03-01'::date);
+ datediff
+----------
+ 2
+(1 row)
+
+SELECT datediff('day', '2023-02-28'::date, '2023-03-01'::date);
+ datediff
+----------
+ 1
+(1 row)
+
+SELECT datediff('year', '2024-12-31'::date, '2025-01-01'::date);
+ datediff
+----------
+ 0.003
+(1 row)
+
+SELECT datediff('year', '2020-01-01'::date, '2025-01-01'::date);
+ datediff
+----------
+ 5.000
+(1 row)
+
+SELECT datediff('day', '1999-12-31'::date, '2000-01-01'::date);
+ datediff
+----------
+ 1
+(1 row)
+
+--
+-- Alias Tests
+--
+SELECT datediff('yy', '2024-01-01'::date, '2025-01-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('yyyy', '2024-01-01'::date, '2025-01-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('mm', '2024-01-15'::date, '2024-02-15'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('qq', '2024-01-01'::date, '2024-04-01'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('wk', '2024-01-01'::date, '2024-01-08'::date);
+ datediff
+----------
+ 1.000
+(1 row)
+
+SELECT datediff('dd', '2024-01-01'::date, '2024-01-15'::date);
+ datediff
+----------
+ 14
+(1 row)
+
+--
+-- Timestamp Tests
+--
+SELECT datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp);
+ datediff
+----------
+ 14
+(1 row)
+
+SELECT datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp);
+ datediff
+----------
+ 1.172
+(1 row)
+
+--
+-- Timestamptz Tests
+--
+SELECT datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz);
+ datediff
+----------
+ 14
+(1 row)
+
+--
+-- Additional Month Calculation Tests
+--
+SELECT datediff('month', '2024-01-25'::date, '2024-03-10'::date);
+ datediff
+----------
+ 1.483
+(1 row)
+
+SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
+ datediff
+----------
+ 1.172
+(1 row)
+
+--
+-- Additional Quarter Calculation Tests
+--
+SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
+ datediff
+----------
+ 1.385
+(1 row)
+
+--
+-- Additional Year Calculation Tests
+--
+SELECT datediff('year', '2024-03-15'::date, '2025-06-20'::date);
+ datediff
+----------
+ 1.266
+(1 row)
+
+SELECT datediff('year', '2020-03-15'::date, '2025-03-15'::date);
+ datediff
+----------
+ 5.000
+(1 row)
+
+SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
+ datediff
+----------
+ 0.497
+(1 row)
+
+--
+-- Week Calculation Additional Tests
+--
+SELECT datediff('week', '2024-01-01'::date, '2024-01-15'::date);
+ datediff
+----------
+ 2.000
+(1 row)
+
+SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
+ datediff
+----------
+ 1.286
+(1 row)
+
+DROP EXTENSION pg_datemath;
diff --git a/contrib/pg_datemath/meson.build b/contrib/pg_datemath/meson.build
new file mode 100644
index 00000000000..4378a0f574e
--- /dev/null
+++ b/contrib/pg_datemath/meson.build
@@ -0,0 +1,36 @@
+# Copyright (c) 2022-2025, PostgreSQL Global Development Group
+
+pg_datemath_sources = files(
+ 'pg_datemath.c',
+)
+
+if host_system == 'windows'
+ pg_datemath_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'pg_datemath',
+ '--FILEDESC', 'pg_datemath - Enhanced date difference functions',])
+endif
+
+pg_datemath = shared_module('pg_datemath',
+ pg_datemath_sources,
+ kwargs: contrib_mod_args,
+)
+
+contrib_targets += pg_datemath
+
+install_data(
+ 'pg_datemath--1.0.sql',
+ 'pg_datemath.control',
+ kwargs: contrib_data_args,
+)
+
+tests += {
+ 'name': 'pg_datemath',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'regress': {
+ 'sql': [
+ 'pg_datemath',
+ ],
+ },
+}
+
diff --git a/contrib/pg_datemath/pg_datemath--1.0.sql b/contrib/pg_datemath/pg_datemath--1.0.sql
new file mode 100644
index 00000000000..b141c86d79c
--- /dev/null
+++ b/contrib/pg_datemath/pg_datemath--1.0.sql
@@ -0,0 +1,56 @@
+/* contrib/pg_datemath/pg_datemath--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pg_datemath" to load this file. \quit
+
+--
+-- datediff(datepart, start_date, end_date) - Enhanced date difference calculation
+--
+-- Returns the difference between two dates in the specified datepart unit.
+-- Supports: year, quarter, month, week, day (and common aliases)
+--
+-- This implementation provides mathematically accurate results using a hybrid
+-- calculation model: full calendar units plus contextual fractions based on
+-- actual period lengths. This is useful for proration, tenure calculation,
+-- and other scenarios requiring precise fractional date differences.
+--
+
+-- Date version
+CREATE FUNCTION datediff(
+ datepart TEXT,
+ start_date DATE,
+ end_date DATE
+)
+RETURNS NUMERIC
+AS 'MODULE_PATHNAME', 'datediff_date'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+COMMENT ON FUNCTION datediff(TEXT, DATE, DATE) IS
+'Calculate the difference between two dates in the specified unit (year, quarter, month, week, day)';
+
+-- Timestamp version
+CREATE FUNCTION datediff(
+ datepart TEXT,
+ start_ts TIMESTAMP,
+ end_ts TIMESTAMP
+)
+RETURNS NUMERIC
+AS 'MODULE_PATHNAME', 'datediff_timestamp'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+COMMENT ON FUNCTION datediff(TEXT, TIMESTAMP, TIMESTAMP) IS
+'Calculate the difference between two timestamps in the specified unit (year, quarter, month, week, day)';
+
+-- Timestamptz version
+CREATE FUNCTION datediff(
+ datepart TEXT,
+ start_tstz TIMESTAMPTZ,
+ end_tstz TIMESTAMPTZ
+)
+RETURNS NUMERIC
+AS 'MODULE_PATHNAME', 'datediff_timestamptz'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+COMMENT ON FUNCTION datediff(TEXT, TIMESTAMPTZ, TIMESTAMPTZ) IS
+'Calculate the difference between two timestamps with timezone in the specified unit (year, quarter, month, week, day)';
+
diff --git a/contrib/pg_datemath/pg_datemath.c b/contrib/pg_datemath/pg_datemath.c
new file mode 100644
index 00000000000..fd8c49bfec7
--- /dev/null
+++ b/contrib/pg_datemath/pg_datemath.c
@@ -0,0 +1,751 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_datemath.c
+ * Enhanced date difference functions for PostgreSQL.
+ *
+ * This extension provides datediff(datepart, start_date, end_date) which
+ * calculates the difference between two dates using a hybrid calculation
+ * model: full calendar units plus contextual fractions based on actual
+ * period lengths.
+ *
+ * Copyright (c) 2024, PostgreSQL Global Development Group
+ *
+ * contrib/pg_datemath/pg_datemath.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <ctype.h>
+#include <math.h>
+
+#include "datatype/timestamp.h"
+#include "fmgr.h"
+#include "utils/builtins.h"
+#include "utils/date.h"
+#include "utils/datetime.h"
+#include "utils/numeric.h"
+#include "utils/timestamp.h"
+
+PG_MODULE_MAGIC_EXT(
+ .name = "pg_datemath",
+ .version = PG_VERSION
+);
+
+/*
+ * Datepart enumeration for routing calculation logic
+ */
+typedef enum
+{
+ DATEPART_DAY,
+ DATEPART_WEEK,
+ DATEPART_MONTH,
+ DATEPART_QUARTER,
+ DATEPART_YEAR,
+ DATEPART_INVALID
+} DatepartType;
+
+/*
+ * parse_datepart - convert datepart string to enum
+ *
+ * Performs case-insensitive comparison and handles aliases.
+ * Returns DATEPART_INVALID for unrecognized input.
+ */
+static DatepartType
+parse_datepart(const char *datepart_str)
+{
+ char lower[32];
+ int i;
+
+ /* Convert to lowercase for comparison */
+ for (i = 0; datepart_str[i] && i < 31; i++)
+ lower[i] = tolower((unsigned char) datepart_str[i]);
+ lower[i] = '\0';
+
+ /* Match canonical names and aliases */
+ if (strcmp(lower, "year") == 0 ||
+ strcmp(lower, "yy") == 0 ||
+ strcmp(lower, "yyyy") == 0 ||
+ strcmp(lower, "y") == 0 ||
+ strcmp(lower, "years") == 0)
+ return DATEPART_YEAR;
+
+ if (strcmp(lower, "quarter") == 0 ||
+ strcmp(lower, "qq") == 0 ||
+ strcmp(lower, "q") == 0 ||
+ strcmp(lower, "quarters") == 0)
+ return DATEPART_QUARTER;
+
+ if (strcmp(lower, "month") == 0 ||
+ strcmp(lower, "mm") == 0 ||
+ strcmp(lower, "m") == 0 ||
+ strcmp(lower, "months") == 0)
+ return DATEPART_MONTH;
+
+ if (strcmp(lower, "week") == 0 ||
+ strcmp(lower, "wk") == 0 ||
+ strcmp(lower, "ww") == 0 ||
+ strcmp(lower, "w") == 0 ||
+ strcmp(lower, "weeks") == 0)
+ return DATEPART_WEEK;
+
+ if (strcmp(lower, "day") == 0 ||
+ strcmp(lower, "dd") == 0 ||
+ strcmp(lower, "d") == 0 ||
+ strcmp(lower, "days") == 0)
+ return DATEPART_DAY;
+
+ return DATEPART_INVALID;
+}
+
+/*
+ * days_in_month_helper - get days in a specific month
+ *
+ * Uses PostgreSQL's day_tab array.
+ * month is 1-based (1=January, 12=December)
+ */
+static int
+days_in_month_helper(int year, int month)
+{
+ return day_tab[isleap(year) ? 1 : 0][month - 1];
+}
+
+/*
+ * is_end_of_month - check if day is the last day of its month
+ */
+static bool
+is_end_of_month(int year, int month, int day)
+{
+ return day == days_in_month_helper(year, month);
+}
+
+/*
+ * days_in_quarter - get total days in a specific quarter
+ *
+ * Quarter is 1-4.
+ * Q1: Jan+Feb+Mar, Q2: Apr+May+Jun, Q3: Jul+Aug+Sep, Q4: Oct+Nov+Dec
+ */
+static int
+days_in_quarter(int year, int quarter)
+{
+ int first_month = (quarter - 1) * 3 + 1;
+ int days = 0;
+ int i;
+
+ for (i = 0; i < 3; i++)
+ days += days_in_month_helper(year, first_month + i);
+
+ return days;
+}
+
+/*
+ * day_of_quarter - get day position within a quarter (1-92)
+ */
+static int
+day_of_quarter(int year, int month, int day)
+{
+ int quarter = (month - 1) / 3 + 1;
+ int first_month = (quarter - 1) * 3 + 1;
+ int days = 0;
+ int m;
+
+ /* Sum days in complete months before this month within the quarter */
+ for (m = first_month; m < month; m++)
+ days += days_in_month_helper(year, m);
+
+ return days + day;
+}
+
+/*
+ * bankers_round - round to 3 decimal places using HALF_EVEN (banker's rounding)
+ *
+ * Decimal results are rounded to exactly 3 decimal places using HALF_EVEN
+ * (banker's) rounding for consistent, unbiased results.
+ */
+static double
+bankers_round(double value)
+{
+ double scaled = value * 1000.0;
+ double integer_part;
+ double frac = modf(scaled, &integer_part);
+ int64 int_val = (int64) integer_part;
+
+ /*
+ * Banker's rounding: round half to even
+ * If fraction is exactly 0.5, round to nearest even number
+ */
+ if (fabs(frac) == 0.5)
+ {
+ /* Round to even */
+ if (int_val % 2 == 0)
+ scaled = integer_part; /* Already even, truncate */
+ else
+ scaled = integer_part + (value >= 0 ? 1.0 : -1.0); /* Round away */
+ }
+ else
+ {
+ /* Standard rounding */
+ scaled = round(scaled);
+ }
+
+ return scaled / 1000.0;
+}
+
+/*
+ * make_numeric_result - convert double to NUMERIC with 3 decimal places
+ *
+ * Uses string conversion approach for precise decimal representation.
+ */
+static Datum
+make_numeric_result(double value)
+{
+ char result_str[32];
+ Datum result;
+
+ snprintf(result_str, sizeof(result_str), "%.3f", value);
+ result = DirectFunctionCall3(numeric_in,
+ CStringGetDatum(result_str),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1));
+ return result;
+}
+
+/*
+ * compute_diff_day - calculate day difference
+ *
+ * Simple subtraction, returns whole number as NUMERIC.
+ */
+static Datum
+compute_diff_day(int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ int start_jd = date2j(start_y, start_m, start_d);
+ int end_jd = date2j(end_y, end_m, end_d);
+ int64 diff = (int64) end_jd - (int64) start_jd;
+
+ return NumericGetDatum(int64_to_numeric(diff));
+}
+
+/*
+ * compute_diff_week - calculate week difference
+ *
+ * Total days / 7, rounded to 3 decimal places.
+ */
+static Datum
+compute_diff_week(int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ int start_jd = date2j(start_y, start_m, start_d);
+ int end_jd = date2j(end_y, end_m, end_d);
+ int64 days = (int64) end_jd - (int64) start_jd;
+ double weeks = (double) days / 7.0;
+
+ return make_numeric_result(bankers_round(weeks));
+}
+
+/*
+ * compute_diff_month - calculate month difference using hybrid model
+ *
+ * Calculation model:
+ * - Aligned dates (same day-of-month or both end-of-month) return whole numbers
+ * - Non-aligned: full months + (remaining days / days in partial period)
+ */
+static Datum
+compute_diff_month(int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ bool negated = false;
+ int full_months;
+ int remaining_days;
+ int partial_period_days;
+ double result;
+ bool start_eom;
+ bool end_eom;
+ bool aligned;
+ int anniversary_y, anniversary_m, anniversary_d;
+ int anniversary_jd, end_jd;
+
+ /* Handle negative spans by swapping and negating result */
+ if (start_y > end_y ||
+ (start_y == end_y && start_m > end_m) ||
+ (start_y == end_y && start_m == end_m && start_d > end_d))
+ {
+ int tmp_y = start_y, tmp_m = start_m, tmp_d = start_d;
+
+ start_y = end_y;
+ start_m = end_m;
+ start_d = end_d;
+ end_y = tmp_y;
+ end_m = tmp_m;
+ end_d = tmp_d;
+ negated = true;
+ }
+
+ /* Check for calendar alignment */
+ start_eom = is_end_of_month(start_y, start_m, start_d);
+ end_eom = is_end_of_month(end_y, end_m, end_d);
+ aligned = (start_d == end_d) || (start_eom && end_eom);
+
+ /* Calculate full months */
+ full_months = (end_y - start_y) * 12 + (end_m - start_m);
+
+ if (aligned)
+ {
+ /* Aligned dates return whole numbers */
+ result = (double) full_months;
+ }
+ else
+ {
+ /*
+ * Find the last "anniversary" before or on end_date.
+ * Anniversary is the same day-of-month as start_d, or end-of-month
+ * if start was end-of-month.
+ */
+ if (end_d < start_d)
+ full_months--;
+
+ if (full_months < 0)
+ full_months = 0;
+
+ /* Calculate anniversary date */
+ anniversary_y = start_y + (start_m + full_months - 1) / 12;
+ anniversary_m = ((start_m - 1 + full_months) % 12) + 1;
+
+ /*
+ * Handle case where start_d doesn't exist in anniversary month
+ * (e.g., Jan 31 -> Feb has no 31st)
+ */
+ if (start_d > days_in_month_helper(anniversary_y, anniversary_m))
+ anniversary_d = days_in_month_helper(anniversary_y, anniversary_m);
+ else
+ anniversary_d = start_d;
+
+ /* Calculate remaining days after anniversary */
+ anniversary_jd = date2j(anniversary_y, anniversary_m, anniversary_d);
+ end_jd = date2j(end_y, end_m, end_d);
+ remaining_days = end_jd - anniversary_jd;
+
+ /*
+ * Calculate partial period length (days from anniversary to next
+ * anniversary)
+ */
+ {
+ int next_anniversary_y = anniversary_y + (anniversary_m) / 12;
+ int next_anniversary_m = (anniversary_m % 12) + 1;
+ int next_anniversary_d;
+ int next_anniversary_jd;
+
+ if (start_d > days_in_month_helper(next_anniversary_y, next_anniversary_m))
+ next_anniversary_d = days_in_month_helper(next_anniversary_y, next_anniversary_m);
+ else
+ next_anniversary_d = start_d;
+
+ next_anniversary_jd = date2j(next_anniversary_y, next_anniversary_m, next_anniversary_d);
+ partial_period_days = next_anniversary_jd - anniversary_jd;
+ }
+
+ if (partial_period_days <= 0)
+ partial_period_days = 1; /* Safety guard */
+
+ result = (double) full_months + (double) remaining_days / (double) partial_period_days;
+ }
+
+ if (negated)
+ result = -result;
+
+ return make_numeric_result(bankers_round(result));
+}
+
+/*
+ * compute_diff_quarter - calculate quarter difference using hybrid model
+ *
+ * Similar to month but with quarter-based periods.
+ */
+static Datum
+compute_diff_quarter(int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ bool negated = false;
+ int start_quarter, end_quarter;
+ int start_day_of_qtr, end_day_of_qtr;
+ int full_quarters;
+ int remaining_days;
+ int partial_period_days;
+ double result;
+
+ /* Handle negative spans */
+ if (start_y > end_y ||
+ (start_y == end_y && start_m > end_m) ||
+ (start_y == end_y && start_m == end_m && start_d > end_d))
+ {
+ int tmp_y = start_y, tmp_m = start_m, tmp_d = start_d;
+
+ start_y = end_y;
+ start_m = end_m;
+ start_d = end_d;
+ end_y = tmp_y;
+ end_m = tmp_m;
+ end_d = tmp_d;
+ negated = true;
+ }
+
+ start_quarter = (start_m - 1) / 3 + 1;
+ end_quarter = (end_m - 1) / 3 + 1;
+ start_day_of_qtr = day_of_quarter(start_y, start_m, start_d);
+ end_day_of_qtr = day_of_quarter(end_y, end_m, end_d);
+
+ /* Calculate full quarters */
+ full_quarters = (end_y - start_y) * 4 + (end_quarter - start_quarter);
+
+ /* Check alignment: same day-of-quarter position */
+ if (start_day_of_qtr == end_day_of_qtr)
+ {
+ result = (double) full_quarters;
+ }
+ else
+ {
+ /*
+ * Non-aligned: find anniversary (same position in quarter), calculate
+ * remaining days
+ */
+ int anniversary_y, anniversary_quarter, anniversary_m, anniversary_d;
+ int anniversary_jd, end_jd;
+ /* Adjust full_quarters if end is before anniversary position */
+ if (end_day_of_qtr < start_day_of_qtr)
+ full_quarters--;
+
+ if (full_quarters < 0)
+ full_quarters = 0;
+
+ /* Calculate anniversary date */
+ anniversary_quarter = start_quarter + full_quarters;
+ anniversary_y = start_y + (anniversary_quarter - 1) / 4;
+ anniversary_quarter = ((anniversary_quarter - 1) % 4) + 1;
+
+ /* Convert day-of-quarter back to month and day */
+ {
+ int first_month = (anniversary_quarter - 1) * 3 + 1;
+ int days_remaining = start_day_of_qtr;
+ int m;
+ bool found = false;
+
+ anniversary_m = first_month;
+ anniversary_d = 1; /* Default initialization */
+ for (m = first_month; m <= first_month + 2 && days_remaining > 0; m++)
+ {
+ int days_in_m = days_in_month_helper(anniversary_y, m);
+
+ if (days_remaining <= days_in_m)
+ {
+ anniversary_m = m;
+ anniversary_d = days_remaining;
+ found = true;
+ break;
+ }
+ days_remaining -= days_in_m;
+ }
+
+ /* Handle overflow (day position exceeds quarter length) */
+ if (!found)
+ {
+ anniversary_m = first_month + 2;
+ anniversary_d = days_in_month_helper(anniversary_y, anniversary_m);
+ }
+ }
+
+ /* Ensure anniversary_d is valid */
+ if (anniversary_d > days_in_month_helper(anniversary_y, anniversary_m))
+ anniversary_d = days_in_month_helper(anniversary_y, anniversary_m);
+
+ anniversary_jd = date2j(anniversary_y, anniversary_m, anniversary_d);
+ end_jd = date2j(end_y, end_m, end_d);
+ remaining_days = end_jd - anniversary_jd;
+
+ /* Partial period is the quarter containing the anniversary */
+ partial_period_days = days_in_quarter(anniversary_y, anniversary_quarter);
+
+ if (partial_period_days <= 0)
+ partial_period_days = 1;
+
+ result = (double) full_quarters + (double) remaining_days / (double) partial_period_days;
+ }
+
+ if (negated)
+ result = -result;
+
+ return make_numeric_result(bankers_round(result));
+}
+
+/*
+ * compute_diff_year - calculate year difference using hybrid model
+ *
+ * Similar to month but with year-based periods.
+ */
+static Datum
+compute_diff_year(int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ bool negated = false;
+ int full_years;
+ int remaining_days;
+ int partial_period_days;
+ double result;
+ bool aligned;
+ int anniversary_y, anniversary_m, anniversary_d;
+ int anniversary_jd, end_jd;
+
+ /* Handle negative spans */
+ if (start_y > end_y ||
+ (start_y == end_y && start_m > end_m) ||
+ (start_y == end_y && start_m == end_m && start_d > end_d))
+ {
+ int tmp_y = start_y, tmp_m = start_m, tmp_d = start_d;
+
+ start_y = end_y;
+ start_m = end_m;
+ start_d = end_d;
+ end_y = tmp_y;
+ end_m = tmp_m;
+ end_d = tmp_d;
+ negated = true;
+ }
+
+ /* Check alignment: same month and day, or Feb 29 -> Feb 28 in non-leap */
+ aligned = (start_m == end_m && start_d == end_d);
+
+ /* Special case: Feb 29 in leap year aligns with Feb 28 in non-leap */
+ if (!aligned && start_m == 2 && start_d == 29 && end_m == 2 && end_d == 28)
+ {
+ if (!isleap(end_y))
+ aligned = true;
+ }
+ if (!aligned && start_m == 2 && start_d == 28 && end_m == 2 && end_d == 29)
+ {
+ if (!isleap(start_y))
+ aligned = true;
+ }
+
+ /* Calculate full years */
+ full_years = end_y - start_y;
+ if (end_m < start_m || (end_m == start_m && end_d < start_d))
+ full_years--;
+
+ if (full_years < 0)
+ full_years = 0;
+
+ if (aligned && full_years > 0)
+ {
+ result = (double) full_years;
+ }
+ else if (aligned && full_years == 0 && end_y > start_y)
+ {
+ /* Exact one year */
+ result = 1.0;
+ }
+ else if (start_y == end_y && start_m == end_m && start_d == end_d)
+ {
+ /* Same date */
+ result = 0.0;
+ }
+ else
+ {
+ /* Non-aligned: calculate fractional part */
+ anniversary_y = start_y + full_years;
+ anniversary_m = start_m;
+
+ /* Handle Feb 29 when anniversary year is not a leap year */
+ if (start_m == 2 && start_d == 29 && !isleap(anniversary_y))
+ anniversary_d = 28;
+ else if (start_d > days_in_month_helper(anniversary_y, anniversary_m))
+ anniversary_d = days_in_month_helper(anniversary_y, anniversary_m);
+ else
+ anniversary_d = start_d;
+
+ anniversary_jd = date2j(anniversary_y, anniversary_m, anniversary_d);
+ end_jd = date2j(end_y, end_m, end_d);
+ remaining_days = end_jd - anniversary_jd;
+
+ /*
+ * Partial period: days from anniversary to next anniversary The
+ * period uses the year that contains the partial span
+ */
+ {
+ int next_anniversary_y = anniversary_y + 1;
+ int next_anniversary_m = anniversary_m;
+ int next_anniversary_d;
+ int next_anniversary_jd;
+
+ if (start_m == 2 && start_d == 29 && !isleap(next_anniversary_y))
+ next_anniversary_d = 28;
+ else if (start_d > days_in_month_helper(next_anniversary_y, next_anniversary_m))
+ next_anniversary_d = days_in_month_helper(next_anniversary_y, next_anniversary_m);
+ else
+ next_anniversary_d = start_d;
+
+ next_anniversary_jd = date2j(next_anniversary_y, next_anniversary_m, next_anniversary_d);
+ partial_period_days = next_anniversary_jd - anniversary_jd;
+ }
+
+ if (partial_period_days <= 0)
+ partial_period_days = 1;
+
+ result = (double) full_years + (double) remaining_days / (double) partial_period_days;
+ }
+
+ if (negated)
+ result = -result;
+
+ return make_numeric_result(bankers_round(result));
+}
+
+/*
+ * datediff_internal - core calculation dispatcher
+ *
+ * Takes year, month, day for both dates and computes the difference
+ * based on the specified datepart.
+ */
+static Datum
+datediff_internal(const char *datepart_str,
+ int start_y, int start_m, int start_d,
+ int end_y, int end_m, int end_d)
+{
+ DatepartType datepart = parse_datepart(datepart_str);
+
+ /* Validate datepart */
+ if (datepart == DATEPART_INVALID)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Invalid datepart: '%s'", datepart_str),
+ errhint("Valid options: year, quarter, month, week, day")));
+ }
+
+ /* Dispatch to appropriate calculator */
+ switch (datepart)
+ {
+ case DATEPART_DAY:
+ return compute_diff_day(start_y, start_m, start_d,
+ end_y, end_m, end_d);
+ case DATEPART_WEEK:
+ return compute_diff_week(start_y, start_m, start_d,
+ end_y, end_m, end_d);
+ case DATEPART_MONTH:
+ return compute_diff_month(start_y, start_m, start_d,
+ end_y, end_m, end_d);
+ case DATEPART_QUARTER:
+ return compute_diff_quarter(start_y, start_m, start_d,
+ end_y, end_m, end_d);
+ case DATEPART_YEAR:
+ return compute_diff_year(start_y, start_m, start_d,
+ end_y, end_m, end_d);
+ default:
+ /* Should not reach here */
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("Unexpected datepart type")));
+ return (Datum) 0; /* Keep compiler happy */
+ }
+}
+
+/*-------------------------------------------------------------------------
+ * Public Entry Points
+ *-------------------------------------------------------------------------
+ */
+
+PG_FUNCTION_INFO_V1(datediff_date);
+
+/*
+ * datediff_date - DATE version of datediff
+ */
+Datum
+datediff_date(PG_FUNCTION_ARGS)
+{
+ text *datepart_text = PG_GETARG_TEXT_PP(0);
+ DateADT start_date = PG_GETARG_DATEADT(1);
+ DateADT end_date = PG_GETARG_DATEADT(2);
+ char *datepart_str;
+ int start_y, start_m, start_d;
+ int end_y, end_m, end_d;
+
+ datepart_str = text_to_cstring(datepart_text);
+
+ /* Convert dates to year/month/day using j2date */
+ j2date(start_date + POSTGRES_EPOCH_JDATE, &start_y, &start_m, &start_d);
+ j2date(end_date + POSTGRES_EPOCH_JDATE, &end_y, &end_m, &end_d);
+
+ return datediff_internal(datepart_str,
+ start_y, start_m, start_d,
+ end_y, end_m, end_d);
+}
+
+PG_FUNCTION_INFO_V1(datediff_timestamp);
+
+/*
+ * datediff_timestamp - TIMESTAMP version of datediff
+ *
+ * Ignores time component, uses only date portion.
+ */
+Datum
+datediff_timestamp(PG_FUNCTION_ARGS)
+{
+ text *datepart_text = PG_GETARG_TEXT_PP(0);
+ Timestamp start_ts = PG_GETARG_TIMESTAMP(1);
+ Timestamp end_ts = PG_GETARG_TIMESTAMP(2);
+ char *datepart_str;
+ struct pg_tm start_tm, end_tm;
+ fsec_t fsec;
+
+ datepart_str = text_to_cstring(datepart_text);
+
+ /* Decompose timestamps to get date components */
+ if (timestamp2tm(start_ts, NULL, &start_tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ if (timestamp2tm(end_ts, NULL, &end_tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ return datediff_internal(datepart_str,
+ start_tm.tm_year, start_tm.tm_mon, start_tm.tm_mday,
+ end_tm.tm_year, end_tm.tm_mon, end_tm.tm_mday);
+}
+
+PG_FUNCTION_INFO_V1(datediff_timestamptz);
+
+/*
+ * datediff_timestamptz - TIMESTAMPTZ version of datediff
+ *
+ * Converts to local time then uses date portion.
+ */
+Datum
+datediff_timestamptz(PG_FUNCTION_ARGS)
+{
+ text *datepart_text = PG_GETARG_TEXT_PP(0);
+ TimestampTz start_tstz = PG_GETARG_TIMESTAMPTZ(1);
+ TimestampTz end_tstz = PG_GETARG_TIMESTAMPTZ(2);
+ char *datepart_str;
+ struct pg_tm start_tm, end_tm;
+ fsec_t fsec;
+ int tz;
+
+ datepart_str = text_to_cstring(datepart_text);
+
+ /* Decompose timestamps with timezone to get date components */
+ if (timestamp2tm(start_tstz, &tz, &start_tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ if (timestamp2tm(end_tstz, &tz, &end_tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ return datediff_internal(datepart_str,
+ start_tm.tm_year, start_tm.tm_mon, start_tm.tm_mday,
+ end_tm.tm_year, end_tm.tm_mon, end_tm.tm_mday);
+}
+
diff --git a/contrib/pg_datemath/pg_datemath.control b/contrib/pg_datemath/pg_datemath.control
new file mode 100644
index 00000000000..0a7da533b00
--- /dev/null
+++ b/contrib/pg_datemath/pg_datemath.control
@@ -0,0 +1,7 @@
+# pg_datemath extension
+comment = 'Enhanced date difference functions with fractional precision'
+default_version = '1.0'
+module_pathname = '$libdir/pg_datemath'
+relocatable = true
+trusted = true
+
diff --git a/contrib/pg_datemath/sql/datediff_advanced_edge_cases.sql b/contrib/pg_datemath/sql/datediff_advanced_edge_cases.sql
new file mode 100644
index 00000000000..5cb92df8801
--- /dev/null
+++ b/contrib/pg_datemath/sql/datediff_advanced_edge_cases.sql
@@ -0,0 +1,489 @@
+--
+-- Advanced Edge Cases and Real-World Table Tests for DATEDIFF
+-- Tests scenarios not covered in basic tests
+--
+
+DROP EXTENSION IF EXISTS pg_datemath CASCADE;
+CREATE EXTENSION pg_datemath;
+
+-- ============================================================================
+-- SECTION 1: EXTREME DATE RANGES
+-- ============================================================================
+
+SELECT '=== EXTREME DATE RANGE TESTS ===' AS section;
+
+-- Very old dates (before 1900)
+SELECT 'Old dates: 1800s' AS test,
+ datediff('year', '1850-06-15', '1900-06-15') AS years_diff,
+ datediff('day', '1899-12-31', '1900-01-01') AS day_across_1900;
+
+-- Very future dates
+SELECT 'Future dates: 2100s' AS test,
+ datediff('year', '2024-01-01', '2100-01-01') AS years_to_2100,
+ datediff('month', '2099-06-15', '2100-06-15') AS months_across_century;
+
+-- Huge date spans (1000+ years)
+SELECT 'Huge span: 1000 years' AS test,
+ datediff('year', '1024-01-01', '2024-01-01') AS millennium,
+ datediff('day', '1024-01-01', '2024-01-01') AS days_in_millennium;
+
+-- ============================================================================
+-- SECTION 2: BOUNDARY CONDITIONS
+-- ============================================================================
+
+SELECT '=== BOUNDARY CONDITION TESTS ===' AS section;
+
+-- First/Last day of year
+SELECT 'Year boundaries' AS test,
+ datediff('day', '2024-01-01', '2024-12-31') AS full_year_days,
+ datediff('year', '2024-01-01', '2024-12-31') AS almost_year;
+
+-- First/Last day of month combinations
+SELECT 'Month boundaries: all 12 months' AS test,
+ datediff('month', '2024-01-01', '2024-01-31') AS jan,
+ datediff('month', '2024-02-01', '2024-02-29') AS feb_leap,
+ datediff('month', '2024-03-01', '2024-03-31') AS mar,
+ datediff('month', '2024-04-01', '2024-04-30') AS apr;
+
+-- Quarter boundaries
+SELECT 'Quarter boundaries' AS test,
+ datediff('quarter', '2024-01-01', '2024-03-31') AS q1_full,
+ datediff('quarter', '2024-04-01', '2024-06-30') AS q2_full,
+ datediff('quarter', '2024-07-01', '2024-09-30') AS q3_full,
+ datediff('quarter', '2024-10-01', '2024-12-31') AS q4_full;
+
+-- ============================================================================
+-- SECTION 3: LEAP YEAR EDGE CASES
+-- ============================================================================
+
+SELECT '=== LEAP YEAR EDGE CASES ===' AS section;
+
+-- Feb 29 to Feb 28 across years
+SELECT 'Feb 29 spanning multiple years' AS test,
+ datediff('year', '2020-02-29', '2024-02-29') AS leap_to_leap,
+ datediff('year', '2024-02-29', '2028-02-29') AS next_leap_cycle;
+
+-- Feb 29 to March 1 in same year
+SELECT 'Feb 29 to Mar 1 same year' AS test,
+ datediff('day', '2024-02-29', '2024-03-01') AS feb29_to_mar1,
+ datediff('month', '2024-02-29', '2024-03-29') AS month_from_feb29;
+
+-- Century years (divisible by 100 but not 400)
+SELECT 'Century year edge case (1900 not leap, 2000 is leap)' AS test,
+ datediff('day', '1900-02-28', '1900-03-01') AS y1900_not_leap,
+ datediff('day', '2000-02-28', '2000-03-01') AS y2000_is_leap;
+
+-- ============================================================================
+-- SECTION 4: REAL-WORLD TABLE SCENARIOS
+-- ============================================================================
+
+SELECT '=== TABLE-BASED REAL-WORLD TESTS ===' AS section;
+
+-- Create comprehensive test tables
+DROP TABLE IF EXISTS employees CASCADE;
+DROP TABLE IF EXISTS subscriptions CASCADE;
+DROP TABLE IF EXISTS orders CASCADE;
+DROP TABLE IF EXISTS contracts CASCADE;
+
+-- Employee table with hire dates
+CREATE TABLE employees (
+ id SERIAL PRIMARY KEY,
+ name TEXT NOT NULL,
+ hire_date DATE NOT NULL,
+ termination_date DATE,
+ department TEXT,
+ salary NUMERIC(10,2)
+);
+
+INSERT INTO employees (name, hire_date, termination_date, department, salary) VALUES
+('Alice Johnson', '2015-03-15', NULL, 'Engineering', 125000),
+('Bob Smith', '2018-07-01', NULL, 'Sales', 85000),
+('Carol White', '2020-01-10', '2023-06-30', 'Marketing', 72000),
+('David Brown', '2019-02-28', NULL, 'Engineering', 115000),
+('Eve Davis', '2021-12-31', NULL, 'HR', 68000),
+('Frank Miller', '2016-06-15', '2022-12-31', 'Engineering', 130000),
+('Grace Lee', '2024-02-29', NULL, 'Sales', 78000), -- Leap year hire
+('Henry Wilson', '2010-01-01', NULL, 'Executive', 250000),
+('Ivy Chen', '2023-11-15', NULL, 'Engineering', 95000),
+('Jack Taylor', '2017-08-20', NULL, 'Sales', 92000);
+
+-- Subscription table with various billing cycles
+CREATE TABLE subscriptions (
+ id SERIAL PRIMARY KEY,
+ customer_name TEXT NOT NULL,
+ plan_type TEXT NOT NULL,
+ start_date DATE NOT NULL,
+ end_date DATE,
+ monthly_rate NUMERIC(8,2),
+ billing_cycle TEXT -- 'monthly', 'quarterly', 'annual'
+);
+
+INSERT INTO subscriptions (customer_name, plan_type, start_date, end_date, monthly_rate, billing_cycle) VALUES
+('Acme Corp', 'Enterprise', '2023-01-15', '2024-01-15', 999.99, 'annual'),
+('Beta Inc', 'Pro', '2023-06-01', '2024-03-18', 199.99, 'monthly'),
+('Gamma LLC', 'Basic', '2022-12-31', NULL, 49.99, 'monthly'),
+('Delta Co', 'Enterprise', '2024-02-29', NULL, 1499.99, 'annual'), -- Leap year start
+('Echo Systems', 'Pro', '2023-03-15', '2023-09-15', 299.99, 'quarterly'),
+('Foxtrot Ltd', 'Basic', '2020-01-01', '2024-01-01', 29.99, 'monthly'),
+('Gulf Corp', 'Enterprise', '2021-07-01', NULL, 899.99, 'quarterly'),
+('Hotel Inc', 'Pro', '2023-11-30', '2024-02-29', 249.99, 'monthly'); -- End on leap day
+
+-- Orders table for aging analysis
+CREATE TABLE orders (
+ id SERIAL PRIMARY KEY,
+ customer_id INT,
+ order_date DATE NOT NULL,
+ due_date DATE NOT NULL,
+ paid_date DATE,
+ amount NUMERIC(12,2)
+);
+
+INSERT INTO orders (customer_id, order_date, due_date, paid_date, amount) VALUES
+(1, '2024-01-15', '2024-02-14', '2024-02-10', 5000.00),
+(2, '2024-02-01', '2024-03-02', NULL, 7500.00), -- Unpaid
+(3, '2023-06-15', '2023-07-15', '2023-09-20', 3200.00), -- Paid late
+(4, '2024-03-01', '2024-03-31', NULL, 12000.00), -- Unpaid
+(1, '2023-12-01', '2024-01-01', '2023-12-28', 8500.00),
+(5, '2023-01-01', '2023-02-01', NULL, 4500.00), -- Very old unpaid
+(2, '2024-01-31', '2024-02-29', '2024-02-29', 6000.00), -- Paid on leap day
+(3, '2023-11-15', '2023-12-15', '2024-01-10', 9000.00);
+
+-- Contracts table
+CREATE TABLE contracts (
+ id SERIAL PRIMARY KEY,
+ vendor TEXT NOT NULL,
+ start_date DATE NOT NULL,
+ end_date DATE NOT NULL,
+ value NUMERIC(15,2),
+ auto_renew BOOLEAN DEFAULT false
+);
+
+INSERT INTO contracts (vendor, start_date, end_date, value, auto_renew) VALUES
+('Microsoft', '2022-01-01', '2025-01-01', 500000.00, true),
+('AWS', '2023-06-15', '2024-06-15', 250000.00, true),
+('Salesforce', '2021-03-01', '2024-02-29', 180000.00, false), -- Ends on leap day
+('Oracle', '2020-07-01', '2023-06-30', 320000.00, false),
+('Google', '2024-01-01', '2027-01-01', 450000.00, true);
+
+-- ============================================================================
+-- SECTION 5: EMPLOYEE TENURE CALCULATIONS
+-- ============================================================================
+
+SELECT '=== EMPLOYEE TENURE ANALYSIS ===' AS section;
+
+-- Current tenure for active employees
+SELECT
+ name,
+ hire_date,
+ datediff('year', hire_date, CURRENT_DATE) AS years_tenure,
+ datediff('month', hire_date, CURRENT_DATE) AS months_tenure,
+ datediff('day', hire_date, CURRENT_DATE) AS days_tenure,
+ CASE
+ WHEN datediff('year', hire_date, CURRENT_DATE) >= 10 THEN 'Veteran (10+ years)'
+ WHEN datediff('year', hire_date, CURRENT_DATE) >= 5 THEN 'Senior (5-10 years)'
+ WHEN datediff('year', hire_date, CURRENT_DATE) >= 2 THEN 'Mid-level (2-5 years)'
+ WHEN datediff('year', hire_date, CURRENT_DATE) >= 1 THEN 'Junior (1-2 years)'
+ ELSE 'New hire (<1 year)'
+ END AS tenure_level
+FROM employees
+WHERE termination_date IS NULL
+ORDER BY hire_date;
+
+-- Tenure at termination for departed employees
+SELECT
+ name,
+ hire_date,
+ termination_date,
+ datediff('year', hire_date, termination_date) AS years_worked,
+ datediff('month', hire_date, termination_date) AS months_worked
+FROM employees
+WHERE termination_date IS NOT NULL
+ORDER BY termination_date DESC;
+
+-- Average tenure by department
+SELECT
+ department,
+ COUNT(*) AS employee_count,
+ ROUND(AVG(datediff('year', hire_date, COALESCE(termination_date, CURRENT_DATE))), 2) AS avg_years,
+ ROUND(AVG(datediff('month', hire_date, COALESCE(termination_date, CURRENT_DATE))), 2) AS avg_months
+FROM employees
+GROUP BY department
+ORDER BY avg_years DESC;
+
+-- ============================================================================
+-- SECTION 6: SUBSCRIPTION BILLING & PRORATION
+-- ============================================================================
+
+SELECT '=== SUBSCRIPTION BILLING ANALYSIS ===' AS section;
+
+-- Active subscription duration
+SELECT
+ customer_name,
+ plan_type,
+ start_date,
+ COALESCE(end_date, CURRENT_DATE) AS effective_end,
+ datediff('month', start_date, COALESCE(end_date, CURRENT_DATE)) AS months_active,
+ monthly_rate,
+ ROUND((datediff('month', start_date, COALESCE(end_date, CURRENT_DATE)) * monthly_rate)::numeric, 2) AS total_billed
+FROM subscriptions
+ORDER BY months_active DESC;
+
+-- Proration calculation for partial months
+SELECT
+ customer_name,
+ start_date,
+ end_date,
+ monthly_rate,
+ datediff('month', start_date, end_date) AS exact_months,
+ FLOOR(datediff('month', start_date, end_date)::numeric) AS full_months,
+ (datediff('month', start_date, end_date) - FLOOR(datediff('month', start_date, end_date)::numeric)) AS partial_month_fraction,
+ ROUND((datediff('month', start_date, end_date) * monthly_rate)::numeric, 2) AS prorated_total
+FROM subscriptions
+WHERE end_date IS NOT NULL;
+
+-- ============================================================================
+-- SECTION 7: ORDER AGING & PAYMENT ANALYSIS
+-- ============================================================================
+
+SELECT '=== ORDER AGING ANALYSIS ===' AS section;
+
+-- Aging buckets for unpaid orders
+SELECT
+ id AS order_id,
+ order_date,
+ due_date,
+ amount,
+ datediff('day', due_date, CURRENT_DATE) AS days_overdue,
+ CASE
+ WHEN datediff('day', due_date, CURRENT_DATE) <= 0 THEN 'Not Yet Due'
+ WHEN datediff('day', due_date, CURRENT_DATE) <= 30 THEN '1-30 Days'
+ WHEN datediff('day', due_date, CURRENT_DATE) <= 60 THEN '31-60 Days'
+ WHEN datediff('day', due_date, CURRENT_DATE) <= 90 THEN '61-90 Days'
+ ELSE '90+ Days'
+ END AS aging_bucket
+FROM orders
+WHERE paid_date IS NULL
+ORDER BY days_overdue DESC;
+
+-- Payment timing analysis
+SELECT
+ id AS order_id,
+ due_date,
+ paid_date,
+ datediff('day', due_date, paid_date) AS days_from_due,
+ CASE
+ WHEN datediff('day', due_date, paid_date) < 0 THEN 'Early'
+ WHEN datediff('day', due_date, paid_date) = 0 THEN 'On Time'
+ WHEN datediff('day', due_date, paid_date) <= 7 THEN 'Within 1 Week'
+ WHEN datediff('day', due_date, paid_date) <= 30 THEN 'Within 1 Month'
+ ELSE 'Over 1 Month Late'
+ END AS payment_status
+FROM orders
+WHERE paid_date IS NOT NULL;
+
+-- ============================================================================
+-- SECTION 8: CONTRACT MANAGEMENT
+-- ============================================================================
+
+SELECT '=== CONTRACT ANALYSIS ===' AS section;
+
+-- Contract duration and remaining time
+SELECT
+ vendor,
+ start_date,
+ end_date,
+ datediff('year', start_date, end_date) AS contract_years,
+ datediff('month', CURRENT_DATE, end_date) AS months_remaining,
+ datediff('day', CURRENT_DATE, end_date) AS days_remaining,
+ CASE
+ WHEN datediff('day', CURRENT_DATE, end_date) < 0 THEN 'Expired'
+ WHEN datediff('day', CURRENT_DATE, end_date) <= 30 THEN 'Expiring Soon'
+ WHEN datediff('day', CURRENT_DATE, end_date) <= 90 THEN 'Review Soon'
+ ELSE 'Active'
+ END AS status
+FROM contracts
+ORDER BY end_date;
+
+-- ============================================================================
+-- SECTION 9: WINDOW FUNCTION TESTS
+-- ============================================================================
+
+SELECT '=== WINDOW FUNCTION TESTS ===' AS section;
+
+-- Running tenure calculation
+SELECT
+ name,
+ hire_date,
+ department,
+ datediff('month', hire_date, CURRENT_DATE) AS months_tenure,
+ RANK() OVER (PARTITION BY department ORDER BY datediff('month', hire_date, CURRENT_DATE) DESC) AS dept_tenure_rank,
+ SUM(datediff('month', hire_date, CURRENT_DATE)) OVER (PARTITION BY department) AS dept_total_months
+FROM employees
+WHERE termination_date IS NULL
+ORDER BY department, months_tenure DESC;
+
+-- ============================================================================
+-- SECTION 10: JOIN AND SUBQUERY TESTS
+-- ============================================================================
+
+SELECT '=== JOIN AND SUBQUERY TESTS ===' AS section;
+
+-- Employees with tenure >= average
+SELECT
+ e.name,
+ e.hire_date,
+ datediff('year', e.hire_date, CURRENT_DATE) AS years,
+ avg_tenure.avg_years
+FROM employees e
+CROSS JOIN (
+ SELECT ROUND(AVG(datediff('year', hire_date, CURRENT_DATE)), 2) AS avg_years
+ FROM employees
+ WHERE termination_date IS NULL
+) avg_tenure
+WHERE termination_date IS NULL
+ AND datediff('year', e.hire_date, CURRENT_DATE) >= avg_tenure.avg_years
+ORDER BY years DESC;
+
+-- ============================================================================
+-- SECTION 11: NULL COLUMN HANDLING
+-- ============================================================================
+
+SELECT '=== NULL COLUMN HANDLING ===' AS section;
+
+-- datediff with NULL columns (should return NULL)
+SELECT
+ name,
+ hire_date,
+ termination_date,
+ datediff('day', hire_date, termination_date) AS tenure_days,
+ COALESCE(datediff('day', hire_date, termination_date)::text, 'Still Active') AS tenure_display
+FROM employees
+ORDER BY hire_date;
+
+-- ============================================================================
+-- SECTION 12: AGGREGATE FUNCTIONS WITH DATEDIFF
+-- ============================================================================
+
+SELECT '=== AGGREGATE FUNCTION TESTS ===' AS section;
+
+-- Statistics on subscription durations
+SELECT
+ billing_cycle,
+ COUNT(*) AS sub_count,
+ ROUND(MIN(datediff('month', start_date, COALESCE(end_date, CURRENT_DATE))), 2) AS min_months,
+ ROUND(MAX(datediff('month', start_date, COALESCE(end_date, CURRENT_DATE))), 2) AS max_months,
+ ROUND(AVG(datediff('month', start_date, COALESCE(end_date, CURRENT_DATE))), 2) AS avg_months,
+ ROUND(SUM(monthly_rate * datediff('month', start_date, COALESCE(end_date, CURRENT_DATE)))::numeric, 2) AS total_revenue
+FROM subscriptions
+GROUP BY billing_cycle
+ORDER BY avg_months DESC;
+
+-- ============================================================================
+-- SECTION 13: FILTER/WHERE CLAUSE TESTS
+-- ============================================================================
+
+SELECT '=== FILTER/WHERE CLAUSE TESTS ===' AS section;
+
+-- Find long-term employees (5+ years)
+SELECT name, hire_date, datediff('year', hire_date, CURRENT_DATE) AS years
+FROM employees
+WHERE termination_date IS NULL
+ AND datediff('year', hire_date, CURRENT_DATE) >= 5
+ORDER BY years DESC;
+
+-- Find overdue orders over 60 days
+SELECT id, due_date, amount, datediff('day', due_date, CURRENT_DATE) AS days_overdue
+FROM orders
+WHERE paid_date IS NULL
+ AND datediff('day', due_date, CURRENT_DATE) > 60
+ORDER BY days_overdue DESC;
+
+-- ============================================================================
+-- SECTION 14: GROUP BY WITH DATEDIFF EXPRESSIONS
+-- ============================================================================
+
+SELECT '=== GROUP BY DATEDIFF TESTS ===' AS section;
+
+-- Group employees by tenure brackets
+WITH employee_tenure AS (
+ SELECT
+ salary,
+ datediff('year', hire_date, CURRENT_DATE) AS years_tenure
+ FROM employees
+ WHERE termination_date IS NULL
+)
+SELECT
+ CASE
+ WHEN years_tenure >= 10 THEN '10+ years'
+ WHEN years_tenure >= 5 THEN '5-10 years'
+ WHEN years_tenure >= 2 THEN '2-5 years'
+ ELSE 'Under 2 years'
+ END AS tenure_bracket,
+ COUNT(*) AS employee_count,
+ ROUND(AVG(salary), 2) AS avg_salary
+FROM employee_tenure
+GROUP BY 1
+ORDER BY MIN(years_tenure) DESC;
+
+-- ============================================================================
+-- SECTION 15: TIMESTAMPTZ TIMEZONE EDGE CASES
+-- ============================================================================
+
+SELECT '=== TIMEZONE EDGE CASES ===' AS section;
+
+-- Same instant, different timezones (should give same result)
+SELECT
+ 'Same instant different TZ' AS test,
+ datediff('day',
+ '2024-06-15 00:00:00+00'::timestamptz,
+ '2024-06-20 00:00:00+00'::timestamptz) AS utc,
+ datediff('day',
+ '2024-06-15 00:00:00-08'::timestamptz,
+ '2024-06-20 00:00:00-08'::timestamptz) AS pst;
+
+-- DST transition dates (US)
+SELECT
+ 'DST transition' AS test,
+ datediff('day', '2024-03-09', '2024-03-11') AS across_spring_forward,
+ datediff('day', '2024-11-02', '2024-11-04') AS across_fall_back;
+
+-- ============================================================================
+-- SECTION 16: PERFORMANCE TEST WITH LARGER DATASET
+-- ============================================================================
+
+SELECT '=== PERFORMANCE TEST ===' AS section;
+
+-- Create a larger test table
+DROP TABLE IF EXISTS perf_test;
+CREATE TABLE perf_test AS
+SELECT
+ generate_series AS id,
+ '2020-01-01'::date + (random() * 1500)::int AS start_date,
+ '2020-01-01'::date + (random() * 1500)::int + (random() * 365)::int AS end_date
+FROM generate_series(1, 10000);
+
+-- Time a batch operation
+\timing on
+SELECT
+ COUNT(*) AS total_rows,
+ SUM(datediff('day', start_date, end_date)) AS total_days,
+ AVG(datediff('month', start_date, end_date)) AS avg_months
+FROM perf_test;
+\timing off
+
+-- Cleanup performance test table
+DROP TABLE IF EXISTS perf_test;
+
+-- ============================================================================
+-- CLEANUP
+-- ============================================================================
+
+SELECT '=== CLEANUP ===' AS section;
+
+DROP TABLE IF EXISTS employees CASCADE;
+DROP TABLE IF EXISTS subscriptions CASCADE;
+DROP TABLE IF EXISTS orders CASCADE;
+DROP TABLE IF EXISTS contracts CASCADE;
+
+SELECT 'Advanced edge case tests completed!' AS final_status;
+
diff --git a/contrib/pg_datemath/sql/datediff_comprehensive_tests.sql b/contrib/pg_datemath/sql/datediff_comprehensive_tests.sql
new file mode 100644
index 00000000000..0892b0a61a6
--- /dev/null
+++ b/contrib/pg_datemath/sql/datediff_comprehensive_tests.sql
@@ -0,0 +1,598 @@
+--
+-- Comprehensive DATEDIFF Function Tests
+-- 50+ tests covering all permutations and edge cases
+--
+
+-- Setup: Create extension
+DROP EXTENSION IF EXISTS pg_datemath CASCADE;
+CREATE EXTENSION pg_datemath;
+
+-- ============================================================================
+-- SECTION 1: Test Data Setup
+-- ============================================================================
+
+DROP TABLE IF EXISTS date_test_data;
+CREATE TABLE date_test_data (
+ id SERIAL PRIMARY KEY,
+ description TEXT,
+ start_date DATE,
+ end_date DATE,
+ start_ts TIMESTAMP,
+ end_ts TIMESTAMP,
+ start_tstz TIMESTAMPTZ,
+ end_tstz TIMESTAMPTZ
+);
+
+-- Insert comprehensive test data
+INSERT INTO date_test_data (description, start_date, end_date, start_ts, end_ts, start_tstz, end_tstz) VALUES
+-- Basic date ranges
+('Same day', '2024-06-15', '2024-06-15', '2024-06-15 10:00:00', '2024-06-15 18:00:00', '2024-06-15 10:00:00+00', '2024-06-15 18:00:00+00'),
+('One day apart', '2024-06-15', '2024-06-16', '2024-06-15 00:00:00', '2024-06-16 00:00:00', '2024-06-15 00:00:00+00', '2024-06-16 00:00:00+00'),
+('One week apart', '2024-06-01', '2024-06-08', '2024-06-01 12:00:00', '2024-06-08 12:00:00', '2024-06-01 12:00:00+00', '2024-06-08 12:00:00+00'),
+('One month apart (same day)', '2024-05-15', '2024-06-15', '2024-05-15 08:30:00', '2024-06-15 08:30:00', '2024-05-15 08:30:00+00', '2024-06-15 08:30:00+00'),
+('One quarter apart', '2024-01-01', '2024-04-01', '2024-01-01 00:00:00', '2024-04-01 00:00:00', '2024-01-01 00:00:00+00', '2024-04-01 00:00:00+00'),
+('One year apart', '2023-06-15', '2024-06-15', '2023-06-15 15:45:00', '2024-06-15 15:45:00', '2023-06-15 15:45:00+00', '2024-06-15 15:45:00+00'),
+
+-- Leap year scenarios
+('Leap year Feb 28 to Mar 1', '2024-02-28', '2024-03-01', '2024-02-28 00:00:00', '2024-03-01 00:00:00', '2024-02-28 00:00:00+00', '2024-03-01 00:00:00+00'),
+('Leap year Feb 29 exists', '2024-02-29', '2024-03-01', '2024-02-29 00:00:00', '2024-03-01 00:00:00', '2024-02-29 00:00:00+00', '2024-03-01 00:00:00+00'),
+('Non-leap year Feb 28 to Mar 1', '2023-02-28', '2023-03-01', '2023-02-28 00:00:00', '2023-03-01 00:00:00', '2023-02-28 00:00:00+00', '2023-03-01 00:00:00+00'),
+('Leap year full year', '2024-01-01', '2025-01-01', '2024-01-01 00:00:00', '2025-01-01 00:00:00', '2024-01-01 00:00:00+00', '2025-01-01 00:00:00+00'),
+
+-- End of month scenarios
+('Jan 31 to Feb 28 (non-leap)', '2023-01-31', '2023-02-28', '2023-01-31 00:00:00', '2023-02-28 00:00:00', '2023-01-31 00:00:00+00', '2023-02-28 00:00:00+00'),
+('Jan 31 to Feb 29 (leap)', '2024-01-31', '2024-02-29', '2024-01-31 00:00:00', '2024-02-29 00:00:00', '2024-01-31 00:00:00+00', '2024-02-29 00:00:00+00'),
+('Mar 31 to Apr 30', '2024-03-31', '2024-04-30', '2024-03-31 00:00:00', '2024-04-30 00:00:00', '2024-03-31 00:00:00+00', '2024-04-30 00:00:00+00'),
+('Month end to month end chain', '2024-01-31', '2024-05-31', '2024-01-31 00:00:00', '2024-05-31 00:00:00', '2024-01-31 00:00:00+00', '2024-05-31 00:00:00+00'),
+
+-- Negative spans (start > end)
+('Negative: 2 weeks back', '2024-06-22', '2024-06-08', '2024-06-22 00:00:00', '2024-06-08 00:00:00', '2024-06-22 00:00:00+00', '2024-06-08 00:00:00+00'),
+('Negative: 3 months back', '2024-09-15', '2024-06-15', '2024-09-15 00:00:00', '2024-06-15 00:00:00', '2024-09-15 00:00:00+00', '2024-06-15 00:00:00+00'),
+('Negative: 2 years back', '2026-01-01', '2024-01-01', '2026-01-01 00:00:00', '2024-01-01 00:00:00', '2026-01-01 00:00:00+00', '2024-01-01 00:00:00+00'),
+
+-- Year boundary crossings
+('Cross year boundary', '2024-12-31', '2025-01-01', '2024-12-31 23:59:59', '2025-01-01 00:00:01', '2024-12-31 23:59:59+00', '2025-01-01 00:00:01+00'),
+('Cross multiple years', '2020-06-15', '2024-06-15', '2020-06-15 00:00:00', '2024-06-15 00:00:00', '2020-06-15 00:00:00+00', '2024-06-15 00:00:00+00'),
+('Century boundary', '1999-12-31', '2000-01-01', '1999-12-31 00:00:00', '2000-01-01 00:00:00', '1999-12-31 00:00:00+00', '2000-01-01 00:00:00+00'),
+
+-- Partial periods
+('Partial month mid-month', '2024-01-15', '2024-02-20', '2024-01-15 00:00:00', '2024-02-20 00:00:00', '2024-01-15 00:00:00+00', '2024-02-20 00:00:00+00'),
+('Partial quarter', '2024-01-15', '2024-05-20', '2024-01-15 00:00:00', '2024-05-20 00:00:00', '2024-01-15 00:00:00+00', '2024-05-20 00:00:00+00'),
+('Partial year', '2024-03-15', '2025-06-20', '2024-03-15 00:00:00', '2025-06-20 00:00:00', '2024-03-15 00:00:00+00', '2025-06-20 00:00:00+00'),
+
+-- Employee tenure scenarios
+('Employee 90-day probation', '2024-01-15', '2024-04-14', '2024-01-15 09:00:00', '2024-04-14 17:00:00', '2024-01-15 09:00:00+00', '2024-04-14 17:00:00+00'),
+('Employee 5-year anniversary', '2019-03-01', '2024-03-01', '2019-03-01 00:00:00', '2024-03-01 00:00:00', '2019-03-01 00:00:00+00', '2024-03-01 00:00:00+00'),
+('Employee 10-year tenure', '2014-06-15', '2024-06-15', '2014-06-15 08:00:00', '2024-06-15 08:00:00', '2014-06-15 08:00:00+00', '2024-06-15 08:00:00+00'),
+
+-- Billing/subscription scenarios
+('Monthly subscription', '2024-01-01', '2024-01-31', '2024-01-01 00:00:00', '2024-01-31 23:59:59', '2024-01-01 00:00:00+00', '2024-01-31 23:59:59+00'),
+('Quarterly billing', '2024-01-01', '2024-03-31', '2024-01-01 00:00:00', '2024-03-31 00:00:00', '2024-01-01 00:00:00+00', '2024-03-31 00:00:00+00'),
+('Annual subscription', '2023-07-15', '2024-07-15', '2023-07-15 00:00:00', '2024-07-15 00:00:00', '2023-07-15 00:00:00+00', '2024-07-15 00:00:00+00'),
+('Prorated mid-month cancel', '2024-03-01', '2024-03-18', '2024-03-01 00:00:00', '2024-03-18 00:00:00', '2024-03-01 00:00:00+00', '2024-03-18 00:00:00+00'),
+
+-- Large spans
+('Decade span', '2010-01-01', '2020-01-01', '2010-01-01 00:00:00', '2020-01-01 00:00:00', '2010-01-01 00:00:00+00', '2020-01-01 00:00:00+00'),
+('25 years span', '1999-06-15', '2024-06-15', '1999-06-15 00:00:00', '2024-06-15 00:00:00', '1999-06-15 00:00:00+00', '2024-06-15 00:00:00+00');
+
+-- ============================================================================
+-- SECTION 2: DAY Datepart Tests (Tests 1-8)
+-- ============================================================================
+
+SELECT '=== DAY DATEPART TESTS ===' AS section;
+
+-- Basic day difference
+SELECT 'Basic day difference' AS test_name,
+ datediff('day', '2024-01-01', '2024-01-15') AS result,
+ 14 AS expected,
+ CASE WHEN datediff('day', '2024-01-01', '2024-01-15') = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Day difference using 'dd' alias
+SELECT 'Day alias dd' AS test_name,
+ datediff('dd', '2024-01-01', '2024-01-15') AS result,
+ 14 AS expected,
+ CASE WHEN datediff('dd', '2024-01-01', '2024-01-15') = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Day difference using 'd' alias
+SELECT 'Day alias d' AS test_name,
+ datediff('d', '2024-03-01', '2024-03-31') AS result,
+ 30 AS expected,
+ CASE WHEN datediff('d', '2024-03-01', '2024-03-31') = 30 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Day difference using 'days' alias
+SELECT 'Day alias days' AS test_name,
+ datediff('days', '2024-06-01', '2024-06-30') AS result,
+ 29 AS expected,
+ CASE WHEN datediff('days', '2024-06-01', '2024-06-30') = 29 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Negative day difference
+SELECT 'Negative day difference' AS test_name,
+ datediff('day', '2024-01-15', '2024-01-01') AS result,
+ -14 AS expected,
+ CASE WHEN datediff('day', '2024-01-15', '2024-01-01') = -14 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Same day returns 0
+SELECT 'Same day returns 0' AS test_name,
+ datediff('day', '2024-06-15', '2024-06-15') AS result,
+ 0 AS expected,
+ CASE WHEN datediff('day', '2024-06-15', '2024-06-15') = 0 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Leap year February (28th to Mar 1st)
+SELECT 'Leap year Feb 28 to Mar 1' AS test_name,
+ datediff('day', '2024-02-28', '2024-03-01') AS result,
+ 2 AS expected,
+ CASE WHEN datediff('day', '2024-02-28', '2024-03-01') = 2 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Non-leap year February
+SELECT 'Non-leap year Feb 28 to Mar 1' AS test_name,
+ datediff('day', '2023-02-28', '2023-03-01') AS result,
+ 1 AS expected,
+ CASE WHEN datediff('day', '2023-02-28', '2023-03-01') = 1 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 3: WEEK Datepart Tests (Tests 9-15)
+-- ============================================================================
+
+SELECT '=== WEEK DATEPART TESTS ===' AS section;
+
+-- Exact 1 week
+SELECT 'Exact 1 week' AS test_name,
+ datediff('week', '2024-01-01', '2024-01-08') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('week', '2024-01-01', '2024-01-08') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Exact 2 weeks
+SELECT 'Exact 2 weeks' AS test_name,
+ datediff('week', '2024-01-01', '2024-01-15') AS result,
+ 2.000 AS expected,
+ CASE WHEN datediff('week', '2024-01-01', '2024-01-15') = 2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Partial week (9 days = 1.286 weeks)
+SELECT 'Partial week 9 days' AS test_name,
+ datediff('week', '2024-01-01', '2024-01-10') AS result,
+ 1.286 AS expected,
+ CASE WHEN datediff('week', '2024-01-01', '2024-01-10') = 1.286 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Week alias 'wk'
+SELECT 'Week alias wk' AS test_name,
+ datediff('wk', '2024-01-01', '2024-01-08') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('wk', '2024-01-01', '2024-01-08') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Week alias 'ww'
+SELECT 'Week alias ww' AS test_name,
+ datediff('ww', '2024-01-01', '2024-01-22') AS result,
+ 3.000 AS expected,
+ CASE WHEN datediff('ww', '2024-01-01', '2024-01-22') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Week alias 'weeks'
+SELECT 'Week alias weeks' AS test_name,
+ datediff('weeks', '2024-02-01', '2024-02-29') AS result,
+ 4.000 AS expected,
+ CASE WHEN datediff('weeks', '2024-02-01', '2024-02-29') = 4.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Negative weeks
+SELECT 'Negative weeks' AS test_name,
+ datediff('week', '2024-01-15', '2024-01-01') AS result,
+ -2.000 AS expected,
+ CASE WHEN datediff('week', '2024-01-15', '2024-01-01') = -2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 4: MONTH Datepart Tests (Tests 16-25)
+-- ============================================================================
+
+SELECT '=== MONTH DATEPART TESTS ===' AS section;
+
+-- Aligned month (same day-of-month)
+SELECT 'Aligned month same day' AS test_name,
+ datediff('month', '2024-01-15', '2024-02-15') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('month', '2024-01-15', '2024-02-15') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Partial month
+SELECT 'Partial month' AS test_name,
+ datediff('month', '2024-01-15', '2024-02-20') AS result,
+ 1.172 AS expected,
+ CASE WHEN datediff('month', '2024-01-15', '2024-02-20') = 1.172 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- End-of-month alignment (Jan 31 -> Feb 29)
+SELECT 'End-of-month alignment' AS test_name,
+ datediff('month', '2024-01-31', '2024-02-29') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('month', '2024-01-31', '2024-02-29') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Month alias 'mm'
+SELECT 'Month alias mm' AS test_name,
+ datediff('mm', '2024-01-01', '2024-02-01') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('mm', '2024-01-01', '2024-02-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Month alias 'm'
+SELECT 'Month alias m' AS test_name,
+ datediff('m', '2024-03-15', '2024-06-15') AS result,
+ 3.000 AS expected,
+ CASE WHEN datediff('m', '2024-03-15', '2024-06-15') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Month alias 'months'
+SELECT 'Month alias months' AS test_name,
+ datediff('months', '2024-01-01', '2024-07-01') AS result,
+ 6.000 AS expected,
+ CASE WHEN datediff('months', '2024-01-01', '2024-07-01') = 6.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Multiple months with partial
+SELECT 'Multiple months partial' AS test_name,
+ datediff('month', '2024-01-25', '2024-03-10') AS result,
+ 1.483 AS expected,
+ CASE WHEN datediff('month', '2024-01-25', '2024-03-10') = 1.483 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Negative months
+SELECT 'Negative months' AS test_name,
+ datediff('month', '2024-06-15', '2024-03-15') AS result,
+ -3.000 AS expected,
+ CASE WHEN datediff('month', '2024-06-15', '2024-03-15') = -3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Month spanning year boundary
+SELECT 'Month across year boundary' AS test_name,
+ datediff('month', '2024-11-15', '2025-02-15') AS result,
+ 3.000 AS expected,
+ CASE WHEN datediff('month', '2024-11-15', '2025-02-15') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Less than one month
+SELECT 'Less than one month' AS test_name,
+ datediff('month', '2024-01-01', '2024-01-15') AS result,
+ 0.452 AS expected, -- 14 days / 31 days in January
+ CASE WHEN datediff('month', '2024-01-01', '2024-01-15') BETWEEN 0.450 AND 0.460 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 5: QUARTER Datepart Tests (Tests 26-33)
+-- ============================================================================
+
+SELECT '=== QUARTER DATEPART TESTS ===' AS section;
+
+-- Exact quarter aligned
+SELECT 'Exact quarter aligned' AS test_name,
+ datediff('quarter', '2024-01-01', '2024-04-01') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('quarter', '2024-01-01', '2024-04-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Partial quarter
+SELECT 'Partial quarter' AS test_name,
+ datediff('quarter', '2024-01-15', '2024-05-20') AS result,
+ 1.385 AS expected,
+ CASE WHEN datediff('quarter', '2024-01-15', '2024-05-20') = 1.385 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Quarter alias 'qq'
+SELECT 'Quarter alias qq' AS test_name,
+ datediff('qq', '2024-01-01', '2024-07-01') AS result,
+ 2.000 AS expected,
+ CASE WHEN datediff('qq', '2024-01-01', '2024-07-01') = 2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Quarter alias 'q'
+SELECT 'Quarter alias q' AS test_name,
+ datediff('q', '2024-01-01', '2024-10-01') AS result,
+ 3.000 AS expected,
+ CASE WHEN datediff('q', '2024-01-01', '2024-10-01') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Quarter alias 'quarters'
+SELECT 'Quarter alias quarters' AS test_name,
+ datediff('quarters', '2024-01-01', '2025-01-01') AS result,
+ 4.000 AS expected,
+ CASE WHEN datediff('quarters', '2024-01-01', '2025-01-01') = 4.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Negative quarters
+SELECT 'Negative quarters' AS test_name,
+ datediff('quarter', '2024-10-01', '2024-04-01') AS result,
+ -2.000 AS expected,
+ CASE WHEN datediff('quarter', '2024-10-01', '2024-04-01') = -2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Less than one quarter
+SELECT 'Less than one quarter' AS test_name,
+ datediff('quarter', '2024-01-01', '2024-02-15') AS result,
+ 0.495 AS expected, -- ~45 days / 91 days
+ CASE WHEN datediff('quarter', '2024-01-01', '2024-02-15') BETWEEN 0.490 AND 0.500 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Quarter across year boundary
+SELECT 'Quarter across year boundary' AS test_name,
+ datediff('quarter', '2024-10-01', '2025-04-01') AS result,
+ 2.000 AS expected,
+ CASE WHEN datediff('quarter', '2024-10-01', '2025-04-01') = 2.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 6: YEAR Datepart Tests (Tests 34-42)
+-- ============================================================================
+
+SELECT '=== YEAR DATEPART TESTS ===' AS section;
+
+-- Exact year aligned
+SELECT 'Exact year aligned' AS test_name,
+ datediff('year', '2024-03-15', '2025-03-15') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('year', '2024-03-15', '2025-03-15') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Partial year in leap year
+SELECT 'Partial year leap year' AS test_name,
+ datediff('year', '2024-01-01', '2024-07-01') AS result,
+ 0.497 AS expected, -- 182 days / 366
+ CASE WHEN datediff('year', '2024-01-01', '2024-07-01') BETWEEN 0.495 AND 0.500 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Year alias 'yy'
+SELECT 'Year alias yy' AS test_name,
+ datediff('yy', '2020-01-01', '2025-01-01') AS result,
+ 5.000 AS expected,
+ CASE WHEN datediff('yy', '2020-01-01', '2025-01-01') = 5.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Year alias 'yyyy'
+SELECT 'Year alias yyyy' AS test_name,
+ datediff('yyyy', '2024-06-15', '2027-06-15') AS result,
+ 3.000 AS expected,
+ CASE WHEN datediff('yyyy', '2024-06-15', '2027-06-15') = 3.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Year alias 'y'
+SELECT 'Year alias y' AS test_name,
+ datediff('y', '2024-01-01', '2024-12-31') AS result,
+ 0.997 AS expected, -- 365 days / 366
+ CASE WHEN datediff('y', '2024-01-01', '2024-12-31') BETWEEN 0.995 AND 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Year alias 'years'
+SELECT 'Year alias years' AS test_name,
+ datediff('years', '2014-06-15', '2024-06-15') AS result,
+ 10.000 AS expected,
+ CASE WHEN datediff('years', '2014-06-15', '2024-06-15') = 10.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Year boundary crossing (1 day)
+SELECT 'Year boundary 1 day' AS test_name,
+ datediff('year', '2024-12-31', '2025-01-01') AS result,
+ 0.003 AS expected, -- 1 day / 365
+ CASE WHEN datediff('year', '2024-12-31', '2025-01-01') BETWEEN 0.001 AND 0.005 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Negative years
+SELECT 'Negative years' AS test_name,
+ datediff('year', '2025-06-15', '2020-06-15') AS result,
+ -5.000 AS expected,
+ CASE WHEN datediff('year', '2025-06-15', '2020-06-15') = -5.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Feb 29 leap year to Feb 28 non-leap (aligned)
+SELECT 'Feb 29 to Feb 28 alignment' AS test_name,
+ datediff('year', '2024-02-29', '2025-02-28') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('year', '2024-02-29', '2025-02-28') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 7: Case Insensitivity Tests (Tests 43-45)
+-- ============================================================================
+
+SELECT '=== CASE INSENSITIVITY TESTS ===' AS section;
+
+-- UPPERCASE datepart
+SELECT 'UPPERCASE MONTH' AS test_name,
+ datediff('MONTH', '2024-01-01', '2024-02-01') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('MONTH', '2024-01-01', '2024-02-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Mixed case datepart
+SELECT 'Mixed case Quarter' AS test_name,
+ datediff('QuArTeR', '2024-01-01', '2024-04-01') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('QuArTeR', '2024-01-01', '2024-04-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Mixed case alias
+SELECT 'Mixed case alias YY' AS test_name,
+ datediff('Yy', '2024-01-01', '2025-01-01') AS result,
+ 1.000 AS expected,
+ CASE WHEN datediff('Yy', '2024-01-01', '2025-01-01') = 1.000 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 8: TIMESTAMP and TIMESTAMPTZ Tests (Tests 46-48)
+-- ============================================================================
+
+SELECT '=== TIMESTAMP TESTS ===' AS section;
+
+-- Timestamp day difference
+SELECT 'Timestamp day diff' AS test_name,
+ datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp) AS result,
+ 14 AS expected,
+ CASE WHEN datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp) = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Timestamp month difference
+SELECT 'Timestamp month diff' AS test_name,
+ datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp) AS result,
+ 1.172 AS expected,
+ CASE WHEN datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp) = 1.172 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- Timestamptz day difference
+SELECT 'Timestamptz day diff' AS test_name,
+ datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz) AS result,
+ 14 AS expected,
+ CASE WHEN datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz) = 14 THEN 'PASS' ELSE 'FAIL' END AS status;
+
+-- ============================================================================
+-- SECTION 9: Error Handling Tests (Tests 49-50)
+-- ============================================================================
+
+SELECT '=== ERROR HANDLING TESTS ===' AS section;
+
+-- Invalid datepart should error
+SELECT 'Invalid datepart error' AS test_name;
+DO $$
+BEGIN
+ PERFORM datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
+ RAISE NOTICE 'FAIL: No error raised for invalid datepart';
+EXCEPTION WHEN invalid_parameter_value THEN
+ RAISE NOTICE 'PASS: Correctly raised error for invalid datepart';
+END $$;
+
+-- NULL handling (should return NULL)
+SELECT 'NULL handling' AS test_name,
+ datediff('day', NULL::date, '2024-01-15'::date) IS NULL AS null_start_returns_null,
+ datediff('day', '2024-01-01'::date, NULL::date) IS NULL AS null_end_returns_null,
+ CASE
+ WHEN datediff('day', NULL::date, '2024-01-15'::date) IS NULL
+ AND datediff('day', '2024-01-01'::date, NULL::date) IS NULL
+ THEN 'PASS'
+ ELSE 'FAIL'
+ END AS status;
+
+-- ============================================================================
+-- SECTION 10: Table-Based Tests
+-- ============================================================================
+
+SELECT '=== TABLE-BASED TESTS ===' AS section;
+
+-- Test all dateparts against table data
+SELECT
+ description,
+ datediff('day', start_date, end_date) AS days,
+ datediff('week', start_date, end_date) AS weeks,
+ datediff('month', start_date, end_date) AS months,
+ datediff('quarter', start_date, end_date) AS quarters,
+ datediff('year', start_date, end_date) AS years
+FROM date_test_data
+ORDER BY id;
+
+-- Test with timestamp columns
+SELECT
+ description,
+ datediff('day', start_ts, end_ts) AS ts_days,
+ datediff('month', start_ts, end_ts) AS ts_months
+FROM date_test_data
+WHERE start_ts IS NOT NULL
+ORDER BY id
+LIMIT 10;
+
+-- Test with timestamptz columns
+SELECT
+ description,
+ datediff('day', start_tstz, end_tstz) AS tstz_days,
+ datediff('year', start_tstz, end_tstz) AS tstz_years
+FROM date_test_data
+WHERE start_tstz IS NOT NULL
+ORDER BY id
+LIMIT 10;
+
+-- ============================================================================
+-- SECTION 11: Aggregation and Analytics Tests
+-- ============================================================================
+
+SELECT '=== AGGREGATION TESTS ===' AS section;
+
+-- Average tenure calculations
+SELECT
+ 'Average differences across test data' AS metric,
+ ROUND(AVG(datediff('day', start_date, end_date)), 2) AS avg_days,
+ ROUND(AVG(datediff('month', start_date, end_date)), 2) AS avg_months,
+ ROUND(AVG(datediff('year', start_date, end_date)), 2) AS avg_years
+FROM date_test_data
+WHERE start_date <= end_date;
+
+-- Group by ranges
+WITH date_diffs AS (
+ SELECT
+ id,
+ description,
+ datediff('day', start_date, end_date) AS day_diff
+ FROM date_test_data
+ WHERE start_date <= end_date
+)
+SELECT
+ CASE
+ WHEN day_diff < 7 THEN 'Less than 1 week'
+ WHEN day_diff < 30 THEN '1 week to 1 month'
+ WHEN day_diff < 90 THEN '1 to 3 months'
+ WHEN day_diff < 365 THEN '3 months to 1 year'
+ ELSE 'Over 1 year'
+ END AS duration_bucket,
+ COUNT(*) AS count
+FROM date_diffs
+GROUP BY 1
+ORDER BY MIN(day_diff);
+
+-- ============================================================================
+-- SECTION 12: Real-World Scenario Tests
+-- ============================================================================
+
+SELECT '=== REAL-WORLD SCENARIO TESTS ===' AS section;
+
+-- Invoice aging report simulation
+WITH invoices AS (
+ SELECT
+ generate_series(1, 10) AS invoice_id,
+ '2024-01-01'::date + (random() * 180)::int AS due_date
+)
+SELECT
+ invoice_id,
+ due_date,
+ CURRENT_DATE AS today,
+ datediff('day', due_date, CURRENT_DATE) AS days_overdue,
+ CASE
+ WHEN datediff('day', due_date, CURRENT_DATE) > 90 THEN 'Critical'
+ WHEN datediff('day', due_date, CURRENT_DATE) > 60 THEN 'Warning'
+ WHEN datediff('day', due_date, CURRENT_DATE) > 30 THEN 'Attention'
+ WHEN datediff('day', due_date, CURRENT_DATE) > 0 THEN 'Overdue'
+ ELSE 'Current'
+ END AS aging_bucket
+FROM invoices
+ORDER BY days_overdue DESC;
+
+-- Subscription proration simulation
+WITH subscriptions AS (
+ SELECT
+ 1 AS sub_id, '2024-01-15'::date AS start_date, '2024-02-20'::date AS cancel_date, 29.99 AS monthly_rate
+ UNION ALL SELECT
+ 2, '2024-03-01', '2024-03-18', 49.99
+ UNION ALL SELECT
+ 3, '2024-06-15', '2024-09-15', 99.99
+)
+SELECT
+ sub_id,
+ start_date,
+ cancel_date,
+ monthly_rate,
+ datediff('month', start_date, cancel_date) AS months_used,
+ ROUND((datediff('month', start_date, cancel_date) * monthly_rate)::numeric, 2) AS prorated_charge
+FROM subscriptions;
+
+-- Employee tenure report
+WITH employees AS (
+ SELECT 'Alice' AS name, '2019-03-15'::date AS hire_date
+ UNION ALL SELECT 'Bob', '2021-06-01'
+ UNION ALL SELECT 'Carol', '2023-01-10'
+ UNION ALL SELECT 'David', '2024-06-15'
+)
+SELECT
+ name,
+ hire_date,
+ datediff('year', hire_date, CURRENT_DATE) AS years_tenure,
+ datediff('month', hire_date, CURRENT_DATE) AS months_tenure,
+ datediff('day', hire_date, CURRENT_DATE) AS days_tenure,
+ CASE
+ WHEN datediff('year', hire_date, CURRENT_DATE) >= 5 THEN 'Senior'
+ WHEN datediff('year', hire_date, CURRENT_DATE) >= 2 THEN 'Mid-level'
+ WHEN datediff('year', hire_date, CURRENT_DATE) >= 1 THEN 'Junior'
+ ELSE 'Probation'
+ END AS tenure_level
+FROM employees
+ORDER BY hire_date;
+
+-- ============================================================================
+-- SUMMARY: Test Results
+-- ============================================================================
+
+SELECT '=== TEST SUMMARY ===' AS section;
+
+-- Count passing tests from table data
+SELECT
+ 'Table data validation' AS category,
+ COUNT(*) FILTER (WHERE datediff('day', start_date, end_date) IS NOT NULL) AS day_tests,
+ COUNT(*) FILTER (WHERE datediff('week', start_date, end_date) IS NOT NULL) AS week_tests,
+ COUNT(*) FILTER (WHERE datediff('month', start_date, end_date) IS NOT NULL) AS month_tests,
+ COUNT(*) FILTER (WHERE datediff('quarter', start_date, end_date) IS NOT NULL) AS quarter_tests,
+ COUNT(*) FILTER (WHERE datediff('year', start_date, end_date) IS NOT NULL) AS year_tests
+FROM date_test_data;
+
+-- Cleanup
+DROP TABLE IF EXISTS date_test_data;
+-- Note: Keeping extension for further manual testing
+-- DROP EXTENSION IF EXISTS pg_datemath;
+
+SELECT 'All comprehensive tests completed!' AS final_status;
+
diff --git a/contrib/pg_datemath/sql/pg_datemath.sql b/contrib/pg_datemath/sql/pg_datemath.sql
new file mode 100644
index 00000000000..1a370ad61f2
--- /dev/null
+++ b/contrib/pg_datemath/sql/pg_datemath.sql
@@ -0,0 +1,112 @@
+--
+-- Test cases for pg_datemath extension
+-- Tests datediff function with various dateparts and edge cases
+--
+
+CREATE EXTENSION pg_datemath;
+
+--
+-- Basic Day Calculations
+--
+SELECT datediff('day', '2024-01-01'::date, '2024-01-15'::date);
+SELECT datediff('day', '2024-01-15'::date, '2024-01-01'::date);
+
+--
+-- Week Calculations
+--
+SELECT datediff('week', '2024-01-01'::date, '2024-01-08'::date);
+SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
+
+--
+-- Month Calculations
+--
+SELECT datediff('month', '2024-01-15'::date, '2024-02-15'::date);
+SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
+SELECT datediff('month', '2024-01-31'::date, '2024-02-29'::date);
+
+--
+-- Quarter Calculations
+--
+SELECT datediff('quarter', '2024-01-01'::date, '2024-04-01'::date);
+SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
+
+--
+-- Year Calculations
+--
+SELECT datediff('year', '2024-03-15'::date, '2025-03-15'::date);
+SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
+
+--
+-- NULL Handling - STRICT functions return NULL for NULL inputs
+--
+SELECT datediff('day', NULL::date, '2024-01-15'::date);
+SELECT datediff('day', '2024-01-01'::date, NULL::date);
+
+--
+-- Invalid Datepart
+--
+SELECT datediff('hour', '2024-01-01'::date, '2024-01-02'::date);
+
+--
+-- Case Insensitivity
+--
+SELECT datediff('MONTH', '2024-01-01'::date, '2024-02-01'::date);
+SELECT datediff('Month', '2024-01-01'::date, '2024-02-01'::date);
+SELECT datediff('month', '2024-01-01'::date, '2024-02-01'::date);
+
+--
+-- Edge Cases
+--
+SELECT datediff('day', '2024-01-01'::date, '2024-01-01'::date);
+SELECT datediff('day', '2024-02-28'::date, '2024-03-01'::date);
+SELECT datediff('day', '2023-02-28'::date, '2023-03-01'::date);
+SELECT datediff('year', '2024-12-31'::date, '2025-01-01'::date);
+SELECT datediff('year', '2020-01-01'::date, '2025-01-01'::date);
+SELECT datediff('day', '1999-12-31'::date, '2000-01-01'::date);
+
+--
+-- Alias Tests
+--
+SELECT datediff('yy', '2024-01-01'::date, '2025-01-01'::date);
+SELECT datediff('yyyy', '2024-01-01'::date, '2025-01-01'::date);
+SELECT datediff('mm', '2024-01-15'::date, '2024-02-15'::date);
+SELECT datediff('qq', '2024-01-01'::date, '2024-04-01'::date);
+SELECT datediff('wk', '2024-01-01'::date, '2024-01-08'::date);
+SELECT datediff('dd', '2024-01-01'::date, '2024-01-15'::date);
+
+--
+-- Timestamp Tests
+--
+SELECT datediff('day', '2024-01-01 10:30:00'::timestamp, '2024-01-15 14:45:00'::timestamp);
+SELECT datediff('month', '2024-01-15 08:00:00'::timestamp, '2024-02-20 16:00:00'::timestamp);
+
+--
+-- Timestamptz Tests
+--
+SELECT datediff('day', '2024-01-01 10:30:00+00'::timestamptz, '2024-01-15 14:45:00+00'::timestamptz);
+
+--
+-- Additional Month Calculation Tests
+--
+SELECT datediff('month', '2024-01-25'::date, '2024-03-10'::date);
+SELECT datediff('month', '2024-01-15'::date, '2024-02-20'::date);
+
+--
+-- Additional Quarter Calculation Tests
+--
+SELECT datediff('quarter', '2024-01-15'::date, '2024-05-20'::date);
+
+--
+-- Additional Year Calculation Tests
+--
+SELECT datediff('year', '2024-03-15'::date, '2025-06-20'::date);
+SELECT datediff('year', '2020-03-15'::date, '2025-03-15'::date);
+SELECT datediff('year', '2024-01-01'::date, '2024-07-01'::date);
+
+--
+-- Week Calculation Additional Tests
+--
+SELECT datediff('week', '2024-01-01'::date, '2024-01-15'::date);
+SELECT datediff('week', '2024-01-01'::date, '2024-01-10'::date);
+
+DROP EXTENSION pg_datemath;
--
2.52.0