在MySQL中计算早上6点之前和晚上10点之后的时差 [英] Calculating time difference before 6am and after 10pm in MySQL

问题描述

我对MySQL日期/时间功能有一个疑问。现在，我有这样的查询：

I have one question regarding MySQL date / time functions. For now, I have query which looks like this:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(sum)))
FROM workingtime
WHERE user='magdalena'
AND type='work'
AND start BETWEEN '2014-03-01' AND '2014-03-12'
AND completed=1

所以在我的数据库中，有2个带有时间戳的列，第一个是开始，第二个是带时间戳一个是结束。我想在单查询中执行的操作是：我想返回开始时间和早上6点之间的时间差是多少+晚上10点到结束之间的时间差是多少（可以选择nex day ...）。我需要在夜班时间使用-因此，我总共需要夜班时间总计。

So in my database there are 2 columns with timestamps, first one is "start", second one is "end". What I would like to execute in sigle query is this: I would like to have returned how much is the time difference between start and 6am + how much is the time difference between 10pm and end (with option for nex day...). I need this for night shift hours - so I need sum of nightshift hours together as a result.

示例：

开始= 2014-02-26 03:30:00

start = 2014-02-26 03:30:00

结束= 2014-02-26 12:16:59

end = 2014-02-26 12:16:59

我想在早上6点，晚上10点和结束之间取得差价。
在这种情况下：3:30:00和6:00:00之间的差是2:30:00。在这种情况下，晚上10点与结束之间没有区别，因为结束时间根本不超过10pm。因此，这种情况下的结果将为2:50:00。这就是我想要的输出。

I would like to get difference between start and 6am and 10pm and end. In this case: difference between 3:30:00 and 6:00:00 is 2:30:00. Difference between 10pm and end is nothing in this case, because end time is not over 10pm at all. So the result in this case will be 2:50:00. That is the output I would like to get.

只有MySQL能做到吗？
谢谢大家，我非常感谢。

Is this possible only with MySQL? Thank you guys, I appreciate it.

推荐答案

使用可以使用 TIMEDIFF 函数，如下所示：

Use can use the TIMEDIFF function, like this:

select 
  CONCAT(HOUR(TIMEDIFF(starttime, CONCAT(DATE(starttime),' 06:00:00'))), ':', MINUTE(TIMEDIFF(starttime, CONCAT(DATE(starttime),' 06:00:00')))) AS startdiff,
  CONCAT(HOUR(TIMEDIFF(CONCAT(DATE(endtime),' 22:00:00'), endtime)), ':', MINUTE(TIMEDIFF(CONCAT(DATE(endtime),' 22:00:00'), endtime))) AS enddiff
from workingtime

工作演示： http://sqlfiddle.com/#!2/fc621/1

我无法理解您问题的以下部分：第二天有选择

I was not able to understand the following part of your question: "with option for next day"

这篇关于在MySQL中计算早上6点之前和晚上10点之后的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！