Re: [PATCH] contrib: Add pg_datemath extension with datediff function - Mailing list pgsql-hackers

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

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