##Background
There is a key 'feature' of the SQL Server DateDiff function that is relevant to this problem, and the DateDiff logic needs to be explained for this bug report to make sense.
DateDiff counts the 'boundaries' between two dates. For example, the function: DateDiff(hour, '1 jan 2015 00:30', '1 jan 2015 01:30') will return the value 1, because there is 1 "top of the hour" between 00:30 and 01:30.
The key phrase there, is "top of the hour". From the documentation for DateDiff:
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
So, from the times 00:05 to 00:15, there's 10 minutes, and no hour boundaries. The DateDiff in hours is 0, and in minutes is 10.
For the times 00:55 to 01:05 though, there's also 10 minutes, but there is 1 hour boundary, so the DateDiff in hours is 1, and in minutes is 10.
Here's an SEDE query that shows the above dates:
Note the last entry there, though... the datediff between 00:59 and 12:01 is 12 hours, but only 662 minutes (11 hours 2 minutes).
This is the key to this bug report.
##Curator badges
When the Curator badges were announced, there was the following description:
If you're interested in the implementation, this SEDE query is roughly what's being run.
If we look at that query, it is (in part):
and ph.UserId = a.OwnerUserId
and abs(datediff(hh, ph.CreationDate, a.CreationDate)) < 12)
That is saying, in "plain English": find edits and answers where the difference between their creation times is less than 12 hours.
Unfortunately, what it's actually saying is that there have to be 11 or fewer top-of-the-hours between them. This means that edits which have 12 top-of-the-hours between them, but less than 12 full hours between them, are being excluded.
The difference is small, but may be significant. For example, on Code Review, the difference amounts essentially to the following and a big thank-you to HABO for spotting it:
vs.
As I say, the difference is small... but....
##Fix
The fix for this is to not use the DateDiff function, but to use a 'between' system. Change the code:
and abs(datediff(hh, ph.CreationDate, a.CreationDate)) < 12)
to be the following instead:
and ph.CreationDate > DateAdd(hh, -12, a.CreationDate)
and ph.CreationDate < DateAdd(hh, 12, a.CreationDate)
##Follow-on
I happened to stumble over this issue as a result of the following discussion here: Have edits that happen more than 12 hours before the answer count for Explainer, Refiner and Illuminator
It concerns me that there may be a number of other places where DateDiff is used to award other badges, or many other things too... perhaps it would be a good idea to grep the code for other uses of DateDiff... ;-)
