SQL (?): Counting time between two date and time values

Question

What is the best way to count the time between two datetime values ​​retrieved from MySQL, when I only need to count the time between the hours of 08: 00: 00-16: 00: 00.

For example, if I have the values ​​2008-10-13 18:00:00 and 2008-10-14 10:00:00, the time difference should be 02:00:00.

Can this be done using SQL or what is the best way to do this? I am building a website and using PHP.

Thank you for your responses.

EDIT: Exactly, I'm trying to calculate the time when the “ticket” was in a certain state during business hours. Time can be like a couple of weeks.

EDIT2: I have no problem calculating the actual time difference, but by subtracting this departure time, 00: 00: 00-08: 00: 00 and 16: 00: 00-00: 00: 00 per day.

-Samuli

Answer 1

TIMEDIFF Function

TIMEDIFF () returns expr1 - expr2 expressed as the value of time. expr1 and expr2 are the time or date and time of the expression, but both must be of the same type.

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', -> '2000:01:01 00:00:00.000001'); -> '-00:00:00.000001' mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001', -> '2008-12-30 01:01:01.000002'); -> '46:58:57.999999' 

Answer 2

I think you should calculate the difference in your own code instead of using the more complex SQL statement, because:

• These calculations seem to be part of your business logic. It seems easier to maintain if you integrate it with the rest of your business code.
• A database is often a bottleneck, so don't download it more than you need.

Answer 3

You might want to try in PHP, but I think DB will be faster

code to return the difference in the array