How To Convert The Unix Time Into Sql Datetime Format

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

Solution 1:

You can use function as below:

selectFROM_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

selectCOUNT(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 >=1375243200and 
  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

Solution 2:

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

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

Solution 3:

you could use FROM_UNIXTIME inside DATE_FORMAT, but luckily, FROM_UNIXTIME also accepts a format string, so you could just use it by itself

Like this

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x')

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

Solution 4:

As detailed in the other answers, FROM_UNIXTIME is the function you are looking for. Please be aware that this implicitly takes into account the local time zone setting on the machine running MySQL.

There's lots of useful information here:

Should MySQL have its timezone set to UTC?

if you need to find out how to check/set the time zone.