MySQL/Date Time
Unix Time
[編輯]基於Unix的時間戳，開始於Unix Epoch，上限到(2038年).
mysql> SET time_zone = '+00:00'; -- GMT Query OK, 0 rows affected (0.00 sec)
mysql> SELECT FROM_UNIXTIME(-1); +-------------------+ | FROM_UNIXTIME(-1) | +-------------------+ | NULL | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(0); -- "Epoch" +---------------------+ | FROM_UNIXTIME(0) | +---------------------+ | 1970-01-01 00:00:00 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(2145916799); +---------------------------+ | FROM_UNIXTIME(2145916799) | +---------------------------+ | 2037-12-31 23:59:59 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(2145916800); +---------------------------+ | FROM_UNIXTIME(2145916800) | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.00 sec)
Unix 時間戳、日期）轉換函數
unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp,format)
獲取當前日期時間
[編輯]獲得當前日期+時間（date + time）函數now()：在語句執行開始時求值
+---------------------+ | now() | +---------------------+ | 2008-08-08 22:20:46 | +---------------------+
now()同義詞有：CURRENT_TIMESTAMP 、 CURRENT_TIMESTAMP()、LOCALTIMESTAMP 、 LOCALTIMESTAMP()、LOCALTIME 、 LOCALTIME()
sysdate() 日期時間函數跟 now() 類似，但sysdate() 在函數執行時動態得到值。
curtime()，同義詞有CURRENT_TIME 、 CURRENT_TIME()
curdate()，同義詞有CURRENT_DATE 、CURRENT_DATE()
日期時間轉為字符串
[編輯]日期時間轉為字符串的函數date_format(date,format), time_format(time,format)：
select date_format('2013-03-09','%Y-%c-%d');
字符串轉為日期時間
[編輯]字符串轉為日期時間的函數：
select STR_TO_DATE('2013-03-09','%Y-%c-%d');
%W 星期名字(Sunday……Saturday) %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） %Y 年, 数字, 4 位 %y 年, 数字, 2 位 %a 缩写的星期名字(Sun……Sat) %d 月份中的天数, 数字(00……31) %e 月份中的天数, 数字(0……31) %m 月, 数字(01……12) %c 月, 数字(1……12) %b 缩写的月份名字(Jan……Dec) %j 一年中的天数(001……366) %H 小时(00……23) %k 小时(0……23) %h 小时(01……12) %I 小时(01……12) %l 小时(1……12) %i 分钟, 数字(00……59) %r 时间,12 小时(hh:mm:ss [AP]M) %T 时间,24 小时(hh:mm:ss) %S 秒(00……59) %s 秒(00……59) %p AM或PM %w 一个星期中的天数(0=Sunday ……6=Saturday ） %U 星期(0……52), 这里星期天是星期的第一天 %u 星期(0……52), 这里星期一是星期的第一天 %% 一个文字“%”。
日期與天數互相轉換
[編輯]to_days(date)是從公元0年1月0日開始的天數：
select to_days('0000-00-01'); -- 1
from_days(days)是給出天數返回日期：
mysql> select from_days(1000); +-----------------+ | from_days(1000) | +-----------------+ | 0002-09-27 | +-----------------+ 1 row in set (0.00 sec)
日期時間抽取出年月周時分
[編輯]SELECT now(),date(now()); -- 日期 SELECT now(),time(now()); -- 时间 SELECT now(),year(now()); -- 年 SELECT now(),quarter(now()); -- 季度 SELECT now(),month(now()); -- 月 SELECT now(),week(now()); -- 周 SELECT now(),day(now()); -- 日 SELECT now(),hour(now()); -- 小时 SELECT now(),minute(now()); -- 分钟 SELECT now(),second(now()); -- 秒 SELECT now(),microsecond(now()); -- 微秒 EXTRACT(unit FROM date)：从日期中抽取出某个单独的部分或组合
個性化顯示時間日期
[編輯]dayofweek(date) dayofmonth(date) dayofyear(date) dayname(date) monthname(date)
set lc_time_names='zh_CN'; show variables like 'lc_time_names'; select dayname(now()),monthname(now());
時間與秒數的互相轉換
[編輯]時間與秒轉換函數：time_to_sec(time), sec_to_time(seconds)
select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'
拼湊日期、時間函數
[編輯]拼湊日期、時間函數：makdedate(year,dayofyear), maketime(hour,minute,second)
select makedate(2001,31); -- '2001-01-31' select makedate(2001,32); -- '2001-02-01' select maketime(12,15,30); -- '12:15:30'
日期加減間隔
[編輯]為日期增加一個時間間隔：date_add()、日期減去一個時間間隔date_sub()：
select date_add(@dt, interval 1 quarter);
SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
不使用函數，也可以寫表達式進行日期的加減： date + INTERVAL expr unit date - INTERVAL expr unit
時間間隔的單位(間隔類型)（20個），如下：
| 標題文本 | 標題文本 |
|---|---|
| HOUR | 小時 |
| MINUTE | 分 |
| SECOND | 秒 |
| MICROSECOND | 毫秒 |
| YEAR | 年 |
| MONTH | 月 |
| DAY | 日 |
| WEEK | 周 |
| QUARTER | 季度 |
| YEAR_MONTH | 年和月 |
| DAY_HOUR | 日和小時 |
| DAY_MINUTE | 日和分鐘 |
| DAY_ SECOND | 日和秒 |
| HOUR_MINUTE | 小時和分 |
| HOUR_SECOND | 小時和秒 |
| MINUTE_SECOND | 分鐘和秒 |
| SECOND_MICROSECOND | 示例 |
| MINUTE_MICROSECOND | 示例 |
| HOUR_MICROSECOND | 示例 |
| DAY_MICROSECOND | 示例 |
日期或時間的相減
[編輯]日期、時間相減函數：datediff(date1,date2), timediff(time1,time2)
時間戳的轉換、加減
[編輯]時間戳（timestamp）轉換、增、減函數：
timestamp(date) -- date to timestamp timestamp(dt,time) -- dt + time timestampadd(unit,interval,datetime_expr) -- timestampdiff(unit,datetime_expr1,datetime_expr2) --
時區轉換
[編輯]MySQL服務器中的三種時區設置：
- 系統時區---保存在系統變量system_time_zone
- 服務器時區---保存在全局系統變量global.time_zone
- 每個客戶端連接的時區---保存在會話變量session.time_zone 客戶端時區的設置會影響一些日期函數返回值的顯示，例如：now()、curtime()、curdate()，也影響timestamp列值的顯示。
默認情況下，客戶端和服務器的時區相同，其值為SYSTEM，表示使用系統時區。
time_zone參數用來設置每個連接會話的時區，該參數分為全局和會話級別，可以動態修改。默認值為SYSTEM，此時使用的是全局參數 system_time_zone 的值，而 system_time_zone 默認繼承自當前系統的時區，即默認情況下 MySQL 時區和系統時區相同。time_zone參數影響時區敏感的時間值的顯示和存儲。包括一些函數(如 now()、curtime())顯示的值，以及存儲在 TIMESTAMP 類型中的值，但不影響 DATE、TIME 和 DATETIME 列中的值，因為這些數據類型在存取時未進行時區轉換，而 TIMESTAMP 類型存入數據庫的實際是 UTC 的時間，查詢顯示時會根據具體的時區來顯示不同的時間。
mysql> show global variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.00 sec)
SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone; 结果为： 'SYSTEM', 'SYSTEM', 'China Standard Time'
mysql> set global time_zone='+8:00'; Query OK, 0 rows affected (0.00 sec) mysql> set time_zone='+8:00'; Query OK, 0 rows affected (0.00 sec)
時區（timezone）轉換函數：
convert_tz(dt,from_tz,to_tz)