How to use UNIX_TIMESTAMP function in MySQL to convert date to timestamp

How to use the UNIX_TIMESTAMP function in MySQL to convert a date to a timestamp

In MySQL, the UNIX_TIMESTAMP function is a very commonly used function, which is used to convert a date time into a timestamp. A timestamp is an integer representation of date and time that represents the number of seconds since January 1, 1970. Use the UNIX_TIMESTAMP function to easily convert dates into timestamps, which is very convenient when processing date and time related data.

In MySQL, the syntax of the UNIX_TIMESTAMP function is as follows:

UNIX_TIMESTAMP(date)

Among them, date is a date and time value or expression. The UNIX_TIMESTAMP function converts this datetime value into the corresponding timestamp.

The following code examples demonstrate how to use the UNIX_TIMESTAMP function in MySQL to convert dates to timestamps.

First, we can use the CURDATE function to get the current date and convert it to a timestamp:

SELECT UNIX_TIMESTAMP(CURDATE());

The above code will return the timestamp of the current date.

Next, we can use the STR_TO_DATE function to convert a string in a specific format into a date time and convert it into a timestamp. Suppose there is a string "2022-01-01 12:00:00", we can use the following code to convert it into a timestamp:

SELECT UNIX_TIMESTAMP(STR_TO_DATE('2022-01-01 12:00:00', '%Y-%m-%d %H:%i:%s'));

In the above code, the STR_TO_DATE function converts the string into a date time , %Y-%m-%d %H:%i:%s is the date and time format. The UNIX_TIMESTAMP function then converts this datetime value into a corresponding timestamp.

In addition to using the CURDATE function and STR_TO_DATE function, we can also use other date and time related functions, such as NOW function, DATE function, etc. to obtain the date and time and convert it into a timestamp. The following example shows how to use the NOW function to get the current date and time and convert it into a timestamp:

SELECT UNIX_TIMESTAMP(NOW());

The above code will return the timestamp of the current date and time.

In addition, in practical applications, sometimes we need to convert the date and time into a timestamp and then perform some calculations, such as calculating the time difference between two dates and times. The following example shows how to calculate the difference in seconds between two date times:

SELECT UNIX_TIMESTAMP('2022-01-01 12:00:00') - UNIX_TIMESTAMP('2022-01-01 10:00:00');

The above code will return the difference in seconds between two date times.

In summary, it is very simple to use the UNIX_TIMESTAMP function to convert a date to a timestamp in MySQL. Whether we are using the CURDATE function to get the current date and time, or using the STR_TO_DATE function to convert a string to a date and time, we can conveniently convert it to a timestamp through the UNIX_TIMESTAMP function. When processing date and time related data, using the UNIX_TIMESTAMP function can simplify operations and improve efficiency.

The above is an introduction and code example on how to use the UNIX_TIMESTAMP function to convert dates to timestamps in MySQL. Hope this helps!

The above is the detailed content of How to use UNIX_TIMESTAMP function in MySQL to convert date to timestamp. For more information, please follow other related articles on the PHP Chinese website!