SQL（？）：计算两个日期时间值之间的时间

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.

当我需要仅计算08:00:00-16:00:00之间的时间时,计算从MySQL获取的两个日期时间值之间的时间的最佳方法是什么。

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.

例如,如果我有值2008-10-13 18:00:00和2008-10-14 10:00:00,则时差应为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.

我可以用SQL做什么或者最好的方法是什么?我正在建立一个网站并使用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.

EDIT2:我在计算实际时差方面没有问题,但减去了关闭时间,每天00:00:00-08:00:00和16:00:00-00:00:00。

-Samuli

3 个解决方案

#1

The TIMEDIFF function

TIMEDIFF功能

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.

TIMEDIFF()返回表达为时间值的expr1 - expr2。 expr1和expr2是时间或日期和时间表达式,但两者必须属于同一类型。

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'

#2

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

我认为您应该计算自己代码中的差异,而不是使用更复杂的SQL语句,因为:

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.

数据库通常是瓶颈,所以不要加载超过需要的数据库。

#3

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

您可能想在PHP中尝试它,但我认为它在DB中会更快

code to return difference in an array

代码返回数组中的差异

#1