[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.

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

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 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 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