CODEWITHSUNDEEP
phpmysqldatetimetimeformatting
A_Elric
A_Elric

Reputation: 3568

Get number of hours as an int since specific date in mysql query

So, I have an issue that I'm trying to wrap my head around. Essentially I have a database that shows when computers were last connected to the database. I am supposed to calculate the uptime of the computers as a percentage (hours connected since first connect / total hours since first connect.)

All I have is a single DateTime field in a mysql database. At that point I could do a "Select TIME from database where ID="1" order by TIME asc limit 1" to get the oldest date, but how would I get the number of hours since that DateTime object?

The desired output would be something like "95%" -- which could be 19 / 20 or something.

Thanks for the help!

Upvotes: 2

Views: 557

Answers (2)

raj-nt
raj-nt

Reputation: 149

use 

timestampdiff(HOUR,NOW(),OLDESTTIME) as uphours

that would give you difference in hours between two datetime

Upvotes: 4

OdinX
OdinX

Reputation: 4211

Convert your DateTime to a timestamp, subtract the oldest from the latest and divide the result by 60 and by 60 again:

(LatestTime-OldestTime)/60/60

Should give you the number of hours between them.

Upvotes: 0

Related Questions