Complex calculation of the hours amount

Question

I have a Pythonic system that stores student absences data in a SQLite database. Each row includes the start and end time of the absence, represented by the number of seconds since Jan 01 1970. I was asked to add a feature which limits the number of hours of absence per week.

It sounds easy to pull out the amount of hours, using a statement like this:

SELECT (sum(ending-starting)/3600) 
FROM requests
WHERE student_id = {x}
AND starting BETWEEN {y} AND ({y}+604800)
AND approved = 1 

The problem is that the limit must only be the hours defined as "mandatory presence." For example, if a user has defined the hours 8:00 to 17:00 as a "mandatory presence," an absence that begins on Sunday at 14:00 and ends on Monday at the same time, will be calculated in the code above 24 hours, while in practice it is only 9 hours.

"Mandatory presence" is defined in the database as two numerical parameters: "morning" and "evening" (always a round hour). Is there a way to make the calculation above taking into account these two numbers? If it can not be done in sql, I would love to hear how to select the data in sql and then perform the calculation in python.

Answer 1

MikeT's answer is not entirely working, but it certainly helped me reach the desired result. Here's the perfect statement:

SELECT
(
    sum((
        (ending - starting)
        -(
            CASE WHEN starting < strftime('%s',date(starting,'unixepoch')||printf(' %02d:00', morning))
            THEN strftime('%s',date(starting,'unixepoch')||printf(' %02d:00', morning)) - starting 
            ELSE 0 
            END
            +
            CASE WHEN ending > strftime('%s',date(ending,'unixepoch')||printf(' %02d:00', evening))
            THEN ending - strftime('%s',date(ending,'unixepoch')||printf(' %02d:00', evening))
            ELSE 0 
            END
        )
    ) /3600.0
    -(
      (24-evening+morning)
      *
      (round(julianday(ending, 'unixepoch'))-round(julianday(starting, 'unixepoch')))
    )
)) AS ha
FROM requests
INNER JOIN students ON requests.student_id = students.ID
INNER JOIN institutes ON students.inst_id = institutes.ID
WHERE student_id = {x}
AND starting BETWEEN {y} AND ({y}+604800)
AND approved = 1;

Thank you very much for your help!