PostgreSQL's Missing DateDiff Function | Hacker News Reader

PostgreSQL's Missing DateDiff Function

narrator.ai

78 pointscedricd57 comments

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

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

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

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

I think they want a function that output "1 year" given those two dates + "years" as arguments.

Correct.

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

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

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

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.

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]

So isn't that `age ( timestamp, timestamp ) → interval`?

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.

isn't that what the AGE function do?

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

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

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)

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

For months and days it would be a bit bigger

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?

Try it again, except looking for months. You'll get -11 instead of 1.

    select extract(month from justify_interval(timestamp '2022-01-01' - timestamp '2021-11-01'))

returns 2

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;

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

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

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.

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.

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.

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.

> DATEDIFF('year', '12-31-2020', '01-01-2021') returns 1 because even though the two dates are a day apart, they've crossed the year boundary.

What is the use of such a function? If I saw this answer I would assume a bug somewhere (until reading the documentation).

These are used pretty often when doing data analysis. It's a simple way to group things together.

For example, find all users who have have been active at least 10 days. This provides a simple way to do a complex operation (if a user signs up Dec 29, a naive implementation won't catch that their 10 days is in January of the next year).

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?

The point of the function is that it doesn't matter you've crossed the year boundary when counting days.

A naive implementation would accidentally get tripped up by the year -- extracting the 'day' part of a timestamp, as an integer, gives you the day from the start of the year. So on one side of the year boundary you have 365 and on the other you have 1. The way to do it correctly is to multiply the day in the year by the year itself so that a '1' on a later year is a bigger number.

And of course grouping by year isn't always what you want to do :)

My naive implementation would have been to calculate `now() - signup_date > "10 days"::interval`. That doesn't trip over any year bounds, but if you sign up at 4pm then I wouldn't consider you until 4pm on the 10th day. Which makes total sense, but generally isn't how this is done when a human does it by hand.

I feel like cedricd and OP of this chain are talking about different things, or at least it's hard to discern from the conversation.

If you care about Days, you normally absolutely -should- use DATEDIFF('DAY' instead of DATEDIFF('YEAR' and act accordingly. The bigger thing is the semantics they provide.

Frankly, this can be a pain point at times, but IIRC PostgreSQL at least is very work-aroundable, IIRC you can get the equivalent for most common cases off the interval from an add/sub.

Honestly, Dates in SQLite are harder to deal with in the long term, since without a native data type you have to write at least the level of conversions you would in PostgreSQL. (e.x. just convert everything to/from tics at the abstraction layer.)

Edit: Also I would suggest considering instead use of getdate() <= DATEADD('day', signup_date,1) or a variant as that is probably more cross DB friendly

It means “spanned which legal-y periods”, I think. E.g. if a user is billed weekly, billing should ~group 2 weeks, even if login events happened at Sat and Mon. As a former accounting consultant/developer I can guess use cases like “group fixed assets by how many tax amortization quarters they already took part in, and then …”.

Lol my favorite case of this was where an org could by regulation be paid no less than 30 days apart, but could not take more than 1 payment per month, and could not take a payment on Sundays or banking holidays. Solve for X.

Find the minimum and maximum maximum number of consecutive payments this org can take?

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

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'

If I want a table of figures for the past 18 months, I could aggregate them like so:

    GROUP BY DATEDIFF('month', "transaction_date", NOW())

And get a result set that shows the relative month in one column, and the sum in another.

And those values relative values won't change every month like they would if I just grouped by

    EXTRACT('year-month', "transaction_date")

(or whatever the syntax is for that)

Also useful for a stored procedure or view than can then be JOINed in other queries as time goes on.

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

That would produce dates instead of integers though. I want a table that looks like this:

    MonthsAgo | Sales
    ==================
            4 | 4,500
            3 | 7,204
            2 | 12,578
            1 | 15,748
            0 | 34,485

And maybe use a parameter so the user can go by week, or quarter, or whatever.

The "distance" between two dates seems quite useful. In Postgres there is no need for a datediff() function - you would just subtract the two dates: date '2021-01-01' - date '2020-12-31'

This seems like a big yet simple feature that Postgresql is missing. I've used this many times in SQL server.

I'm surprised there is not native function. Does anyone know why?

I've always wondered myself. Maybe it's because it's mostly useful for analytical workloads instead of operational ones.

Redshift, famously based off Postgres, chose to implement it

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

Seconds are particularly hard. There's no way to predict exactly when leap seconds will get added, so if one of the times is far enough in the future you can't get an exact difference in seconds. And you have to keep track of not just the current number of leap seconds added to UTC from when the epoch started, but when those leap seconds were added.

You don’t have to, see my other comment.

Yes, if you can accept the precision loss (you probably can) it's fine to ignore leap seconds, so only DST matters. Or time zones, if you're not using UTC, GPS, or TAI.

But I just got done writing a reference clock driver for the Chrony NTP server/client for a GPS module which outputs GPS time. But Chrony needs samples in UTC, so I did have to care about leap seconds to make that particular GPS source usable. And I had to add a way to update the leap seconds offset when a new leap second will be scheduled. Thankfully I had no need to convert differences between wildly different timestamps to sub-second precision.

more annoying ones being days around the date of adoption of the Gregorian calendar

Nobody really cares except for pedantic or historian reasons. You have to use a specialized library for such non-dumbed-down dates in programming languages, and sql is not an exception. Day is exactly 86400 seconds, with an hour correction when formatting (or parsing) under system-known DST.

Almost all systems use generic dates (at a day granularity), which are isotropic at all times, by ignoring these historical jumps. The only real/modern things are DST and leap seconds, the latter also often ignored for programmer’s sanity.

Python: https://stackoverflow.com/questions/39686553/what-does-pytho...

Js (also mentions most others): https://stackoverflow.com/questions/53019726/where-are-the-l...

C#: https://stackoverflow.com/questions/8760674/are-nets-datetim...

Leap seconds only have sense in let’s name it “real-event-time” systems, where common generic dates are unusable anyway. It’s a complete nonsense in regular programming and in sql. Regular systems are okay with being off with each other, and leap seconds are smeared across much bigger differences by ntp et al.

Don’t overthink software dates.

You can find the difference between dates by subtracting one from the other, the same way you’d find the difference between two numbers. That’s why there’s no need for a function to do it.

That's…not what this function does.

Ah right, my mistake. I’m used to DATEDIFF on other platforms meaning the interval between two dates, and misread.

I never understood the need for a datediff function. In Postgres (or Oracle) you just subtract two timestamps and use the resulting interval. It's a different approach to the same problem.

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

Well for "counting buckets" you can use `date_bin()` since Postgres 14 which groups the difference between timestamps into defined intervals.

Reply on news.ycombinator.com