4 ms·

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,

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.

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.