跳至內容

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)