轉載自? ?MySQL extract()函數

簡介MySQL EXTRACT函數

EXTRACT()函數提取日期的一部分。下面說明了EXTRACT()函數的語法。

EXTRACT(unit FROM date)

EXTRACT()函數需要兩個參數：unit和date。

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

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)

從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值的一部分。

總結

以上是生活随笔為你收集整理的MySQL extract()函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。