php mysql timediff 到小时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2174058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql timediff to hours
提问by momo
I'm Trying to get the timediff from my table and convert it to hours (it's for an hourly billed service)
我正在尝试从我的表中获取 timediff 并将其转换为小时（这是按小时计费的服务）
SELECT TIME_TO_SEC(TIMEDIFF(endDate,startDate))/3600 FROM tasks >
SELECT TIME_TO_SEC(TIMEDIFF(endDate,startDate))/3600 FROM 任务 >
where endDate and startDate are in datetime format
其中 endDate 和 startDate 采用日期时间格式
is there another way (more efficient) to do this task? Thanks !
有没有另一种方法（更有效）来完成这项任务？谢谢 ！
回答by Trav L
TIMEDIFF(endDate, startDate)outputs in DateTime format, so flat that to timestamp and devide by (60*60)
TIMEDIFF(endDate, startDate)以 DateTime 格式输出，如此平坦以至于时间戳和除以 (60*60)
SELECT (UNIX_TIMESTAMP(TIMEDIFF(endDate, startDate))/(60*60)) AS hours_difference
FROM tasks
Edit:Alternatively,TimestampDiffmay also provide a valid solution in more elegant way providing its example:
编辑：或者，TimestampDiff也可以通过提供其示例以更优雅的方式提供有效的解决方案：
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
And your solution can be:
您的解决方案可以是：
SELECT TIMESTAMPDIFF(HOUR, startDate, endDate) AS hours_different
FROM tasks
回答by Funk Doc
NOTE the most voted up answer in this chain is INCORRECT! Using HOUR will only return hours as an integer. Below would correct the most popular answer to return hours as an integer and minutes as a decimal (ie 6.5 hours).
注意此链中投票最多的答案是不正确的！使用 HOUR 只会以整数形式返回小时数。下面将更正最流行的答案，将小时数返回为整数，将分钟数返回为小数（即 6.5 小时）。
TIME_TO_SEC(TIMEDIFF(endDate, startDate))/3600 as hours
回答by Joey
HOUR(TIMEDIFF(endDate, startDate))
might work—if I'm reading the docs correctly.
可能有用——如果我正确阅读文档。
回答by Ralf Strehle
TIMESTAMPDIFF(HOUR, startDate, endDate)
is the best way to do it, because it works with big time intervals, like
是最好的方法，因为它适用于大的时间间隔，比如
TIMESTAMPDIFF(HOUR, "2012-01-01 00:00:00", "2050-01-01 00:00:00")
Result: 333120
结果： 333120
while
尽管
HOUR(TIMEDIFF("2050-01-01 00:00:00", "2012-01-01 00:00:00"))
Result: 838
结果： 838
fails.
失败。
As we see in the example above, it surprisingly even works beyond the timestamp limitation in year 2038.
正如我们在上面的示例中看到的那样，它甚至超出了 2038 年的时间戳限制。
The maximum hours which are returned by HOUR(TIMEDIFF(dateEnd, dateStart))is 838, because TIMEDIFFis limited to the range allowed for TIMEvalues.
返回的最大小时数HOUR(TIMEDIFF(dateEnd, dateStart))为 838，因为TIMEDIFF受限于TIME值允许的范围。
回答by Donny Kurnia
You can use UNIX_TIMESTAMPto do the calculation in SELECT query.
您可以使用UNIX_TIMESTAMP在 SELECT 查询中进行计算。
SELECT (UNIX_TIMESTAMP(endDate)-UNIX_TIMESTAMP(startDate))/3600 hour_diff
FROM tasks
UNIX_TIMESTAMPconvert datetime to number of second from epoch. You can substract both timestamp to get difference in second. Divide it with 3600 will give you difference in hour.
UNIX_TIMESTAMP将日期时间转换为纪元的秒数。您可以减去两个时间戳以获得秒的差异。将它除以 3600 会给您带来不同的小时数。
回答by Rik
for getting Diffrence in Hour :
以小时为单位获得差异：
Hour(TIMEDIFF(date1,date2)) as Hour1
for getting Diffrence in Minute :
在 Minute 中获得差异：
Minute(TIMEDIFF(date1,date2)) as Minute1
for getting Diffrence in Second :
为了在 Second 中获得差异：
Second(TIMEDIFF(date1,date2)) as Second1
回答by momo
eg: startDate 2010-01-31 00:00:00, endDate 2010-01-31 19:24:22
例如：开始日期 2010-01-31 00:00:00，结束日期 2010-01-31 19:24:22
SELECT (UNIX_TIMESTAMP(dateFin)-UNIX_TIMESTAMP(dateDebut))/3600 hour_diff
FROM tasks
SELECT TIME_TO_SEC(TIMEDIFF(endDate,startDate))/3600
FROM tasks
returns 19.4061 which is good
返回 19.4061 这很好
SELECT TIMESTAMPDIFF(HOUR, endDate, startDate) AS hours_different
FROM tasks
Only returns hours while i also need minutes to be converted.
只返回小时，而我还需要转换分钟。
SELECT (UNIX_TIMESTAMP(TIMEDIFF(endDate, startDate))/(60*60)) AS hours_difference
FROM tasks
returns 0. I think the first one is the most efficent. Thanks !!
返回 0。我认为第一个是最有效的。谢谢 ！！
回答by Charif DZ
select hour(timediff('2017-01-01 00:00:00', '2017-01-01 00:01:00'));
return 1
select hour(timediff('2017-01-01 00:01:00', '2017-01-01 00:00:00'));
return 1
select time_to_sec(timediff('2017-01-01 00:00:00', '2017-01-01 00:00:05'));
return -5
select time_to_sec(timediff('2017-01-01 00:00:00', '2017-01-01 00:00:05'))/60 as 'minutes' ;
return -0.0833
if you have the seconds you can convert it as you want or what and hours(..) will not use it when i compare because it's always postive value
如果你有秒数，你可以根据需要转换它，或者什么和 hours(..) 在我比较时不会使用它，因为它总是正值
回答by Hisham shahid
you can use mysql funcion timestampdiff like below
你可以像下面这样使用mysql函数timestampdiff
TIMESTAMPDIFF(HOUR, '2018-06-01 00:00:00', '2018-06-01 12:00:00')
回答by goat
TIMEDIFF(endDate, startDate) / 10000
