63396

Format MySQL number to datetime?

Question:

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

Which returns:

Server: ServerA Type: server Boot Time: 2014-02-11 13:26:52 Last SNMP Poll: 2014-02-11 18:35:14

Answer1:

SELECT NOW() - INTERVAL 10329 SECOND

Answer2:

TIME_FORMAT(SEC_TO_TIME(uptime),'%Hh %im')

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 TIME_FORMAT method. <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>

Recommend

  • Ansible: setting user on dynamic ec2
  • How to obtain service credentials for a service instance created on IBM Bluemix without binding the
  • How to autoplay video in angularjs?
  • Laravel Eloquent integration in Codeigniter
  • Get current user from inside the model in Sails
  • C function strchr - How to calculate the position of the character?
  • Geom_jitter colour based on values
  • back button function for phonegap windows phone 7
  • Select value from xtype selection type checkbox CQ5
  • Cuda Clang and OS X Mavericks
  • Salesforce Different WSDL files and when to use
  • android.support.v7.widget.Toolbar VectorDrawableCompat IllegalStateException when using support lib
  • JqueryMobile Popup menu is not working
  • Reading a file into a multidimensional array
  • Android application: how to use the camera and grab the image bytes?
  • presentShareDialogWithParams posts to FB wall, but callback handler results say error
  • Time complexity of a program which involves multiple variables
  • Checking free space on FTP server
  • How to clear text inside text field when radio button is select
  • Cannot resolve symbol 'MyApi'
  • Scrapy recursive link crawler
  • How to recover from a Spring Social ExpiredAuthorizationException
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Deserializing XML into class C#
  • Adding custom controls to a full screen movie
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • -fvisibility=hidden not passed by compiler for Debug builds
  • How do I configure my settings file to work with unit tests?
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal