How to convert the UNIX TIME into SQL datetime format

datetime, mysql, sql, timestamp

Solution

You can use function as below:

select FROM_UNIXTIME(UNIX_TIMESTAMP(),'%a %b %d %H:%i:%s UTC %Y');

output will be:

'Wed Feb 05 05:36:16 UTC 2014'

In your query

select COUNT(DISTINCT devices) AS "Devices",
  FROM_UNIXTIME(measure_tab.time,'%a %b %d %H:%i:%s UTC %Y') as d from measure_tab where 
  measure_tab.time >= 1375243200 and 
  measure_tab.time < 1375315200;

For more info you can check documentation: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

You can see sql fiddle:http://sqlfiddle.com/#!2/a2581/20357

Problem

``` select COUNT(DISTINCT devices) AS "Devices" from measure_tab where measure_tab.time >= 1375243200 and measure_tab.time < 1375315200; ``` The output of the above sql query gives a table with a column named "Devices" with number of devices. I want the time which is one of the attributes of measure_tab should also get displayed in another column of the output table with the UNIX TIME which is 1375243200 in this query converted into the SQL datetime format which is `Thu Aug 1 00:00:00 UTC 2013`. Can someone help me out? Thanks

Original source

Related problems