While working with MySQL, understanding the use case of the “UNIX_TIMESTAMP()” function is crucial for managing date and time data efficiently. This function allows you to convert date and time values into the “UNIX” timestamp format, which is a representation of time in seconds since January 1, 1970. The “UNIX_TIMESTAMP()” function provides flexibility in retrieving the current UNIX timestamp, converting specific dates and times, and so on.
This guide will explain how to use the “UNIX_TIMESTAMP()” function in MySQL.
How to Use UNIX_TIMESTAMP() Function in MySQL?
To use the “UNIX_TIMESTAMP()” function, execute the MySQL query within the MySQL environment, such as the command line tool or a GUI interface. After logging in, use the syntax below to convert a date-time value to the “UNIX” format using the “UNIX_TIMESTAMP()” function
In the above syntax, “[DateTime_value]” represents the specific date-time value that you want to convert into the UNIX time format.
Let’s move to the examples to understand how to use the “UNIX_TIMESTAMP()” function in MySQL.
Example 1: Retrieve the Current UNIX timestamp
To retrieve the current time in “UNIX” format, use the only “UNIX_TIMESTAMP()” function with the “SELECT” statement as shown in the below example:
Output
The output showed the current time in “UNIX” time format.
Alternatively, the current time in “UNIX” format can also be fetched by passing the “NOW()” function as an argument of the “UNIX_TIMESTAMP()” function:
Output
The output displayed the current time in “UNIX” format.
Example 2: Convert a Specific Date and Time to a UNIX Timestamp
To convert a specific date-time value to a “UNIX” timestamp, provide the desired date-time value as an argument to the “UNIX_TIMESTAMP()” function. An example of converting a static date-time value is given below:
In the above example, the static date-time value is “2023-05-10 10:25:00”.
Output
The output depicts the UNIX timestamp of the given static date-time value.
Example 3: Convert a Date and Time Stored in a Column to a UNIX Timestamp
To convert the date-time value stored in a table to a “UNIX” timestamp, you can utilize the “SELECT” statement with the column name of the particular table as an argument of the “UNIX_TIMESTAMP()” function. An example to convert the date-time value of the “joineddate” column of the “lh_data” table is provided below:
Output
The output showed the date-time values of the “joineddate” column in “UNIX” time format.
Example 4: Subtract 1 Hour From the Current UNIX Timestamp
Any specific duration such as seconds, minutes, hours, etc., can be added or subtracted from the date-time value that is in “UNIX” format. An example of subtracting “1 hr.” or “3600 seconds” from the current “UNIX” time is given below:
Output
The output displayed the current time in UNIX format after subtracting 1 hr. from it.
How to Convert a UNIX Timestamp to a Readable Date and Time Format?
To convert a UNIX time format into a readable date-time format, the “FROM_UNIXTIME()” function can be utilized. The “FROM_UNIXTIME()” function takes a “UNIX” format time value as an argument and converts it into the readable date-time format as shown in the given below example:
Output
The output showed that the UNIX time has been converted into the readable time format.
Conclusion
The “UNIX_TIMESTAMP()” function in MySQL allows us to convert date and time values into the “UNIX” time format. The stated function has various use cases, such as retrieving the current UNIX timestamp and converting a specific date-time or the date-time values stored in a table column to the UNIX time format, by utilizing the “UNIX_TIMESTAMP()” function with a “SELECT” statement. Furthermore, you can manipulate UNIX timestamps by adding or subtracting specific durations as explained in this article.
