代码之家 › 专栏 › 技术社区 › Philip Reynolds

SQL-将时间戳标准化为工作时间

mysql sql

Philip Reynolds · 技术社区 · 16 年前

我对这个问题的最初回答是编写脚本。我不再使用SQL,而是深入了解Python并将其规范化。不过,我很好奇是否有人能想出一个使用SQL的解决方案。

如果某个日期出现在工作时间之外,我希望将该日期正常化到下一个工作日。我会保持简单的说法,营业时间是周一到周五上午9点到下午6点。那些时间以外的任何事情都是工作时间以外的。

应该发生的是日期被更改,使星期六下午2点变成星期一上午9点(商业周中的第一个合法时间)。星期三晚上7点变成星期四上午9点。等等。让我们忽略假期。

样本数据:

mysql> select mydate from mytable ORDER by mydate;
+---------------------+
| mydate              |
+---------------------+
| 2009-09-13 17:03:09 | 
| 2009-09-14 09:45:49 | 
| 2009-09-15 09:57:28 | 
| 2009-09-16 21:55:01 | 
+---------------------+
4 rows in set (0.00 sec)

第一个日期是星期天,所以应该正常化到2009-09-14 09:00:00

第二次约会很好,星期一上午9点。

第三次约会很好,星期二上午9点。

第四个日期是星期三晚上9点(不在我们的营业时间上午9点到下午6点),应该改为星期四上午9点。

3 回复 | 直到 16 年前

pmg 16 年前

我觉得你最好用你的python解决方案…但我喜欢挑战:)

select mydate
     , case dayadjust
-- BUG
--         when 0 then mydate
-- BUG
           when 0 then case
                 when hour(mydate)<9
                       then date_add(from_days(to_days(mydate)),
                               INTERVAL 9 HOUR)
                 else mydate
           end
-- BUG SQUASHED
           else date_add(from_days(to_days(mydate) + dayadjust),
                         INTERVAL 9 HOUR)
       end as mynewdate
from (
        select mydate
             , case
                   when addday>=moreday then addday
                   else moreday
               end as dayadjust
        from (
                select mydate
                     , weekday(mydate) as w
                     , hour(mydate) as h
                     , case weekday(mydate)
                           when 6 then 1
                           when 5 then 2
                           when 4 then
                                   case
                                         when hour(mydate) >= 18 then 3
                                         else 0
                                   end
                           else 0
                       end as addday
                     , case when hour(mydate)>=18 then 1 else 0 end as moreday
                from mytable
                order by mydate
        ) alias1
) alias2

在MySQL上测试

$ mysql tmp < phil.sql
mydate  mynewdate
2009-09-12 17:03:09     2009-09-14 09:00:00
2009-09-12 21:03:09     2009-09-14 09:00:00
2009-09-13 17:03:09     2009-09-14 09:00:00
2009-09-14 09:45:49     2009-09-14 09:45:49
2009-09-15 09:57:28     2009-09-15 09:57:28
2009-09-16 21:55:01     2009-09-17 09:00:00
2009-09-17 11:03:09     2009-09-17 11:03:09
2009-09-17 22:03:09     2009-09-18 09:00:00
2009-09-18 12:03:09     2009-09-18 12:03:09
2009-09-18 19:03:09     2009-09-21 09:00:00
2009-09-19 06:03:09     2009-09-21 09:00:00
2009-09-19 16:03:09     2009-09-21 09:00:00
2009-09-19 19:03:09     2009-09-21 09:00:00

HLGEM 16 年前

不知道为什么要这样做,但是如果需要始终对数据库中的所有数据都是正确的,则需要一个触发器。我将设置一个表,从中提取指定工作时间的表,您可以使用该表确定下一个有效的工作时间日和时间。(我可能会考虑制作一个表格,告诉你下一个工作日和下一个工作时间是什么,每种可能性,这不是很多变化,如果你改变下一年的假期,或者如果你改变整个工作时间,可能需要每年更新一次。通过预润滑,您可能可以节省处理时间)。我也会使用您的脚本,因为在输入数据之前最好先修复它,但是您需要触发器来确保来自任何源的数据(以及早晚有来自除您的应用程序以外的其他源的更改)满足数据完整性规则。

Lukasz Lysik 16 年前

我认为您不能在一个查询中完成,但您可以尝试以下操作:

-- Mon-Thu, after 17:00
-- Set date = next day, 9:00
UPDATE 
    myTable 
SET  
    mydate = DATE_ADD(DATE_ADD(DATE(date), INTERVAL 1 DAY), INTERVAL 9 HOUR) 
WHERE 
    TIME(mydate) >= 17 
    AND DAYOFWEEK(mydate) IN (1,2,3,4)

-- Mon-Fri, before 9:00
-- Set date = the same day, 9:00
UPDATE 
    myTable 
SET 
    mydate = DATE_ADD(DATE(date), INTERVAL 9 HOUR) 
WHERE 
    TIME(mydate) < 9 
    AND DAYOFWEEK(mydate) IN (1,2,3,4,5)

-- Fri, after 17:00, Sat, Sun
-- Set date = monday, 9.00
UPDATE 
    myTable 
SET 
    mydate = DATE_ADD(DATE_ADD(DATE(date), INTERVAL 3 DAY), INTERVAL 9 HOUR) 
WHERE 
    (TIME(mydate) >= 17 
    AND DAYOFWEEK(mydate) = 5) 
    OR DAYOFWEEK(mydate) IN (6,7)