MySQL extract()函数

广告位

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

在本教程中,您将学习如何使用MySQL EXTRACT()函数来提取DATEDATETIME值的一部分。

简介MySQL EXTRACT函数

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

EXTRACT(unit FROM date)  

EXTRACT()函数需要两个参数:unitdate

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

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

dateDATEDATETIME值,从中提取间隔的日期。

MySQL EXTRACT函数示例

从日期时间提取日期:

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

datetime中提取day_hour

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

datetime中提取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)  

datetime中提取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)  

datetime中提取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)  

datetime提取小时数:

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

datetime中提取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)  

datetime中提取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)  

datetime中提取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)  

datetime提取微秒:

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)  

datetime中提取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)  

datetime中提取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)  

datetime中提取秒数:

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

datetime中提取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)  

datetime提取周:

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)  

datetime提取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值的一部分。

贺, 贺朝

关于作者: 贺朝

为您推荐