select nearest time for sequential dates

Question

lets say I have list of dates in datetime format:

6/30/2015 10:44:44 PM
6/30/2015 11:00:24 PM 
7/1/2015 12:22:46 AM    
7/1/2015 12:26:38 AM 
7/1/2015 2:55:04 AM 
7/1/2015 3:23:00 AM 
7/1/2015 3:32:09 AM 
7/1/2015 3:52:27 AM 
7/1/2015 3:57:25 AM 
7/1/2015 4:03:34 AM 
7/1/2015 4:23:52 AM 
7/1/2015 4:32:00 AM 
7/1/2015 4:50:03 AM    
7/1/2015 4:54:46 AM 
7/1/2015 5:10:20 AM 
7/1/2015 5:13:37 AM 
7/1/2015 5:18:51 AM 
.... 
7/31/2015 11:18:51 PM

and I would like to grab a nearest time to 5AM for every date in the list. How should I go about it?

SELECT TOP 1 *
FROM x
WHERE x.date < @CurrentDate
ORDER BY x.date DESC

should grab the nearest date to the current date, but for every different date?

Answer 1

You can use the TIMEDIFF and TIME_TO_SEC method to get the "nearest" date to some other date:

SELECT 
  d 
FROM 
  test 
ORDER BY 
  ABS(TIME_TO_SEC(TIMEDIFF(d, "2015-10-23 19:00:00")))
LIMIT 
  0,1 ;

http://sqlfiddle.com/#!9/5b67f/2

To get the closed date per day and available entry, you need to extend this query, so it doesn't compare with a fixed date, but "5 AM every Day":

To achieve this:

• Calculate the minimum offset of every entry towards it's 5 AM date
• Group by DATE(d)
• Now the crucial point: rather than selecting the date ( or min(date)) which might be wrong due to grouping* (note at end of post), we use the date we are targeting plus (or minus) the offset (which is correct due to grouping along with the min() aggregation)

In the Example DATE_ADD(DATE(d), INTERVAL 19 HOUR) is used to determine 7 pm for the current row. For 5 am it would be DATE_ADD(DATE(d), INTERVAL 5 HOUR) :

(I left the columns for debugging in the query, they can be removed ofc. You only need the actualDate-Column)

SELECT 
  DATE(d) AS day,
  MIN(ABS(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR))))) AS offset,
  MIN(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))) AS controlOffset1,
  MIN(TIME_TO_SEC(TIMEDIFF(DATE_ADD(DATE(d), INTERVAL 19 HOUR),d))) AS controlOffset2,
  CASE
      WHEN MIN(ABS(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR))))) = MIN(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))) && MIN(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))) > 0 THEN
       DATE_Add(DATE_ADD(DATE(d), INTERVAL 19 HOUR), INTERVAL 
         MIN(ABS(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))))               
       SECOND)  
      WHEN MIN(ABS(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR))))) = MIN(TIME_TO_SEC(TIMEDIFF(DATE_ADD(DATE(d), INTERVAL 19 HOUR),d))) THEN
       DATE_SUB(DATE_ADD(DATE(d), INTERVAL 19 HOUR), INTERVAL 
         MIN(ABS(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))))               
       SECOND)  
      WHEN MIN(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))) = MIN(TIME_TO_SEC(TIMEDIFF(DATE_ADD(DATE(d), INTERVAL 19 HOUR),d))) THEN
       DATE_ADD(DATE_ADD(DATE(d), INTERVAL 19 HOUR), INTERVAL 
         MIN(ABS(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))))               
       SECOND)  
        WHEN MIN(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))) <> MIN(TIME_TO_SEC(TIMEDIFF(DATE_ADD(DATE(d), INTERVAL 19 HOUR),d))) THEN
        DATE_SUB(DATE_ADD(DATE(d), INTERVAL 19 HOUR), INTERVAL 
         MIN(ABS(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))))               
       SECOND)  
      END AS actualDate,
      case 

      WHEN MIN(ABS(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR))))) = MIN(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))) && MIN(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))) > 0 THEN
        "TEST#1"
        WHEN MIN(ABS(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR))))) = MIN(TIME_TO_SEC(TIMEDIFF(DATE_ADD(DATE(d), INTERVAL 19 HOUR),d))) THEN
        "TEST#2"
        WHEN MIN(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))) = MIN(TIME_TO_SEC(TIMEDIFF(DATE_ADD(DATE(d), INTERVAL 19 HOUR),d))) THEN
        "TEST#3"
        WHEN MIN(TIME_TO_SEC(TIMEDIFF(d, DATE_ADD(DATE(d), INTERVAL 19 HOUR)))) <> MIN(TIME_TO_SEC(TIMEDIFF(DATE_ADD(DATE(d), INTERVAL 19 HOUR),d))) THEN
        "TEST#4"
      END AS testCase
