Thread

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

Pavel Stehule <pavel.stehule@gmail.com> — 2025-11-27T03:18:40Z

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