在使用資料庫時,時常會用到跟日期或時間相關的操作,例如取得當下日期、加幾日、計算日期相差幾天等等。
取得現在的日期時間:NOW()
取得當前的日期和時間 (current date and time)
mysql> SELECT NOW(); // '2020-06-21 00:46:40'
取得現在的日期:CURDATE()
取得當前的日期 (current date)
mysql> SELECT CURDATE(); // '2020-06-21'
取出 datetime的日期部分:DATE()
取出 datetime 的日期部分
mysql> SELECT DATE('2020-06-21 01:02:03'); // '2020-06-21'
取出 datetime的特定部分:EXTRACT()
取出 datetime 的特定部分,可自行決定參數
EXTRACT() 語法 (Syntax)
EXTRACT(part FROM datetime)
EXTRACT() 範例
mysql> SELECT EXTRACT(MONTH FROM "2020-06-15 01:02:03.1234"); // 6
參數
將範例的part改成下列後輸出結果
參數 | 結果 |
---|---|
MICROSECOND | 123400 |
SECOND | 3 |
MINUTE | 2 |
HOUR | 1 |
DAY | 15 |
WEEK | 24 |
MONTH | 6 |
QUARTER | 2 |
YEAR | 2020 |
SECOND_MICROSECOND | 3123400 |
MINUTE_SECOND | 203 |
HOUR_MICROSECOND | 10203123400 |
HOUR_SECOND | 10203 |
HOUR_MINUTE | 102 |
DAY_MICROSECOND | 15010203123400 |
DAY_SECOND | 15010203 |
DAY_MINUTE | 150102 |
DAY_HOUR | 1501 |
YEAR_MONTH | 202006 |
加上一個日期時間:DATE_ADD()
DATE_ADD() 語法 (Syntax)
DATE_ADD(date, INTERVAL value addunit)
參數
addunit可改為下列
- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
- SECOND_MICROSECOND
- MINUTE_MICROSECOND
- MINUTE_SECOND
- HOUR_MICROSECOND
- HOUR_SECOND
- HOUR_MINUTE
- DAY_MICROSECOND
- DAY_SECOND
- DAY_MINUTE
- DAY_HOUR
- YEAR_MONTH
DATE_ADD() 範例
mysql> SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE); // 2017-06-15 09:49:21
mysql> SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 DAY); // 2017-06-12 09:34:21
減去一個日期時間:DATE_SUB()
DATE_SUB() 語法 (Syntax)
DATE_SUB(date, INTERVAL value addunit)
參數
addunit可改為下列
- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
- SECOND_MICROSECOND
- MINUTE_MICROSECOND
- MINUTE_SECOND
- HOUR_MICROSECOND
- HOUR_SECOND
- HOUR_MINUTE
- DAY_MICROSECOND
- DAY_SECOND
- DAY_MINUTE
- DAY_HOUR
- YEAR_MONTH
DATE_SUB() 範例
mysql> SELECT DATE_SUB("2017-06-15 09:34:21", INTERVAL 15 MINUTE); // 2017-06-15 09:19:21
mysql> SELECT DATE_SUB("2017-06-15 09:34:21", INTERVAL -3 DAY); // 2017-06-18 09:34:21
日期相減:DATEDIFF()
DATEDIFF(date1, date2)
date1-date2的值(天數)
DATEDIFF() 範例
mysql> SELECT DATEDIFF("2017-06-25", "2017-06-15"); // 10
mysql> SELECT DATEDIFF("2017-06-25", "2016-04-15"); // 436
計算兩個日期時間的間隔:TIMESTAMPDIFF()
TIMESTAMPDIFF() 語法 (Syntax)
TIMESTAMPDIFF(unit, date1, date2)
參數
unit可改為下列
- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
TIMESTAMPDIFF() 範例
mysql> SELECT TIMESTAMPDIFF(SECOND, "2017-06-13", "2017-06-15"); // 172800
mysql> SELECT TIMESTAMPDIFF(MONTH, "2015-06-13", "2017-07-15"); // 25
格式化日期時間:DATE_FORMAT()
DATE_FORMAT(date, format)
在 format 中你可以使用的格式有:
格式 | 顯示 |
---|---|
%a | 縮寫的星期幾 Sun..Sat |
%b | 縮寫的月份 Jan..Dec |
%c | 月份 0..12 |
%D | 一個月的第幾天,英文前綴表示 0th, 1st, 2nd, 3rd, … |
%d | 一個月的第幾天,數字 00..31 |
%e | 一個月的第幾天,數字 0..31 |
%f | 毫秒 000000..999999 |
%H | 小時 00..23 |
%h | 小時 01..12 |
%I | 小時 01..12 |
%i | 分鐘 00..59 |
%j | 一年中的第幾天 001..366 |
%k | 小時 0..23 |
%l | 小時 1..12 |
%M | 月份名稱 January..December |
%m | 月份 00..12 |
%p | AM 或 PM |
%r | 十二小時制的時間 hh:mm:ss 後面接 AM 或 PM |
%S | 秒數 00..59 |
%s | 秒數 00..59 |
%T | 二十四小時制的時間 hh:mm:ss |
%U | 一年中的第幾週,用星期日當作是一週的第一天 00..53 |
%u | 一年中的第幾週,用星期一當作是一週的第一天 00..53 |
%V | 一年中的第幾週,用星期日當作是一週的第一天 01..53,與 %X 一起使用 |
%v | 一年中的第幾週,用星期一當作是一週的第一天 01..53,與 %x 一起使用 |
%W | 星期幾名稱 Sunday..Saturday |
%w | 一週的第幾天,0=星期日..6=星期六 |
%X | 年,四位數整數,用星期日當作是一週的第一天,與 %V 一起使用 |
%x | 年,四位數整數,用星期一當作是一週的第一天,與 %v 一起使用 |
%Y | 年,四位數整數 |
%y | 年,兩位數整數 |
%% | 跳脫符號,表示百分比 % |
DATE_FORMAT() 範例
mysql> SELECT DATE_FORMAT("2017-06-15", "%M %d %Y"); // June 15 2017