开发者

How do I get the difference between two datetimes in MYSQL?

开发者 https://www.devze.com 2023-02-17 09:39 出处:网络
I\'m trying this SELECT DATEDIFF(second,log.start_time,log.end_time) as seconds from log where log.log_id = \"some crazy UUID\";

I'm trying this

SELECT DATEDIFF(second,log.start_time,log.end_time) 
as seconds 
from log 
where log.log_id = "some crazy UUID";

but I get an error because DATEDIFF() accepts no formatter expr like second.

So I tried,

SELECT second(DATEDIFF开发者_如何学运维(second,log.start_time,log.end_time)) 
as seconds 
from log
where log.log_id = "some crazy UUID";

But that doesn't work for formatting either.


I think you meant to use TIMESTAMPDIFF() instead of DATEDIFF():

SELECT TIMESTAMPDIFF(second,log.start_time,log.end_time) as seconds 
from log 
where log.log_id = "some crazy UUID";


Try this

SELECT TIME_TO_SEC( TIMEDIFF( log.start_time, log.end_time )) AS seconds
FROM log 
WHERE log.log_id = "some crazy UUID";


DATEDIFF accept only two parameters and you are passing three parameters here.

How it works and also it returns only the difference b/w dates not including time.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html


DATEDIFF() returns days of difference.

Use TIMEDIFF() or convert both timestamps with TO_SECONDS()) and get their difference:

SELECT TO_SECONDS(log.end_time) - TO_SECONDS(log.start_time) AS seconds
  FROM log 
  WHERE log.log_id = "some crazy UUID"
;


DATEDIFF :

From the msdn, it returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate. The DATEDIFF() function returns the time between two dates. Syntax :

DATEDIFF ( datepart , startdate , enddate )  

You should use TIMEDIFF() or TIMESTAMPDIFF() function.
Just replace TIMESTAMPDIFF() function with DATEFDIFF()

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号