mysql时间比较 [英] mysql time comparison

问题描述

我有job_start和job_end时间，并且timediff将给我时间差.现在，我想看看该工作是否花费了2小时30分钟以上.我该如何比较?如果这样做，我将收到错误消息:

I have job_start and job_end times and timediff will give me the time difference. Now I want to see if that job took more than 2 hrs 30 min. How do I compare it? I am getting errora if I do it like this:

timediff(job_start,job_end)> '2:30:00'    
timediff(job_start,job_end)> time(2:30:00)   
timediff(job_start,job_end)> time_format(2:30:00)

以上语法均无效.

推荐答案

从mysql文档获取功能

From mysql docs for function TIMESTAMPDIFF:

结果的单位(整数)由unit参数指定. 单位的合法值与TIMESTAMPADD()函数的说明中列出的值相同.

The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.

应为以下值之一:MICROSECOND(微秒)，SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER或YEAR.

Which should be one of the following values: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

如果是整数则为结果.我建议使用MINUTE:

Result if integer.I recommend MINUTE:

TIMESTAMPDIFF(MINUTE, job_start, job_end) > 150

(2 * 60 + 30)分钟= 150分钟= 2,5小时

(2 * 60 + 30) mins = 150 mins = 2,5 hours

这篇关于mysql时间比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！