How to convert date to timestamp using UNIX_TIMESTAMP function in MySQL

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

A timestamp is a number that represents a date and time, and it is commonly used to store and process dates and times in computer systems. In MySQL, you can use the UNIX_TIMESTAMP function to convert a date to a timestamp. This article will introduce how to use the UNIX_TIMESTAMP function to achieve this conversion.

First, we need to understand the usage of the UNIX_TIMESTAMP function. The UNIX_TIMESTAMP function takes a UNIX-style date (the number of seconds since 00:00:00 on January 1, 1970) as a parameter and returns a corresponding timestamp. The following is the syntax of the UNIX_TIMESTAMP function:

UNIX_TIMESTAMP(date)

Among them, date is a date type parameter, which can be DATE, DATETIME, TIMESTAMP, YEAR, YEAR_MONTH or YEAR_MONTH_DAY type. The UNIX_TIMESTAMP function converts the date parameter to seconds and returns a timestamp.

Next, we use a few examples to demonstrate how to use the UNIX_TIMESTAMP function to convert a date to a timestamp.

Example 1: Convert a date type field to a timestamp

Suppose we have a table named orders, which contains a date type field order_date. We can use the UNIX_TIMESTAMP function to convert the value of the order_date field into a timestamp and query it. The following is the sample code:

SELECT UNIX_TIMESTAMP(order_date) AS timestamp FROM orders;

Example 2: Convert a string type date to a timestamp

Sometimes, we You may need to convert a string date to a timestamp. In MySQL, you can use the STR_TO_DATE function to convert a string type date to a date type, and then use the UNIX_TIMESTAMP function to convert it to a timestamp. The following is the sample code:

SELECT UNIX_TIMESTAMP(STR_TO_DATE('2021-01-01', '%Y-%m-%d')) AS timestamp;

In the above example , we convert the string '2021-01-01' to a date type, and then use the UNIX_TIMESTAMP function to convert it to a timestamp.

Example 3: Convert date and time type fields to timestamps

In addition to date types, MySQL also provides date and time types (such as DATETIME and TIMESTAMP). We can also use the UNIX_TIMESTAMP function to convert these types of fields into timestamps. The following is the sample code:

SELECT UNIX_TIMESTAMP(create_datetime) AS timestamp FROM orders;

In this example, we convert the value of the create_datetime field into a timestamp.

To summarize, by using the UNIX_TIMESTAMP function in MySQL, we can convert dates into timestamps. Whether it is a date type, a string type date, or a date and time type field, we can use the UNIX_TIMESTAMP function to achieve this conversion. I hope this article can help you deal with date conversion issues in MySQL.

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