How to convert the UNIX TIME into SQL datetime format

Question

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

Answer 1

In Mysql you can use from_unixtime() function to convert unix timestamp to Date:

select COUNT(DISTINCT devices) AS "Devices" from measure_tab where 
  measure_tab.time >= from_unixtime(1375243200) and 
  measure_tab.time < from_unixtime(1375315200);

Answer 2

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