Re: [PATCH] contrib: Add pg_datemath extension with datediff function

Hi

st 26. 11. 2025 v 21:26 odesílatel Myles Lewis <myles93(at)sbcglobal(dot)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(dot)stehule(at)gmail(dot)com>
> wrote:
>
> Hi
>
> út 25. 11. 2025 v 21:15 odesílatel Peter Eisentraut <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
>
>
>