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)