MySQL EXTRACT() 函数
- 定义和用法
EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。 - 语法
EXTRACT(unit FROM date)
| Unit 值（有且只有以下值，随意搭配不存在） | 说明 |
|---|---|
| MICROSECOND | 取值微秒 |
| SECOND | 取值秒 |
| MINUTE | 取值分钟 |
| HOUR | 取值小时 |
| DAY | 取值日期 |
| WEEK | 取值当年第几周（1-53） |
| MONTH | 取值月份 |
| QUARTER | 取值当年第几季度（1-4） |
| YEAR | 取值年份 |
| SECOND_MICROSECOND | 取值秒&微秒 |
| MINUTE_MICROSECOND | 取值分钟&微秒 |
| MINUTE_SECOND | 取值分钟&秒 |
| HOUR_MICROSECOND | 取值小时&微秒 |
| HOUR_SECOND | 取值小时&秒 |
| HOUR_MINUTE | 取值小时&分钟 |
| DAY_MICROSECOND | 取值日期&微秒 |
| DAY_SECOND | 取值日期&秒 |
| DAY_MINUTE | 取值日期&分钟 |
| DAY_HOUR | 取值日期&小时 |
| YEAR_MONTH | 取值年&月份 |
执行一个查询
SELECT p.`creationTime`
,EXTRACT( MICROSECOND FROM p.`creationTime`) AS a
,EXTRACT( SECOND FROM p.`creationTime`) AS b
,EXTRACT( MINUTE FROM p.`creationTime`) AS c
,EXTRACT( HOUR FROM p.`creationTime`) AS d
,EXTRACT( DAY FROM p.`creationTime`) AS e
,EXTRACT( WEEK FROM p.`creationTime`) AS f
,EXTRACT( MONTH FROM p.`creationTime`) AS g
,EXTRACT( QUARTER FROM p.`creationTime`) AS h
,EXTRACT( YEAR FROM p.`creationTime`) AS i
,EXTRACT( SECOND_MICROSECOND FROM p.`creationTime`) AS j
,EXTRACT( MINUTE_MICROSECOND FROM p.`creationTime`) AS k
,EXTRACT( MINUTE_SECOND FROM p.`creationTime`) AS l
,EXTRACT( HOUR_MICROSECOND FROM p.`creationTime`) AS m
,EXTRACT( HOUR_SECOND FROM p.`creationTime`) AS n
,EXTRACT( HOUR_MINUTE FROM p.`creationTime`) AS o
,EXTRACT( DAY_MICROSECOND FROM p.`creationTime`) AS p
,EXTRACT( DAY_SECOND FROM p.`creationTime`) AS q
,EXTRACT( DAY_MINUTE FROM p.`creationTime`) AS r
,EXTRACT( DAY_HOUR FROM p.`creationTime`) AS s
,EXTRACT( YEAR_MONTH FROM p.`creationTime`) AS t
FROM `XXX` p WHERE p.`id` =170821175153
查询结果
Paste_Image.png
Paste_Image.png
- 作用
可以使用此函数来对数据进行按不同时间维度分组，按年份、按季度、按月份、按周、按天数等
