c# - 为连续日期选择最近的时间

Question

假设我有日期时间格式的日期列表:

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

我想为列表中的每个日期抓取离凌晨 5 点最近的时间。我该怎么办？

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

应该获取离当前日期最近的日期，但对于每个不同的日期？

Answer 1

您可以使用 TIMEDIFF和 TIME_TO_SEC获取与其他日期“最近”的日期的方法:

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

要获取每天的关闭日期和可用条目，您需要扩展此查询，因此它不会与固定日期进行比较，而是“每天早上 5 点”:

要实现这一点:

• 计算每个条目到它的 5 AM 日期
的最小偏移量
• 分组依据 DATE(d)
• 现在是关键点:而不是选择 date (或 min(date) )由于分组*(在帖子末尾注释)可能是错误的，我们使用我们定位的日期加上(或减去)偏移量(这是正确的 由于与 min() 聚合一起分组)

在示例中DATE_ADD(DATE(d), INTERVAL 19 HOUR)用于确定7 pm对于当前行。对于 5 am它将是 DATE_ADD(DATE(d), INTERVAL 5 HOUR) :

(我在查询中留下了用于调试的列，它们可以从中删除。您只需要 actualDate -列)

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)

数据:

     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");

结果:

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

解释:

• 当我们确定了 Min(Abs())偏移量，我们需要弄清楚，如果我们需要 add 7pm 的偏移量值，或 subtract
• 我们使用 2 个控制偏移来确定:
  • controlOffset1: MIN(actualDate - 7pm)
  • controlOffset2:MIN(7pm - actualDate)
• 案例 1:偏移匹配 controlOffset1:我们有一个比目标晚的实际日期 -> 使用 DATE_ADD
• 案例 2:偏移匹配 controlOffset2:我们有一个比目标早的实际日期 -> 使用 DATE_SUB
• 案例 3:controlOffset1 匹配 controlOffset2:我们的实际日期晚于目标日期，但其他日期早于:使用 DATE_ADD
• 案例 4:甚至不匹配案例 3 的所有内容:必须是案例 4，使用 DATE_SUB :-)

现在唯一未确定的可能是，如果两个日期 +/- 具有相同的偏移量，即 +/- 5 分钟。因此无论如何结果都是不确定的，所以你应该能够通过使用 >= 扩展一些条件来获得非空值。或 <= . (编辑:将与案例 3 匹配，选择较晚的日期:http://sqlfiddle.com/#!9/21f2ce/2)

ps.: 与应用程序端迭代方法中的“单次获取”相比，最终解决方案的性能表现会很有趣。

*比较偏移量:为什么是Min(d - 7pm)与 Min(7pm -d) 不同的偏移量- 不仅是标志？因为d由于缺少聚合而未确定。因此，使用两个控制偏移，我们可以根据 7pm 确定值 正确聚合，因此可靠。)