How to Use UNIX_TIMESTAMP() Function in MySQL?

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:




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:




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:

SELECT UNIX_TIMESTAMP('2023-05-10 10:25:00');


In the above example, the static date-time value is “2023-05-10 10:25:00”.


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:

SELECT name, UNIX_TIMESTAMP(joineddate) FROM lh_data;



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:




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:




The output showed that the UNIX time has been converted into the readable time format.


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.

About the author

Danish Ghafoor

I am a computer science graduate with a certification in cybersecurity. My passion for technology has led me to develop a deep understanding of the challenges and solutions in the field of information security. I am dedicated to staying up-to-date with the latest industry trends and technologies, and constantly seeking out new opportunities to expand my knowledge and skills.