MySQL EXTRACT函数

时间:2019-11-20 08:52:22  来源:igfitidea点击:

简介:在本教程中,您将学习如何使用MySQL EXTRACT()函数提取DATE或DATETIME值的一部分。

MySQL EXTRACT()函数简介

EXTRACT()函数提取日期的一部分。
下面说明了EXTRACT()函数的语法。

EXTRACT(unit FROM date)

EXTRACT()函数需要两个参数unit和date。

单位是要从日期提取的间隔。
以下是unit参数的有效间隔。

  • DAY_HOUR
  • DAY_MICROSECOND
  • DAY_MINUTE
  • DAY_SECOND
  • 小时
  • HOUR_MICROSECOND
  • HOUR_MINUTE
  • HOUR_SECOND
  • 微秒
  • 分钟
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • 25美分硬币
  • 第二
  • SECOND_MICROSECOND
  • YEAR_MONTH

日期是您从中提取间隔的DATE或DATETIME值。

MySQL EXTRACT函数示例

从日期时间中提取日期:

mysql> SELECT EXTRACT(DAY FROM '2017-07-14 09:04:44') DAY;
+------+
| DAY  |
+------+
|   14 |
+------+
1 row in set (0.00 sec)

从日期时间中提取day_hour:

mysql> SELECT EXTRACT(DAY_HOUR FROM '2017-07-14 09:04:44') DAYHOUR;
+---------+
| DAYHOUR |
+---------+
|    1409 |
+---------+
1 row in set (0.00 sec)

从日期时间提取day_microsecond:

mysql> SELECT EXTRACT(DAY_MICROSECOND FROM '2017-07-14 09:04:44') DAY_MS;
+----------------+
| DAY_MS         |
+----------------+
| 14090444000000 |
+----------------+
1 row in set (0.00 sec)

从日期时间中提取day_minute:

mysql> SELECT EXTRACT(DAY_MINUTE FROM '2017-07-14 09:04:44') DAY_M;
+--------+
| DAY_M  |
+--------+
| 140904 |
+--------+
1 row in set (0.00 sec)

从日期时间中提取day_second

mysql> SELECT EXTRACT(DAY_SECOND FROM '2017-07-14 09:04:44') DAY_S;
+----------+
| DAY_S    |
+----------+
| 14090444 |
+----------+
1 row in set (0.00 sec)

从日期时间中提取小时:

mysql> SELECT EXTRACT(HOUR FROM '2017-07-14 09:04:44') HOUR;
+------+
| HOUR |
+------+
|    9 |
+------+
1 row in set (0.00 sec)

从日期时间中提取hour_microsecond:

mysql> SELECT EXTRACT(HOUR_MICROSECOND FROM '2017-07-14 09:04:44') HOUR_MS;
+-------------+
| HOUR_MS     |
+-------------+
| 90444000000 |
+-------------+
1 row in set (0.00 sec)

从日期时间中提取hour_minute:

mysql> SELECT EXTRACT(HOUR_MINUTE FROM '2017-07-14 09:04:44') HOUR_M;
+--------+
| HOUR_M |
+--------+
|    904 |
+--------+
1 row in set (0.00 sec)

从日期时间中提取hour_second:

mysql> SELECT EXTRACT(HOUR_SECOND FROM '2017-07-14 09:04:44') HOUR_S;
+--------+
| HOUR_S |
+--------+
|  90444 |
+--------+
1 row in set (0.00 sec)

从日期时间提取微秒:

mysql> SELECT EXTRACT(MICROSECOND FROM '2017-07-14 09:04:44') MICROSECOND;
+-------------+
| MICROSECOND |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

从日期时间中提取分钟:

mysql> SELECT EXTRACT(MINUTE FROM '2017-07-14 09:04:44') MINUTE;
+--------+
| MINUTE |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

从日期时间中提取minute_microsecond:

mysql> SELECT EXTRACT(MINUTE_MICROSECOND FROM '2017-07-14 09:04:44') MINUTE_MS;
+-----------+
| MINUTE_MS |
+-----------+
| 444000000 |
+-----------+
1 row in set (0.00 sec)

从日期时间中提取minute_second:

mysql> SELECT EXTRACT(MINUTE_SECOND FROM '2017-07-14 09:04:44') MINUTE_S;
+----------+
| MINUTE_S |
+----------+
|      444 |
+----------+
1 row in set (0.00 sec)

从日期时间中提取月份:

mysql> SELECT EXTRACT(MONTH FROM '2017-07-14 09:04:44') MONTH;
+-------+
| MONTH |
+-------+
|     7 |
+-------+
1 row in set (0.00 sec)

从日期时间中提取季度:

mysql> SELECT EXTRACT(QUARTER FROM '2017-07-14 09:04:44') QUARTER;
+---------+
| QUARTER |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

从日期时间中提取第二个:

mysql> SELECT EXTRACT(SECOND FROM '2017-07-14 09:04:44') SECOND;
+--------+
| SECOND |
+--------+
|     44 |
+--------+
1 row in set (0.00 sec)

从日期时间中提取second_microsecond:

mysql> SELECT EXTRACT(SECOND_MICROSECOND FROM '2017-07-14 09:04:44') SECOND_MS;
+-----------+
| SECOND_MS |
+-----------+
|  44000000 |
+-----------+
1 row in set (0.00 sec)

从日期时间中提取周:

mysql> SELECT EXTRACT(WEEK FROM '2017-07-14 09:04:44') WEEK;
+------+
| WEEK |
+------+
|   28 |
+------+
1 row in set (0.00 sec)

从日期时间中提取年份:

mysql> SELECT EXTRACT(YEAR FROM '2017-07-14 09:04:44') YEAR;
+------+
| YEAR |
+------+
| 2017 |
+------+
1 row in set (0.00 sec)

从日期时间中提取year_month

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2017-07-14 09:04:44') YEARMONTH;
+-----------+
| YEARMONTH |
+-----------+
|    201707 |
+-----------+
1 row in set (0.00 sec)

在本教程中,您学习了如何使用MySQL EXTRACT()函数提取DATE或DATETIME值的一部分。