Обсуждение: [PATCH] Add mssql_compat extension with DATEDIFF function
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
Вложения
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.
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
Вложения
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
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
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.RegardsPavel
Вложения
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:
- 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.
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!MylesOn 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.RegardsPavel
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
On Nov 26, 2025, at 9:18 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:Hist 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:
- 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.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!MylesOn 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.RegardsPavel