FROM 
  test 
GROUP BY DATE(d)

Data:

     CREATE table test (d datetime);

 # TEST 1: Solution later
 INSERT INTO test (d) values ("2015-10-23 19:10:00"); #this
 INSERT INTO test (d) values ("2015-10-23 19:20:00");
 INSERT INTO test (d) values ("2015-10-23 19:30:00");
 INSERT INTO test (d) values ("2015-10-23 19:40:00");
 INSERT INTO test (d) values ("2015-10-23 19:50:00");

 # TEST 2: Solution earlier
 INSERT INTO test (d) values ("2015-10-24 18:10:00");
 INSERT INTO test (d) values ("2015-10-24 18:20:00");
 INSERT INTO test (d) values ("2015-10-24 18:30:00");
 INSERT INTO test (d) values ("2015-10-24 18:40:00"); 
 INSERT INTO test (d) values ("2015-10-24 18:50:00");#this

 # TEST 3: Solution later, but earlier available
 INSERT INTO test (d) values ("2015-10-25 18:30:00");
 INSERT INTO test (d) values ("2015-10-25 18:40:00");
 INSERT INTO test (d) values ("2015-10-25 18:50:00");
 INSERT INTO test (d) values ("2015-10-25 19:05:00"); #this
 INSERT INTO test (d) values ("2015-10-25 19:10:00");
 INSERT INTO test (d) values ("2015-10-25 19:20:00");
 INSERT INTO test (d) values ("2015-10-25 19:30:00");

 # Test 4: Solution earlier, but later available
 INSERT INTO test (d) values ("2015-10-26 18:30:00");
 INSERT INTO test (d) values ("2015-10-26 18:40:00");
 INSERT INTO test (d) values ("2015-10-26 18:50:00");
 INSERT INTO test (d) values ("2015-10-26 18:55:00"); #this
 INSERT INTO test (d) values ("2015-10-26 19:10:00");
 INSERT INTO test (d) values ("2015-10-26 19:20:00");
 INSERT INTO test (d) values ("2015-10-26 19:30:00");
 INSERT INTO test (d) values ("2015-10-26 19:40:00");

Result:

day                         offset  controlOffset1  controlOffset2  actualDate                  testCase
October, 23 2015 00:00:00   600     600             -3000            October, 23 2015 19:10:00  TEST#1
October, 24 2015 00:00:00   600     -3000           600              October, 24 2015 18:50:00  TEST#2
October, 25 2015 00:00:00   300     -1800           -1800            October, 25 2015 19:05:00  TEST#3
October, 26 2015 00:00:00   300     -1800           -2400            October, 26 2015 18:55:00  TEST#4

http://sqlfiddle.com/#!9/951b5/22

Explanation:

• When we have determined the Min(Abs()) offset, we need to figure out, if we need to add this offset to the 7pm value, or subtract it.
• We are using 2 control offsets for the determination:
  • controlOffset1: MIN(actualDate - 7pm)
  • controlOffset2: MIN(7pm - actualDate)
• Case 1: offset matches controlOffset1: We have an actualDate LATER than the target -> Use DATE_ADD
• Case 2: offset matches controlOffset2: We have an actualDate EARLIER than the target -> Use DATE_SUB
• Case 3: controlOffset1 matches controlOffset2: We have an actualDate LATER than the target, but other dates before: Use DATE_ADD
• Case 4: Everything not even matching case 3: Musst be case 4, use DATE_SUB :-)

The only thing undertermined might now be, if two dates are +/- with the same offset, i.e. +/- 5 Minutes. Therefore the result is undetermined anyway, so you should be able to get non-null values by extending some conditions with >= or <=. (Edit: will be matched with case 3, picking the later date: http://sqlfiddle.com/#!9/21f2ce/2)

ps.: It would be interesting how the final solution behaves performance wise, compared to the "single-fetch" in an application-side iterative approach.

*compare the offsets: Why is Min(d - 7pm) a different offset than Min(7pm -d) - and not only the sign? Because d is undetermined due to missing aggregation. So with the two control offsets we can determine values depending on 7pm that are aggregated properly and therefore reliable.)