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.

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.

SELECT Query

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

When you run this query this gives the following output –

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

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.