News for hackers. Stuff that matters.
Re:PostgreSQL's Missing DateDiff Function by Izkata on Tuesday December 21, 2021 @05:42PM (#29643008)
Isn't this what EXTRACT does? (Note the first paragraph that says it works on "interval" types)

https://www.postgresql.org/docs/9.3/functions-datetime.html#...

Just did a search and got it from here, which shows it being used on the difference between two dates: https://stackoverflow.com/questions/24929735/how-to-calculat...

Re:PostgreSQL's Missing DateDiff Function by cedricd on Tuesday December 21, 2021 @05:48PM (#29643047)
Those postgres docs are usually so dry but this is great

> The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time

Re:PostgreSQL's Missing DateDiff Function by reidjs on Tuesday December 21, 2021 @07:41PM (#29644043)
Haha it even keeps going

> This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 century to 1 century. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican.

Re:PostgreSQL's Missing DateDiff Function by qw on Wednesday December 22, 2021 @06:09AM (#29647813)
Date and time handling is one of the areas that is surprisingly difficult, for something that appears "simple". It tends to cause a lot of frustrations, and I can see this person struggling with all the different use cases and wants the readers to know it's not their fault. Otherwise it could generate questions in support forums.
Re:PostgreSQL's Missing DateDiff Function by cldellow on Tuesday December 21, 2021 @05:46PM (#29643034)
No, for example, the datediff in years for New Year's Eve and New Year's Day should be 1 (because it spans a year boundary), but EXTRACT on the difference would give you 0:

    select extract(year from '2021-01-01'::timestamptz - '2020-12-31'::timestamptz);
Re:PostgreSQL's Missing DateDiff Function by mrkurt on Tuesday December 21, 2021 @06:01PM (#29643165)
Am I missing something here, or is the subtraction operator exactly right?

    > select ('2021-01-01'::timestamptz - '2020-12-31'::timestamptz) as diff;
    1 day
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?
Re:PostgreSQL's Missing DateDiff Function by nicoburns on Tuesday December 21, 2021 @06:13PM (#29643281)
I think they want a function that output "1 year" given those two dates + "years" as arguments.
Re:PostgreSQL's Missing DateDiff Function by castorp on Wednesday December 22, 2021 @08:08AM (#29648796)
But the difference between 2021-01-01 and 2020-12-31 is only one day, not a year.

It gets even strange for e.g. select DATEDIFF(year, '2021-12-01', '2022-01-01') which still returns 1 even though that's a whole month. I don't see a use case for this kind of result

Re:PostgreSQL's Missing DateDiff Function by erichocean on Tuesday December 21, 2021 @06:25PM (#29643377)
Correct.
Re:PostgreSQL's Missing DateDiff Function by castorp on Wednesday December 22, 2021 @02:14AM (#29646381)
> No, for example, the datediff in years for New Year's Eve and New Year's Day should be 1

I don't understand the purpose of such a calculation. If you want to check if two dates (or timestamps) fall into the same year, you compare the year part of them. If you want to check how far they are apart, you compare the difference between the two to an interval.

Re:PostgreSQL's Missing DateDiff Function by cedricd on Tuesday December 21, 2021 @05:52PM (#29643079)
Yep. That's exactly why this function isn't trivial to write. Boundaries are not obvious and native pg functions (insofar as I'm aware of them) don't do these kinds of diffs.

Semantically the code has to diff days (for example) but be aware that you crossed a year boundary (or any other).

Re:PostgreSQL's Missing DateDiff Function by pella on Tuesday December 21, 2021 @06:29PM (#29643414)
alternative - for 1y results:

  select date_part('year','2021-01-01'::date) - date_part('year','2020-12-31'::date) as yeardiff;

  yeardiff 
  ----------
      1
  (1 row)
Re:PostgreSQL's Missing DateDiff Function by tomrod on Tuesday December 21, 2021 @07:01PM (#29643695)
That doesn't make sense to me. Why wouldn't datediff be 0 in this case, as it is less than 365 days? Else one should simply:

    extract(year from d1) - extract(year from d2) 
and call it a day?
Re:PostgreSQL's Missing DateDiff Function by btilly on Tuesday December 21, 2021 @08:22PM (#29644416)
Try it again, except looking for months. You'll get -11 instead of 1.
Re:PostgreSQL's Missing DateDiff Function by castorp on Wednesday December 22, 2021 @08:16AM (#29648878)

    select extract(month from justify_interval(timestamp '2022-01-01' - timestamp '2021-11-01'))
returns 2
Re:PostgreSQL's Missing DateDiff Function by saltcured on Wednesday December 22, 2021 @02:32PM (#29653933)
I'm pretty sure this will provide the desired semantics, though I am away from a usable postgres and not bothering to test it.

CREATE FUNCTION date_diff(granularity text, t1 timestamptz, t2 timestamptz) RETURNS int8 AS $$ SELECT date_part(granularity, date_trunc(granularity, t2) - date_trunc(granularity, t1))::int8 $$ LANGUAGE SQL;

Re:PostgreSQL's Missing DateDiff Function by brandmeyer on Tuesday December 21, 2021 @06:12PM (#29643277)
Why would you want this to round up instead of down or to nearest?
Re:PostgreSQL's Missing DateDiff Function by wongarsu on Tuesday December 21, 2021 @06:18PM (#29643328)
It's not even rounded up, datediff('years', 2020-01-01, 2021-12-31) returns 1.

I'm not sure what it's used for though

Re:PostgreSQL's Missing DateDiff Function by d0100 on Tuesday December 21, 2021 @06:40PM (#29643511)
extract(year from '2021-01-01'::timestamptz) - extract(year from '2020-12-31'::timestamptz)

For months and days it would be a bit bigger

Re:PostgreSQL's Missing DateDiff Function by Nullabillity on Tuesday December 21, 2021 @05:51PM (#29643077)
Doesn't look like that works correctly for smaller units. For example:

    teo=> select extract(minutes from interval '70 minutes');
     date_part 
    -----------
            10
    (1 row)
For a sum this should return 70 (since there are obviously 70 minutes in total). Instead, it gets normalized to 1 hour and 10 minutes, and returns the minute component only.
Re:PostgreSQL's Missing DateDiff Function by jimktrains2 on Tuesday December 21, 2021 @06:51PM (#29643589)
I wouldn't say it's incorrect, but it is definitely different that what is sometimes needed.

The only, and unsatisfactory, answer I've seen to get a timedelta in wholey one unit is to extract the epoch and divide by the number of seconds in that unit.