MySQL – Convert Unix Timestamp to Date Time

I think you already know that the Unix Timestamp is not Human readable values it is just set of Integer numbers to the user when you show it on the screen.

Because of this reason we need to convert it before present it to the user.

If you do not convert the value while selecting rows from the Database Table then you are doing this programmatically after SELECT.

In the PHP you can use the date() function for converting.

$timestamp = 1476956996;
echo "date time : ".date('d-M-Y H:i:s a',$timestamp);

This gives the following output –

date time : 20-Oct-2016 11:49:56 am

In simple terms, the Unix timestamp stores the current Date Time in Integer format.

In this short tutorial, I show how you can convert Unix Timestamp value to readable Date Time format when you select data from the MySQL Database Table.

MySQL – Convert Unix Timestamp to Date Time


Contents

  1. How to make field store Unix timestamp value ?
  2. Select and Convert
  3. Specify Date format
  4. Conclusion

1. How to make field store Unix timestamp value ?

If you know how to enable and store Timestamp then you can skip to next section.

For this, you need to set or create a column in your Table which able to take Integer value (e.g. DataTypes – INT,MEDIUMINT, BIGINT) and set its size to 11 or more.

If you are using PHP within your project then you can use the time() function to get current Unix Timestamp.

$timestamp = time();
echo $timestamp;    // 1477015564

2. Select and Convert

For converting a timestamp you can use FROM_UNIXTIME.

Syntax – 

FROM_UNIXTIME( timestamp[, format] )

Parameters

  • The first parameter is your Timestamp value or field name.
  • This is an optional parameter which you can use to set your specific format.

If you are using only one parameter then it will return value in 'YYYY-MM-DD HH:MM:SS' format.

Example

For demonstration, I am using users table.

Table Structure

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  'fullname' varchar(60) NOT NULL,
  'timestamp' int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Inserted 3 entries on it.

id username fullname timestamp
1 yssyogesh Yogesh singh 1476928222
2 sonarika Sonarika Bhadoria 1476929693
3 vishal Vishal Sahu 1476930908

SELECT Query

SELECT 
username,
FROM_UNIXTIME(timestamp) as timestamp 
FROM `users`

When you run this query this gives the following output –

username timestamp
yssyogesh 2016-10-20 07:20:22
sonarika 2016-10-20 07:44:53
vishal 2016-10-20 08:05:08

 


3. Specify Date format

For changing format, you need to specify the second parameter in the function.

SELECT Query

SELECT 
username,
FROM_UNIXTIME(timestamp,'%a - %D %M %y %H:%i:%s') as timestamp 
FROM `user`

Output

username timestamp
yssyogesh Thu – 20th October 16 07:20:22
sonarika Thu – 20th October 16 07:44:53
vishal Thu – 20th October 16 08:05:08

 


4. Conclusion

Now you know how to convert your timestamp field to specific Date Time format within your SELECT query and you can use it directly in your program.

 

Spread the love

Related Post

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *