I have a MySQL database show uptime for devices. The uptime is how long the device has been up in seconds. I have a query showing all devices with uptime less than 86400 (24 hours) which I want to bring into a PHP page.
I want to format this uptime string number into a datetime that's human readable. Here is my query:
select hostname,type,uptime from devices where uptime < 86400 And here is sample output: hostname: serverA type: server uptime: 10329
How can I turn that 10329 into an actual time? I assume there is some sort of value that can pull the current time in seconds, minus that uptime value, and convert that into MM/DD/YYYY HH:MM:SS readable value?
Any help is great appreciated!
EDIT: Decided to use both answer below, but here is the query that fits my needs:
SELECT hostname AS Server,type AS Type,NOW() - INTERVAL uptime SECOND AS "Boot Time",last_polled AS "Last SNMP Poll" FROM devices WHERE uptime < 86400
Server: ServerA Type: server Boot Time: 2014-02-11 13:26:52 Last SNMP Poll: 2014-02-11 18:35:14Answer1:
SELECT NOW() - INTERVAL 10329 SECOND
Google: <a href="https://stackoverflow.com/questions/8193868/convert-seconds-to-human-readable-time-duration" rel="nofollow">Convert seconds to human readable time duration</a>
select hostname,type,TIME_FORMAT(SEC_TO_TIME(uptime),'%Hh %im') from devices where uptime < 86400
This will give you Hours and minutes of uptime. You can add seconds in as well if you want by reading up on the
<a href="http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_time-format" rel="nofollow">http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_time-format</a>