7 ms·

PostgreSQL's Missing DateDiff Function

78 points by cedricd 5y ago

deleted 5y ago
[deleted]
xupybd 5y ago
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?
- kevinmcconnell 5y ago
  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.
  - erichocean 5y ago
    That's…not what this function does.
    - kevinmcconnell 5y ago
      Ah right, my mistake. I’m used to DATEDIFF on other platforms meaning the interval between two dates, and misread.
- cedricd 5y ago
  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
  - WkndTriathlete 5y ago
    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.)
    - SAI_Peregrinus 5y ago
      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.
      - wruza 5y ago
        You don’t have to, see my other comment.
        SAI_Peregrinus 5y ago
        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.
    - wruza 5y ago
      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-python-return-on-the-leap-second https://stackoverflow.com/questions/39686553/what-does-pytho... Js (also mentions most others): https://stackoverflow.com/questions/53019726/where-are-the-leap-seconds-in-javascript https://stackoverflow.com/questions/53019726/where-are-the-l... C#: https://stackoverflow.com/questions/8760674/are-nets-datetime-methods-capable-of-recognising-a-leap-second 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.
- deleted 5y ago
  [deleted]
- castorp 5y ago
  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.
  - michael-ax 5y ago
    one is counting distance, the other one buckets. alias the fn name to datebucketdelta to make the different problems memorable individually. :)
    - castorp 5y ago
      Well for "counting buckets" you can use `date_bin()` since Postgres 14 which groups the difference between timestamps into defined intervals.
Izkata 5y ago
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#FUNCTIONS-DATETIME-EXTRACT 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-calculate-date-difference-in-postgresql https://stackoverflow.com/questions/24929735/how-to-calculat...
- cldellow 5y ago
  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);
  - cedricd 5y ago
    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).
  - mrkurt 5y ago
    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
    - nicoburns 5y ago
      I think they want a function that output "1 year" given those two dates + "years" as arguments.
      - erichocean 5y ago
        Correct.
jimktrains2 5y ago
> 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).
- cedricd 5y ago
  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).
  - jimktrains2 5y ago
    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?
    - cedricd 5y ago
      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 :)
      - wongarsu 5y ago
        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.

78 points by cedricd 5y ago

deleted 5y ago

[deleted]

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?

kevinmcconnell 5y ago
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.
- erichocean 5y ago
  That's…not what this function does.
  - kevinmcconnell 5y ago
    Ah right, my mistake. I’m used to DATEDIFF on other platforms meaning the interval between two dates, and misread.
cedricd 5y ago
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
- WkndTriathlete 5y ago
  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.)
  - SAI_Peregrinus 5y ago
    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.
    - wruza 5y ago
      You don’t have to, see my other comment.
      - SAI_Peregrinus 5y ago
        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.
  - wruza 5y ago
    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-python-return-on-the-leap-second https://stackoverflow.com/questions/39686553/what-does-pytho... Js (also mentions most others): https://stackoverflow.com/questions/53019726/where-are-the-leap-seconds-in-javascript https://stackoverflow.com/questions/53019726/where-are-the-l... C#: https://stackoverflow.com/questions/8760674/are-nets-datetime-methods-capable-of-recognising-a-leap-second 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.
deleted 5y ago
[deleted]
castorp 5y ago
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.
- michael-ax 5y ago
  one is counting distance, the other one buckets. alias the fn name to datebucketdelta to make the different problems memorable individually. :)
  - castorp 5y ago
    Well for "counting buckets" you can use `date_bin()` since Postgres 14 which groups the difference between timestamps into defined intervals.

Izkata 5y ago

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#FUNCTIONS-DATETIME-EXTRACT 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-calculate-date-difference-in-postgresql https://stackoverflow.com/questions/24929735/how-to-calculat...

cldellow 5y ago
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);
- cedricd 5y ago
  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).
- mrkurt 5y ago
  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
  - nicoburns 5y ago
    I think they want a function that output "1 year" given those two dates + "years" as arguments.
    - erichocean 5y ago
      Correct.

jimktrains2 5y ago

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

cedricd 5y ago
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).
- jimktrains2 5y ago
  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?
  - cedricd 5y ago
    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 :)
    - wongarsu 5y ago
      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.