希望将sql、pandas中用到的常見時間日期操作做個總結，便于日常工作中檢視使用。
下一篇：時間日期處理-pandas
文章目錄
- 1. mysql 日期
-
- 1.1 mysql 日期和時間類型
- 1.2 mysql 日期和時間函數
-
- 1.2.1 擷取目前時間、日期
- 1.2.2 常用時間選取函數
- 1.2.3 日期和時間的增減
- 1.2.4 日期時間間隔
- 1.2.5 日期時間格式轉換
- 1.2.6 日期時間比較
- 1.2.7 UNIX時間戳
1. mysql 日期
1.1 mysql 日期和時間類型
- mysql中總共有5種日期時間類型，建表的時候可以指定該列的日期時間類型，具體如下表：
| 類型 | 大小（位元組） | 格式 | 最小值 | 最大值 | 舉例 |
|---|---|---|---|---|---|
| DATE | 4 | YYYY-MM-DD | 1000-01-01 | 9999-12-31 | 1973-12-30 |
| TIME | 3 | HH:MM:SS | -838:59:59 | 838:59:59 | 15:30:00 |
| DATETIME | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 | 1973-12-30 15:30:00 |
| TIEMSTAMP | 4 | YYYYMMDDHHMMSS | 1970-01-01 00:00:00 | 2038年某個時刻 | 19731230153000 |
| YEAR(2|4) | 1 | YYYY或YY | 1901（4位）1970（2位） | 2155（4位） 2069（2位） | 1973 |
- 區分DATETIME和TIEMSTAMP：
- 兩者都可用來表示YYYY-MM-DD HH:MM:SS 類型的日期：timestamp類型在mysql中是以‘YYYY-MM-DD HH:MM:SS’的形式顯示，雖然其存儲形式為‘YYYYMMDDHHMMSS’。
- 位元組數不同，時間範圍不同：timestamp占用記憶體小，但它可取範圍也小：在1970-2038之間；而datetime範圍在1000-9999，時間範圍比較廣。
-
涉及時區問題：timestamp類型能夠根據不同地區的時區來轉換時間；而datetime不會。
例如在東八區插入的timestamp類型為2020-06-20 14:21:25，在東七區顯示時，時間就變成了13:21:25，在東九區顯示時，時間就變成了15:21:25。而插入datetime類型時不論在哪個時區檢視都顯示2020-06-20 14:21:25。
是以如果涉及跨時區業務，使用timestamp更合适。
1.2 mysql 日期和時間函數
1.2.1 擷取目前時間、日期
| 函數 | 功能 |
|---|---|
| curdate() | 傳回目前日期 |
| curtime() | 傳回目前時間 |
| now() | 傳回目前日期+時間 |
1.2.2 常用時間選取函數
| 函數 | 功能 | 函數 | 功能 |
|---|---|---|---|
| date() | 傳回指定日期時間的日期部分 | time() | 傳回指定日期時間的時間部分 |
| year() | 傳回指定日期時間的所在年份 | quarter() | 傳回指定日期時間的所在季度 |
| month() | 傳回指定日期時間的所在月份 | week() | 傳回指定日期時間是第幾周 |
| day() | 傳回指定日期時間是當月的第幾天 | hour() | 傳回指定日期時間的小時數 |
| minute() | 傳回指定日期時間的分鐘數 | second() | 傳回指定日期時間的秒數 |
| 函數 | 功能 | 函數 | 功能 |
|---|---|---|---|
| dayofyear() | 傳回指定時間是當年的第幾天 | dayofweek() | |
| dayofmonth() | 同month() | weekofyear() | 注意weekofyear() = week() + 1 |
| dayname() | 傳回星期幾名稱 ，如Monday | monthname() | 傳回月名稱,如July |
-
如果想同時擷取比如year-month,可以使用extract()函數：
EXTRACT(unit FROM date)
- unit 就是常見的day/month 等，取單個機關與上述函數功能 一緻
- 可以同時取到的有：year_month、hour_minute等。注意傳回的是202006形式，不是2020-06
select extract(year_month from now());
-- 傳回的是202006
1.2.3 日期和時間的增減
data_add()、adddate()、date_sub()、subdate()
- 增加
-
date_add(date,INTERVAL expr unit)
- unit ：day、week、month、year、hour、minute、second等
SELECT date_add('2020-06-20',interval 1 day) -- 增加1天
,date_add(now(),interval 1 month) -- 增加1個月
,date_add(curtime(),interval 3 minute); -- 增加3分鐘
- adddate(date,天數)
-
減少
date_sub(date,INTERVAL expr unit)
subdate(date,天數)
1.2.4 日期時間間隔
datediff()、timediff()、timestampdiff()
-
datediff(日期1，日期2)
-傳回的是相差的天數，日期1 - 日期2;
-
timediff(時間1，時間2)
-傳回結果是時間表達式hh:mm:ss，時間1 - 時間2；
-
timestampdiff(unit，日期1，日期2)
- 日期2-日期1的時間差(注意與datediff順序不同)，機關由unit參數決定
- unit 同上，支援常見的day week month quarter year hour minute second等。
1.2.5 日期時間格式轉換
在MySQL中，日期/時間對象輸出時會自動轉為字元串，而運算時字元串又會自動轉為日期/時間對象完成運算，這中間就是時間轉換函數在發揮作用。
是以，用日期時間形式的字元串如‘2020-06-20’計算時就自動轉為了日期對象。
-
date_format(date,format)
-将日期轉換為指定格式的字元串
-
str_to_date(str,format)
- 将日期字元串轉換為日期格式
- format 格式表查詢見參考資料内容。
1.2.6 日期時間比較
日期時間是直接可以比較大小，日期時間距現在越近的越大。
Mysql中可以直接用字元串進行比較時間大小， 隻要你的日期格式是合法的就行。
表中字段類型都為varchar的，也可以直接進行比較大小。
select curdate()='2020-06-20'
,'2020-09-09' > '2020-08-08'
,'2020-06-20' > '2020-06-20 02:08:00'; -- 第一個日期會自動補全時間00:00:00
-- 傳回1，表示true;0表示false
1.2.7 UNIX時間戳
Unix時間(Unix Time)，也叫做POSIX時間或紀元時間(Epoch Time)，是用來記錄時間的流逝，是以也常被叫做時間戳。
定義為從1970-01-01T00:00:00開始流逝的秒數，不考慮閏秒。之後的時間是正數，之前的是負數。
從定義可以看到，它隻代表了從Unix紀元開始流逝的秒數，是以你身處地球上何處，這個時間都是一樣的。
-
unix_timestamp([date])
-用于傳回指定時間的UNIX時間戳；若未指定時間，則預設傳回目前時間的時間戳
SELECT UNIX_TIMESTAMP()
,UNIX_TIMESTAMP(curdate());
2. from_unixtime(unix_time)
- 将unix時間戳轉換為正常的時間形式；
【參考資料】
MySQL日期與時間函數（日期/時間格式化、增減、對比、時區、UTC和UNIX時間）