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.
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.
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.
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.
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.
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).
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
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.
> 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.
> 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.
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.
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.
> 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.
> 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'
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'
59 comments
[ 3.2 ms ] story [ 31.6 ms ] threadI'm surprised there is not native function. Does anyone know why?
Redshift, famously based off Postgres, chose to implement it
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.
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.
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...
Semantically the code has to diff days (for example) but be aware that you crossed a year boundary (or any other).
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
So this is correct for years, right?
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.
> # SELECT date_part('year', '2021-01-01'::timestamptz) - date_part('year', '2020-12-31'::timestamptz) as years;
Or what am I missing?
[edit: formatting]
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.
I'm not sure what it's used for though
For months and days it would be a bit bigger
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;
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.
> The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time
> 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.
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.
What is the use of such a function? If I saw this answer I would assume a bug somewhere (until reading the documentation).
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).
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 :)
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
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.
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'
And those values relative values won't change every month like they would if I just grouped by
(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.