The Unix Timestamp, while efficient for data storage, lacks human readability and appears as a set of integer numbers to users. To make it more user-friendly, it requires conversion into a readable format before displaying it to end-users.
If you don’t perform this conversion directly while selecting rows from the Database Table, you’ll need to handle it programmatically. For example, in PHP, you can utilize the date() function for conversion like so:
$timestamp = 1690768832;
echo "date time : ".date('d-M-Y H:i:s a',$timestamp);
The above code gives the following output:
date time : 31-Jul-2023 02:00:32 am
In this tutorial, we will explore how to convert Unix Timestamp values into a more readable Date Time format when selecting data from a MySQL Database Table. Let’s dive in and make the process of converting Unix Timestamps more accessible and comprehensible.
Table of Content
- Enabling and Storing Unix Timestamps in MySQL
- Converting Unix Timestamps to Readable Date Time using FROM_UNIXTIME()
- Customizing Date-Time Format
- Conclusion
1. Enabling and Storing Unix Timestamps in MySQL
Before diving into the conversion process, it’s crucial to ensure that Unix Timestamps can be properly enabled and stored in your MySQL Database Table.
You can skip to the next section if you already know this.
Create a Column:
Begin by creating a new column in your table to store the Unix Timestamp. Choose an appropriate data type that can accommodate integer values. Common data types for this purpose include `INT`, `MEDIUMINT`, and `BIGINT`. Ensure that you set the size of the column to at least 11 to accommodate the maximum number of digits in a Unix Timestamp.
timestamp int(11) NOT NULL
Getting the Current Unix Timestamp in PHP:
For developers using PHP within their projects, obtaining the current Unix Timestamp is made easy with the time() function.
Here’s how you can get the current Unix Timestamp:
$timestamp = time(); echo $timestamp; // 1690768832
The value of $timestamp will be the current Unix Timestamp, which is an essential step in recording time-based data. Once you have your table configured to store Unix Timestamps and are familiar with obtaining the current timestamp, let’s move on to the conversion process.
2. Converting Unix Timestamps to Readable Date Time using FROM_UNIXTIME()
Now that we have enabled and stored Unix Timestamps in our MySQL table, we can proceed to convert these timestamps into human-readable Date Time formats. To achieve this, we will utilize the powerful FROM_UNIXTIME() function provided by MySQL.
This function allows us to transform Unix Timestamps into more understandable date and time representations.
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
)
Inserted 3 entries on it.
| id | username | fullname | timestamp |
|---|---|---|---|
| 1 | yssyogesh | Yogesh singh | 1690769025 |
| 2 | sonarika | Sonarika Bhadoria | 1690954485 |
| 3 | vishal | Vishal Sahu | 1691917943 |
SELECT Query
SELECT username, FROM_UNIXTIME(timestamp) as timestamp FROM `users`
When you run this query this gives the following output –
| username | timestamp |
|---|---|
| yssyogesh | 2023-07-31 07:33:45 |
| sonarika | 2023-08-02 11:04:45 |
| vishal | 2023-08-13 14:42:23 |
3. Customizing Date-Time Format
Pass a second parameter to the FROM_UNIXTIME() function, you can define your desired format.
SELECT Query
In the query, we provided the custom format string directly within the FROM_UNIXTIME() function. The format string contains various placeholders representing different components of the date and time:
SELECT username, FROM_UNIXTIME(timestamp,'%a - %D %M %y %H:%i:%s') as timestamp FROM `user`
Output
| username | timestamp |
|---|---|
| yssyogesh | Sun – 30th May 21 01:34:53 |
| sonarika | Wed – 2nd August 23 11:04:45 |
| vishal | Sun – 13th August 23 14:42:23 |
Understanding the Format String
In the format string, I used the following placeholders to represent different parts of the date and time:
%a: Short weekday names (e.g., Sun, Mon, Tue)%D: Day of the month (e.g., 1st, 2nd, 3rd, 4th)%M: Full month name (e.g., January, February, March)%y: Year with two digits (e.g., 21 for 2021)%H: Hour in 24-hour format (00-23)%i: Minutes (00-59)%s: Seconds (00-59)
4. Conclusion
Understanding how to convert Unix Timestamps to human-readable Date Time formats using the FROM_UNIXTIME() function in MySQL is a valuable skill for developers and database administrators. By customizing the date and time representation directly within the SELECT queries, you can present data in a format that aligns with your application’s requirements.
This capability not only enhances user experience but also facilitates data analysis and improves the overall presentation of time-related information.
Embrace this knowledge to efficiently manage time data in your MySQL databases and create applications that are more user-friendly and visually appealing.
If you found this tutorial helpful then don't forget to share.