CODEWITHSUNDEEP
mysqldatetimetimedatediff
davidahines
davidahines

Reputation: 4094

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

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.

Upvotes: 2

Views: 5593

Answers (5)

Saurabh Gokhale
Saurabh Gokhale

Reputation: 46425

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()

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

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"
;

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65587

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";

Upvotes: 6

ajm
ajm

Reputation: 13223

Try this 

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

Upvotes: 3

Shakti Singh
Shakti Singh

Reputation: 86476

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

Upvotes: 2

Related Questions