MySQL EXTRACT() 用法与实例

在 MySQL 中，EXTRACT() 函数用于从给定的日期/时间表达式中提取指定的部分，比如年、月、日、时、分、秒等。

发布于

在 MySQL 中，EXTRACT() 函数用于从给定的日期/时间表达式中提取指定的部分，比如年、月、日、时、分、秒等。

语法

您可以使用以下语法：

EXTRACT(unit FROM date)

date 是要从中提取的日期/时间表达式。

unit 是要提取的日期部分，它可以是以下值中的一个：

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

示例 1 - 年、月、日

要从日期中提取年、月、日，请使用以下语句：

SELECT
  '2023-01-31' AS 'Date',
  EXTRACT(YEAR FROM '2023-01-31') AS 'Year',
  EXTRACT(MONTH FROM '2023-01-31') AS 'Month',
  EXTRACT(DAY FROM '2023-01-31') AS 'Day';

输出：

+------------+------+-------+------+
| Date       | Year | Month | Day  |
+------------+------+-------+------+
| 2023-01-31 | 2023 |     1 |   31 |
+------------+------+-------+------+

示例 2 – 时、分、秒

要从时间中提取时、分、秒，请使用以下语句：

SELECT
  '10:11:12' AS 'Time',
  EXTRACT(HOUR FROM '10:11:12') AS 'Hour',
  EXTRACT(MINUTE FROM '10:11:12') AS 'Minute',
  EXTRACT(SECOND FROM '10:11:12') AS 'Second';

输出：

+----------+------+--------+--------+
| Time     | Hour | Minute | Second |
+----------+------+--------+--------+
| 10:11:12 |   10 |     11 |     12 |
+----------+------+--------+--------+

示例 3 – 更多

要从时间中提取年、月、日、时、分、秒，请使用以下语句：

SELECT
  '2023-01-31 10:11:12.123456' AS 'DateTime',
  EXTRACT(YEAR FROM '2023-01-31 10:11:12.123456') AS 'Year',
  EXTRACT(MONTH FROM '2023-01-31 10:11:12.123456') AS 'Month',
  EXTRACT(DAY FROM '2023-01-31 10:11:12.123456') AS 'Day',
  EXTRACT(HOUR FROM '2023-01-31 10:11:12.123456') AS 'Hour',
  EXTRACT(MINUTE FROM '2023-01-31 10:11:12.123456') AS 'Minute',
  EXTRACT(SECOND FROM '2023-01-31 10:11:12.123456') AS 'Second',
  EXTRACT(MICROSECOND FROM '2023-01-31 10:11:12.123456') AS 'Microseconds';

输出：

+----------------------------+------+-------+------+------+--------+--------+--------------+
| DateTime                   | Year | Month | Day  | Hour | Minute | Second | Microseconds |
+----------------------------+------+-------+------+------+--------+--------+--------------+
| 2023-01-31 10:11:12.123456 | 2023 |     1 |   31 |   10 |     11 |     12 |       123456 |
+----------------------------+------+-------+------+------+--------+--------+--------------+

示例 4 – 复合单位

MySQL EXTRACT() 支持一些复合单位：

SELECT
  '2023-01-31' AS 'Date',
  EXTRACT(YEAR_MONTH FROM '2023-01-31') AS 'Year Month';

输出：

+------------+------------+
| Date       | Year Month |
+------------+------------+
| 2023-01-31 |     202301 |
+------------+------------+