MySQL:将相对秒数转换为日期时间? [英] MySQL: Convert relative seconds count to datetime?

问题描述

我有一个MySQL数据库显示设备的正常运行时间.正常运行时间是指设备已启动多长时间(以秒为单位).我有一个查询，显示所有我想带入PHP页面的设备，其正常运行时间少于86400(24小时).

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

如何将10329转换为实际时间?我假设有某种值可以拉动当前时间(以秒为单位)，减去正常运行时间值，然后将其转换为MM/DD/YYYY HH:MM:SS可读值?

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?

非常感谢您的帮助！

决定使用以下两个答案，但这是符合我需要的查询:

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:14

推荐答案

SELECT NOW() - INTERVAL 10329 SECOND

这篇关于MySQL:将相对秒数转换为日期时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！