Advanced SQL date subtraction

I'm working on a game who track's how long you are logged in. It saves a check-in time to the database, aswell as an check-out date.

It's a game, something like capture the flag, only what i don't want it to do is counting the time between 18:00 (6 pm) and 09:00 (9 am) with the score. This goes for every day, and not just once.

The score is displayed in seconds. Easly said it's the difference between the check-in and the check-out time (minus the time between 18:00-09:00, for everyday).

Is somebody an expert in advanced SQL date calculations and can help me out? Please!

This is my basic query, without the time subtraction:

SELECT
SUM(TIMESTAMPDIFF(SECOND,sessions.checkin,IFNULL(sessions.checkout,NOW()))) 
AS score
FROM `sessions`
GRO开发者_如何学运维UP BY `user_id`

---

It will not be pretty (you might want to add functions for readability of your queries if you are going to do this a lot)

Assuming you don't have logins that span multiple days you can do the following

   SELECT
       user_id,
       SUM(
          TIMESTAMPDIFF(SECOND,
             CASE 
                WHEN sessions.checkin < timestamp(date(sessions.checkin), maketime(9,0,0))
                   THEN timestamp(date(sessions.checkin), maketime(9,0,0))
                WHEN sessions.checkin > timestamp(date(sessions.checkin), maketime(18,0,0))
                   THEN timestamp(date(sessions.checkin)+1, maketime(9,0,0))
                ELSE
                   sessions.checkin
             END,
             CASE
                WHEN IFNULL(sessions.checkout, now()) > timestamp(date(IFNULL(sessions.checkout, now())), maketime(18,0,0))
                   THEN timestamp(date(IFNULL(sessions.checkout, now())), maketime(18,0,0))
                WHEN IFNULL(sessions.checkout, now()) < timestamp(date(IFNULL(sessions.checkout, now())), maketime(9,0,0))
                   THEN timestamp(date(IFNULL(sessions.checkout, now()))-1, maketime(18,0,0))
                ELSE
                   IFNULL(sessions.checkout, now())
             END)) AS score
    FROM `sessions`
    GROUP BY `user_id`

It does not work if logins that span multiple days or to be precise the query can work with logins that span multiple days such as checkin at 18:05 on first day and checkout at 8:55 on the 3rd day, but as soon as it spans into countable time on multiple days the caluclation is not over - for each record that spans countable times on multiple days you need to substract number of uncountable seconds from the result. Let me know if you need help with that.

The above query should run relatively cheaply - given the fact that you are running it over the whole table anyway, so it will do a table scan, the fact that there are a lot of repeated calculations and that it looks huge should not matter as much. These calculations happen in memory with no additional I/O and that should happen a few orders of magnitude faster than disk I/O (compare with your original query's execution time and let us know if you'll see any degradation in performance).