如何在mysql中的两个日期时间中获得差异 [英] How to get difference in two datetime in mysql

问题描述

Hai，



我需要得到两个日期时间的差异，并且下一列中设置了差异值。

喜欢< br $>

Hai,

I need to get difference of two datetime, and that diff value is set in next column.
like

Date	                 Duration
 28-02-2017 17:46:00	 
 28-02-2017 17:47:00	 00:01:00
 28-02-2017 17:49:00	 00:02:00
 28-02-2017 17:55:00	 00:06:00
Total            	 00:09:00

我尝试过： < br $> b $ b

What I have tried:

select t.start_time ,datediff(t.start_time,t1.start_time) durarion  
From ICC_resource_utilization t
inner join ICC_resource_utilization t1 on t.utilization_id=t1.utilization_id

推荐答案

这是SQL Server版本：

This is SQL Server version:

DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722';  
DECLARE @enddate datetime2 = '2007-05-04 12:10:09.3312722';   
SELECT DATEDIFF(day, @startdate, @enddate);  

这是MySQL版本：

This is MySQL version:

SELECT 
    orderNumber,
    ROUND(DATEDIFF(requiredDate, orderDate) / 7, 2), --Calculate by Week 
    ROUND(DATEDIFF(requiredDate, orderDate) / 30,2) --Calculate by Month
FROM
    orders

试试这篇文章中提供的解决方案 - sql - MySQL - 从前一行减去值，分组依据 [ ^ ]



而不是使用c.value - prev.value根据示例，使用 DATEDIFF （c.datevalue，prev.datevalue）。



您可能需要连接到子查询以获得总数。

Try the solutions presented on this post - sql - MySQL - Subtracting value from previous row, group by[^]

stead of using c.value - prev.value as per the example use DATEDIFF(c.datevalue, prev.datevalue).

You will probably have to do a join to a subquery to get the total.

这篇关于如何在mysql中的两个日期时间中获得差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！