SQL(?): Counting the time between two datetime values

Question

Hi,

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

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

Can I do it with SQL or what is the best way to do it? I'm building a website and using PHP.

Thank you for your answers.

EDIT: The exact thing is that I'm trying to count the time a "ticket" has been in a specific state during working hours. The time could be like a couple weeks.

EDIT2: I have no problems counting the actual time difference, but substracting that off-time, 00:00:00-08:00:00 and 16:00:00-00:00:00 per day.

-Samuli

Answer 1

The TIMEDIFF function

TIMEDIFF() returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, 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

You may want to try it in PHP, but I'm thinking it'll be faster in DB

code to return difference in an array

Answer 3

I think you should calculate the difference in your own code instead of using a more-complex SQL sentence because:

• That calculation seems to be part of your business logic. It seems easier to maintain if you integrate it with the rest of your business code.
• The database is often the bottleneck, so don't load it more than needed.