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

查询结果