Convert unix timestamp to date sql
Get via App Store Read this post in our app!
How to convert the UNIX TIME into SQL datetime format
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?
You can use function as below:
In Mysql you can use from_unixtime() function to convert unix timestamp to Date :
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
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x')
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:
if you need to find out how to check/set the time zone.