News for hackers. Stuff that matters.
Re:PostgreSQL's Missing DateDiff Function by wongarsu on Tuesday December 21, 2021 @06:16PM (#29643310)
Yes, but that's not what datediff does. The article doesn't really make it clear, but Microsoft explains Datediff as "This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate." That's very different from "the difference between two date" that is what the subtract operator calculates
Re:PostgreSQL's Missing DateDiff Function by mrkurt on Tuesday December 21, 2021 @06:29PM (#29643413)
Oh I see! What a weird function. I've used datediff in mysql and it's ... different: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functi...

So this is correct for years, right?

    > select floor(extract(epoch from date_trunc('year', '2021-01-01'::timestamptz) - date_trunc('year', '2020-12-31'::timestamptz)) / 3600 / 24 / 365) as diff;
    1
Re:PostgreSQL's Missing DateDiff Function by jeroenhd on Wednesday December 22, 2021 @06:48AM (#29648069)
Years aren't 365 days long, though. Technically, there are about 365.24 days in a year, but human dating doesn't take that into account well. The 3600 * 24 * 365 approach is tricky because there's a 1/4 chance that it'll work for you when you test it...

I don't think there's a good, mathematical expression for determining something as arbitrary as dates. If there was, I don't think MS would implement a special function like DateDiff for it.

Re:PostgreSQL's Missing DateDiff Function by funcDropShadow on Wednesday December 22, 2021 @08:40AM (#29649120)
So, it is this:

> # SELECT date_part('year', '2021-01-01'::timestamptz) - date_part('year', '2020-12-31'::timestamptz) as years;

Or what am I missing?

[edit: formatting]

Re:PostgreSQL's Missing DateDiff Function by castorp on Wednesday December 22, 2021 @02:12AM (#29646362)
The question is: what do you do with the result?

If you e.g. want to check if two timestamps are more than a year apart you just compare the difference to an interval of 1 year.

Re:PostgreSQL's Missing DateDiff Function by wavesquid on Tuesday December 21, 2021 @09:02PM (#29644728)
So isn't that `age ( timestamp, timestamp ) → interval`?
Re:PostgreSQL's Missing DateDiff Function by ryzvonusef on Wednesday December 22, 2021 @06:13AM (#29647841)
isn't that what the AGE function do?