How to extract a specified part of a datetime using MySQL's EXTRACT function

How to use MySQL's EXTRACT function to extract the specified part of the date and time

MySQL database is one of the most commonly used relational databases and is widely used in various types of applications. Date and time are common data types in databases, and it is often necessary to extract specific parts from date and time fields to meet business needs. In MySQL, you can use the EXTRACT function to extract a specified part from a datetime. This article will introduce how to use MySQL's EXTRACT function to extract the specified part of the date and time, and provide some code examples.

EXTRACT function is a function in MySQL used to extract a specified part from a date, time, or datetime. The basic syntax is as follows:

EXTRACT(unit FROM date)

Among them, unit is the keyword that specifies the date and time part to be extracted, and date is the date and time value to be extracted.

The following are some commonly used unit keywords and their corresponding date and time parts:

• YEAR: Year
• MONTH: Month
• DAY :Date
• HOUR:Hour
• MINUTE:Minute
• SECOND:Second

Now let’s go through some code examples to illustrate how to use EXTRACT function.

Example 1: Extract the year

Suppose we have a table named orders, which has a datetime field representing the date and time of the order. Now we want to extract the year of all orders.

SELECT EXTRACT(YEAR FROM datetime) AS year
FROM orders;

The above code will extract the year of the datetime field from the orders table and return it as the alias year.

Example 2: Extract the month

Next, let’s assume we want to extract the month of the order.

SELECT EXTRACT(MONTH FROM datetime) AS month
FROM orders;

The above code will extract the month of the datetime field from the orders table and return it as the alias month.

Example 3: Fetching Date

Now, let’s say we want to fetch the date of the order.

SELECT EXTRACT(DAY FROM datetime) AS day
FROM orders;

The above code will extract the date of the datetime field from the orders table and return it as the alias day.

Example 4: Extract hours

Suppose we have a table named logs, which has a timestamp field representing the timestamp of the log. Now we want to extract the hours of all logs.

SELECT EXTRACT(HOUR FROM timestamp) AS hour
FROM logs;

The above code will extract the hour of the timestamp field from the logs table and return it as an alias hour.

Example 5: Extract minutes

Suppose we want to extract the minutes of the log.

SELECT EXTRACT(MINUTE FROM timestamp) AS minute
FROM logs;

The above code will extract the minutes of the timestamp field from the logs table and return it as the alias minute.

Example 6: Extract seconds

Finally, let’s say we want to extract the seconds of the log.

SELECT EXTRACT(SECOND FROM timestamp) AS second
FROM logs;

The above code will extract the seconds of the timestamp field from the logs table and return it as the alias second.

The above is an example of using MySQL's EXTRACT function to extract the specified part of the date and time. By using the EXTRACT function, we can flexibly extract the required parts from the datetime field to meet different business needs. I hope this article will help you understand and apply the EXTRACT function!

The above is the detailed content of How to extract a specified part of a datetime using MySQL's EXTRACT function. For more information, please follow other related articles on the PHP Chinese website!