Home   Best Sellers   Blogging   Coding & Design   Technology   SEO   Travel & living   Career   Videos   Tips   Online tools     
Home  »     »     »     »  Is there any MySQL function to return date from unix time stamp?

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

Tuesday, July 14, 2015

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.


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')

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)

Share this!

How to link to this page?
If you wish to link to this page from your website, simply Copy and paste the above HTML code to your web page. It will appear on your page as:
Is there any MySQL function to return date from unix time stamp?.

comments powered by Disqus

This Weeks 7 Popular Posts

Subscribe to Recent Posts by Email
Stay connected to CROZOOM with regular Email notices of new Techie articles and IT Jobs. Updates will be delivered to your Inbox as soon as they are posted online.

Enter Your Email Address:  

Delivered by FeedBurner   RSS Feed

Search this Blog   

Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to recruit.vo@gmail.com   Read more »