PostgreSQL's Missing DateDiff Function (opens in new tab)

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.

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

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

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

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)

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?

Why would you want this to round up instead of down or to nearest?

extract(year from '2021-01-01'::timestamptz) - extract(year from '2020-12-31'::timestamptz)

For months and days it would be a bit bigger

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.

In PostgreSQL you would group by date_trunc('month', transaction_date)

> These are used pretty often when doing data analysis.

In my experience you almost always want to count intervals for analysis and not interval boundaries. For example when calculating someone’s age in years.

The use cases for interval boundaries seem to mostly be businesses rules based on contract dates.

In that example, what does knowing you've crossed 1 year boundry give you (i.e. gow do you use the numeric result?) And if it does matter why not group by the year?

Normal subtraction works there so that's not a compelling example at all.

> For example, find all users who have have been active at least 10 days.

Well, then you only need to compare the difference of the timestamp (or date) values with an interval of 10 days. e.g. end_time - start_time >= interval '10 days'

one is counting distance, the other one buckets. alias the fn name to datebucketdelta to make the different problems memorable individually. :)

My guess is that it is because DATEDIFF is hard to write correctly. (In particular, the DATEDIFF function presented in the article, while probably correct in a lot of common cases, is incorrect for certain intervals and resolutions, the easiest of which are daylight saving time boundaries and leap seconds, and one of the more annoying ones being days around the date of adoption of the Gregorian calendar. Seriously, if you're in GB or the US and sitting at a Linux command window type `cal 9 1752` and view one of the wonders in the long, inglorious history of timekeeping.)

That's…not what this function does.