如何suctrize corectly datetime（end_date和begin_date之间的差异） - mysql [英] How to sumarize corectly datetime (difference between end_date and begin_date) - mysql

问题描述

我在mysql中使用。我创建了表Sikcness，我添加了一条记录：

I'm using in mysql. I've created table "Sikcness" and I've added one record:

+--------+---------+---------+-------------+--------+----------+
| Id_SICK|ID_WORKER| BEGIN_DATE          | END_DATE            |
+--------+---------+---------+----------+------------+---------+
| 1      |   1     |2019-03-18 07:00:00  |2019-03-20 15:00:00  |  
+--------+---------+--------+------------+----------+----------+  

然后我想及时消失（End_date列和begin_date列之间的时差）命令：

Then i'd like to sumerize in time (Time difference between End_date column and begin_date column) by the command:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(Sickness.END_DATE) - TIME_TO_SEC(Sickness.BEGIN_DATE))) AS 'SICKNESS TIME' FROM Sickness WHERE ID_WORKER = 1

但我只有那个结果（这是不正确的）：

But i have only that result (which is incorrect):

SICKNESS TIME
08:00:00

该命令应该算如下：

That command should count it like this:

+---------+-------------+--------+----------+
| BEGIN_DATE          | END_DATE            |
+---------+----------+------------+---------+
|2019-03-18 07:00:00  |2019-03-18 15:00:00  |  
+--------+------------+----------+----------+  
|2019-03-19 07:00:00  |2019-03-19 15:00:00  |  
+--------+------------+----------+----------+  
|2019-03-20 07:00:00  |2019-03-20 15:00:00  |  
+--------+------------+----------+----------+ 

那么这是正确的：

Then it would be correct:

SICKNESS TIME
24:00:00

我应该写什么样的mysql查询？有任何想法吗？迎接。



我的尝试：



我试过了只有下面的命令：

What kind of mysql query should i write? Any ideas? Greets.

What I have tried:

I've tried only with this command in below:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(Sickness.END_DATE) - TIME_TO_SEC(Sickness.BEGIN_DATE))) AS 'SICKNESS TIME' FROM Sickness WHERE ID_WORKER = 1

它不起作用：（

And it didn't work :(

推荐答案

查看文档， TIME_TO_SEC 函数只计算时间值的秒数，而不是日期。你需要添加一些用于计算天数的代码。请查看 MySQL DATEDIFF（）函数 [ ^ ]。请注意，在上述情况下，它会将结果计算为2天，因此您需要相应地调整答案。

Looking at the documentation, the TIME_TO_SEC function only calculates the number of seconds in the time value, not the date. You would need to add some code to calculate the number of days also. Take a look at MySQL DATEDIFF() Function[^]. Note that in the above case it would calculate the result as 2 days, so you need to adjust your answer accordingly.

这篇关于如何suctrize corectly datetime（end_date和begin_date之间的差异） - mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！