首页 文章

SQL - 将时间戳规范化为营业时间

提问于
浏览 次
1

我对此问题的初步答案是编写脚本 . 我没有使用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点转换到上午9点 .

sql mysql

3 回答

  • 0

    我认为你的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
    回复于
  • 1

    不确定为什么要这样做，但如果需要始终对数据库中的所有数据都这样，则需要一个触发器 . 我会设置一个表格来指定营业时间，您可以使用该表格来确定下一个有效的营业时间日期和时间 . （我甚至可以考虑制作一张 table ，告诉你下一个工作日和小时的具体情况，不是这个变化很大，如果你改变明年的假期，或者如果你改变一年的假期，可能需要每年更新一次 . 你改变整个营业时间 . 通过预先计算，你可以节省处理这个的时间 . ）我也会想要使用你的脚本，因为最好在输入之前修复数据，但你需要触发器来确保来自任何源的数据（迟早会有来自应用程序以外的源的更改）满足数据诚信规则 .

    回复于
  • 0

    我不认为你可以在一个查询中做到这一点，但你可以试试这个：

    -- 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)
    回复于

相关问题