MySQL – Convert Unix Timestamp to Date Time

The Unix Timestamp is not Human readable values it is a set of Integer numbers to the user when it is been shown on the screen.

Because of this reason, it needs to convert before showing it to the user.

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

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

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

This gives the following output –

date time : 30-May-2021 21:59:16 pm

In this tutorial, I show how you can convert Unix Timestamp value to readable Date Time format when 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 the next section.

For this, you need to set or create a column in your Table which able to take an 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 the current Unix Timestamp.

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

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 that 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 the demonstration, I am using the 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 the 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 a specific Date Time format within your SELECT query and you can use it directly in your program.