CODEWITHSUNDEEP
mysqlsqldatediff
rym
rym

Reputation: 545

How to calculate difference between two dates

I want to calculate difference between two dates (using SQL Request) with this format "YYYY-MM-DD HH:mm:ss",the result should be in hours/minutes.

example: "2011-08-02 18:00:00" - "2011-08-03 09:01:00" = 15h and 1 min

1/I haven't found a function that calculate what I want ,just found TIMEDIFF() and DATEDIFF()

2/Also my request don't work because the subquery return more than one row ,I have tried to calculate with datediff to only test the request

select sum(DATEDIFF((select changegroup.CREATED
from changegroup,changeitem
where changegroup.id=changeitem.groupid
and oldString='Closed'
and changegroup.issueid=10301),
(select changegroup.CREATED
where changegroup.id=changeitem.groupid
and newString='Closed'
and changegroup.issueid=10301))) as somme
from changegroup,changeitem;

How can I calculate the difference between two dates and how can I fix my sql request?

thank you

Upvotes: 0

Views: 1751

Answers (1)

Paolo Falabella
Paolo Falabella

Reputation: 25844

  1. the following returns "15h and 01 min" which I think is the format you wanted
SELECT Time_format(Timediff('2011-08-03 09:01:00', '2011-08-02 18:00:00'),
              '%Hh and %i min')
  1. hard to answer without knowing more about the two tables you're querying. Could you expand your question a little?

Upvotes: 2

Related Questions