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

> On Nov 25, 2025, at 10:29 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
> Hi
>
> út 25. 11. 2025 v 21:15 odesílatel Peter Eisentraut <peter(at)eisentraut(dot)org <mailto:peter(at)eisentraut(dot)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