Skip to main content

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)

Popular posts from this blog

How to apply for a new ration card and what are the documents required?

List of documents required for new ration card application in Kerala and how to apply


Application for a new ration card should be addressed to Taluk Supply Officer (TSO) / City Rationing Officer (CRO) of applicant's residing area.

Primary document required are Residence certificate, Income certificate and incase the applicant's name is in another ration card then he/she should submit the reduction certificate ontained from previous TSO / CRO as proof for removing his/her name from old Ration card.

You can get the new application from your currently residing Taluk Supply Office.

Payback Points - How to redeem - How to merge multiple payback accounts - Block Payback card - Payback customer care

Joomla and Forum Integration - Integrating Forums to Joomla

Joomla is one of the most popular CMS opensource packages. It is very easier to develop website's using Joomla. You just need to download Joomla package from Joomla's Official website www.joomla.org  and install it on your domain and later adding customizations to templates and feature and Your website is ready :). Now a days most websites provides a forum section for it users for discussing various article topics, gathering opinions etc.



Following are some best know forum opensource packages which can be integrated with Joomla and create a new forum experience for users


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 »
Member
Search This Blog