Hacker News Reader

Comment by xupybd | Hacker News Reader

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

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.

Reply on news.ycombinator.com