Is there any MySQL function to return date from unix time stamp?

In the DB table, when a record is inserted the timestamp is also inserted.
This time stamp is added in the insert query using the time() function.
When The records are listed currently the php date( $timestamp , "d/m/Y h:i:s" ) function is used to dhow the data back.

Is there any function in MYSQL which can  generate date from the unix timestamp stored in the table?


Answer is YES!!!! ;)

You can use the MySQL function FROM_UNIXTIME() for getting date from the unix timestamp in MySQL table.

Example

mysql> SELECT FROM_UNIXTIME(1436716249);
-> '2015-07-12 10:30:19'

If the time stamp is stored in the field "InsertTimeStamp" , then here is the sample query to access it:

mysql> SELECT
from_unixtime( InsertTimeStamp , '%Y %D %M %h:%i:%s')
FROM
your_table_name

Another Example:

mysql> Select OrderNo, ApplicantName , from_unixtime( AppEntryTimeStamp , '%Y %D %M %h:%i:%s %p %a') as AppTime from tbl_Orders order by OrderNo Desc

3      John      2015 12th July 10:50:49 AM Sun
2      Rocky      2015 10th May 11:25:11 AM Sun
1      Laura      2015 16th Jan 12:33:57 AM Sun

For date formating specifies in MySQL, please visit MySQL date formating.

Reference Links:

1. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime
2. http://php.net/manual/en/function.time.php
3. http://php.net/manual/en/function.date.php

Quick Notes:

PHP's time() function — Return the current Unix timestamp.
Format : int time ( void )

date() function in PHP will Format a local time/date
Format : string date ( string $format [, int $timestamp = time() ] )

MySQL's FROM_UNIXTIME() function Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.
Format : FROM_UNIXTIME(unix_timestamp)      OR    FROM_UNIXTIME(unix_timestamp,format)