摘要:在本教程中,您将学习如何使用 MySQL EXTRACT()
函数提取DATE
或DATETIME
值的一部分。
MySQL EXTRACT()
函数简介
EXTRACT()
函数提取日期的一部分。下面说明了EXTRACT()
函数的语法。
EXTRACT(unit FROM date)
Code language: SQL (Structured Query Language) (sql)
EXTRACT()
函数需要两个参数unit
和date
。
unit
是您要从date
中提取的间隔。以下是unit
参数的有效间隔。
- 天
- DAY_HOUR
- DAY_MICROSECOND
- DAY_MINUTE
- DAY_SECOND
- 小时
- 小时_微秒
- 小时_分钟
- HOUR_SECOND
- 微秒
- 分钟
- 分钟_微秒
- 分钟_秒
- 月
- 四分之一
- 第二
- SECOND_微秒
- 星期
- 年
- 年_月
date
是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)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取 day_hour:
mysql> SELECT EXTRACT(DAY_HOUR FROM '2017-07-14 09:04:44') DAYHOUR;
+---------+
| DAYHOUR |
+---------+
| 1409 |
+---------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取 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)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取 day_month:
mysql> SELECT EXTRACT(DAY_MINUTE FROM '2017-07-14 09:04:44') DAY_M;
+--------+
| DAY_M |
+--------+
| 140904 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取 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)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取小时:
mysql> SELECT EXTRACT(HOUR FROM '2017-07-14 09:04:44') HOUR;
+------+
| HOUR |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取 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)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取 hour_min:
mysql> SELECT EXTRACT(HOUR_MINUTE FROM '2017-07-14 09:04:44') HOUR_M;
+--------+
| HOUR_M |
+--------+
| 904 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取 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)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取微秒:
mysql> SELECT EXTRACT(MICROSECOND FROM '2017-07-14 09:04:44') MICROSECOND;
+-------------+
| MICROSECOND |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取分钟:
mysql> SELECT EXTRACT(MINUTE FROM '2017-07-14 09:04:44') MINUTE;
+--------+
| MINUTE |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取 分钟_微秒:
mysql> SELECT EXTRACT(MINUTE_MICROSECOND FROM '2017-07-14 09:04:44') MINUTE_MS;
+-----------+
| MINUTE_MS |
+-----------+
| 444000000 |
+-----------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取 分钟_秒:
mysql> SELECT EXTRACT(MINUTE_SECOND FROM '2017-07-14 09:04:44') MINUTE_S;
+----------+
| MINUTE_S |
+----------+
| 444 |
+----------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取月份:
mysql> SELECT EXTRACT(MONTH FROM '2017-07-14 09:04:44') MONTH;
+-------+
| MONTH |
+-------+
| 7 |
+-------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取四分之一:
mysql> SELECT EXTRACT(QUARTER FROM '2017-07-14 09:04:44') QUARTER;
+---------+
| QUARTER |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取秒数:
mysql> SELECT EXTRACT(SECOND FROM '2017-07-14 09:04:44') SECOND;
+--------+
| SECOND |
+--------+
| 44 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取第二微秒:
mysql> SELECT EXTRACT(SECOND_MICROSECOND FROM '2017-07-14 09:04:44') SECOND_MS;
+-----------+
| SECOND_MS |
+-----------+
| 44000000 |
+-----------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取周:
mysql> SELECT EXTRACT(WEEK FROM '2017-07-14 09:04:44') WEEK;
+------+
| WEEK |
+------+
| 28 |
+------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取年份:
mysql> SELECT EXTRACT(YEAR FROM '2017-07-14 09:04:44') YEAR;
+------+
| YEAR |
+------+
| 2017 |
+------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
从日期时间中提取year_month
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2017-07-14 09:04:44') YEARMONTH;
+-----------+
| YEARMONTH |
+-----------+
| 201707 |
+-----------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
在本教程中,您学习了如何使用 MySQL EXTRACT()
函数提取DATE
或DATETIME
值的一部分。
本教程有帮助吗?