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)
取自「https://zh.wikibooks.org/w/index.php?title=MySQL/Date_Time&oldid=141967」