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”