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 ?
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.
Be First to Comment