Find time difference between two dates

[Zaggs]

Hi Guys,

I have the following MySQL query and I would like to find the time difference in days, mins, hours between the current time and the "end_date" field (which is a datetime filed).

select auctions.end_date,domains.* from domains left join auctions on auctions.domain_id = domains.id where end_date > now() and active='1' order by end_date desc limit 10 

Any ideas how this can be achieved in the above query?

Thanks

[kornev]

Try for days:

round((end_date > now())/86400)

the same can be done for other part.

[Zaggs]

Try for days:

round((end_date > now())/86400)

the same can be done for other part.

Hmmm, I do not think this works. The query that I originally posted returns results, but when I insert round() it returns an empty set.

Any ideas?

[r937]

end_date > now() returns either 0 or 1, because it's either true or false

divide 0 or 1 by 86400 using integer arithmetic, and the answer is 0

zaggs, are you using an application language like php? please show how you would accomplish the formatting in that language

[Zaggs]

end_date > now() returns either 0 or 1, because it's either true or false

divide 0 or 1 by 86400 using integer arithmetic, and the answer is 0

zaggs, are you using an application language like php? please show how you would accomplish the formatting in that language

Hi, yes I am using php. You mean use something like date_format() ?

[r937]

something like? it would have to be a bit more than just a function

you want a time difference, right? well you can do that in mysql with this --

SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(end_date) as diff_seconds
  FROM ...

but then the tricky part is translating this into words

[Zaggs]

something like? it would have to be a bit more than just a function

you want a time difference, right? well you can do that in mysql with this --

SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(end_date) as diff_seconds
  FROM ...

but then the tricky part is translating this into words

Great stuff! That's all I needed

Thank you.