MySQL – Unix Timestamp

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.