SQL - Normalising timestamps to business hours

Question

My initial answer to this problem has been to script it. Instead of using SQL, I've dipped into Python and normalised them. I am curious whether anyone can come up with a solution using SQL though.

If a date occurs outside of business hours, I want to normalise the date to the next working day. I'll keep this really simple and say that business hours is 9am to 6pm Monday to Friday. Anything outside of those hours is outside of business hours.

What should happen the dates is that they are changed so that 2pm on Saturday becomes 9am on Monday morning (the first legitimate time in the business week). 7pm on a Wednesday becomes 9am Thursday morning. etc. etc. Let's ignore holidays.

Sample data:

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)

The first date is a Sunday so it should be normalised to 2009-09-14 09:00:00

The second date is fine, it's at 9am on a Monday.

The third date is fine, it's at 9am on a Tuesday.

The fourth date is at 9pm (outside of our 9am to 6pm business hours) on a Wednesday and should be transformed to 9am Thursday morning.

Answer 1

Not sure why you want to do this, but if it needs to always be true of all data in your database, you need a trigger. I would set up a table to pull from that specifies the business hours and you can use that table to determine the next valid business hour day and time. (I might even consider making a table that tells you exactly what the next business day and hour is for each possibility, it's not like this changes a lot, might have to be updated once a year if you change holidays for the next year or if you change the overall business hours. By precalulating, you can probably save time in processing this.). I would also conmtinue to use your script becasue it's better to fix data before it gets entered, but you need the trigger to ensure that data from any source (and sooner or later there will be changes form sources other than your application) meets the data integrity rules.

Answer 2

I don't think you can do it in one query, but you can try this:

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

Answer 3

I think you're better off with your Python solution ... but I like challenges :)

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

Tested on 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