MySQL日期时间处理函数

-- mysql日期时间处理函数
select now() from dual;-- 当前日期时间：2017-05-12 11:41:47
-- 在mysql里也存在和oracle里类似的dual虚拟表：官方声明纯粹是为了满足select ... from...这一习惯问题，mysql会忽略对该表的引用。
-- 那么mysql中就不用dual了吧。
select now();-- 当前日期时间：2017-05-12 11:41:55
-- 除了 now() 函数能获得当前的日期时间外，mysql 中还有下面的函数：
select current_timestamp();-- 2017-05-15 10:19:31
select current_timestamp;-- 2017-05-15 10:19:51
select localtime();-- 2017-05-15 10:20:00
select localtime;-- 2017-05-15 10:20:10
select localtimestamp();-- 2017-05-15 10:20:21(v4.0.6)
select localtimestamp;-- 2017-05-15 10:20:30(v4.0.6)
-- 这些日期时间函数，都等同于 now()。鉴于 now() 函数简短易记，建议总是使用 now()来替代上面列出的函数。
 
select sysdate();-- 当前日期时间：2017-05-12 11:42:03
-- sysdate() 日期时间函数跟 now() 类似，
-- 不同之处在于：now() 在执行开始时值就得到了;sysdate() 在函数执行时动态得到值。
-- 看下面的例子就明白了：
select now(), sleep(3), now();
select sysdate(), sleep(3), sysdate();
 
 
select curdate();-- 当前日期：2017-05-12
select current_date();-- 当前日期：等同于 curdate()
select current_date;-- 当前日期：等同于 curdate()
 
select curtime();-- 当前时间：11:42:47
select current_time();-- 当前时间：等同于 curtime()
select current_time;-- 当前时间：等同于 curtime()
 
-- 获得当前 utc 日期时间函数
select utc_timestamp(), utc_date(), utc_time()
-- mysql 获得当前时间戳函数：current_timestamp, current_timestamp()
select current_timestamp, current_timestamp();-- 2017-05-15 10:32:21 | 2017-05-15 10:32:21
 
 
-- mysql 日期时间 extract（选取） 函数
set @dt = '2017-05-15 10:37:14.123456';
select date(@dt);-- 获取日期：2017-05-15
select time('2017-05-15 10:37:14.123456');-- 获取时间：10:37:14.123456
select year('2017-05-15 10:37:14.123456');-- 获取年份
select month('2017-05-15 10:37:14.123456');-- 获取月份
select day('2017-05-15 10:37:14.123456');-- 获取日
select hour('2017-05-15 10:37:14.123456');-- 获取时
select minute('2017-05-15 10:37:14.123456');-- 获取分
select second('2017-05-15 10:37:14.123456');-- 获取秒
select microsecond('2017-05-15 10:37:14.123456');-- 获取毫秒
select quarter('2017-05-15 10:37:14.123456');-- 获取季度
select week('2017-05-15 10:37:14.123456');-- 20 (获取周)
select week('2017-05-15 10:37:14.123456', 7);-- ****** 测试此函数在mysql5.6下无效
select weekofyear('2017-05-15 10:37:14.123456');-- 同week()
select dayofyear('2017-05-15 10:37:14.123456');-- 135 (日期在年度中第几天)
select dayofmonth('2017-05-15 10:37:14.123456');-- 5 (日期在月度中第几天)
select dayofweek('2017-05-15 10:37:14.123456');-- 2 (日期在周中第几天；周日为第一天)
select weekday('2017-05-15 10:37:14.123456');-- 0
select weekday('2017-05-21 10:37:14.123456');-- 6(与dayofweek()都表示日期在周的第几天，只是参考标准不同，weekday()周一为第0天，周日为第6天)
select yearweek('2017-05-15 10:37:14.123456');-- 201720(年和周)
 
select extract(year from '2017-05-15 10:37:14.123456');
select extract(month from '2017-05-15 10:37:14.123456');
select extract(day from '2017-05-15 10:37:14.123456');
select extract(hour from '2017-05-15 10:37:14.123456');
select extract(minute from '2017-05-15 10:37:14.123456');
select extract(second from '2017-05-15 10:37:14.123456');
select extract(microsecond from '2017-05-15 10:37:14.123456');
select extract(quarter from '2017-05-15 10:37:14.123456');
select extract(week from '2017-05-15 10:37:14.123456');
select extract(year_month from '2017-05-15 10:37:14.123456');
select extract(day_hour from '2017-05-15 10:37:14.123456');
select extract(day_minute from '2017-05-15 10:37:14.123456');-- 151037(日时分)
select extract(day_second from '2017-05-15 10:37:14.123456');-- 15103714(日时分秒)
select extract(day_microsecond from '2017-05-15 10:37:14.123456');-- 15103714123456(日时分秒毫秒)
select extract(hour_minute from '2017-05-15 10:37:14.123456');-- 1037(时分)
select extract(hour_second from '2017-05-15 10:37:14.123456');-- 103714(时分秒)
select extract(hour_microsecond from '2017-05-15 10:37:14.123456');-- 103714123456(日时分秒毫秒)
select extract(minute_second from '2017-05-15 10:37:14.123456');-- 3714(分秒)
select extract(minute_microsecond from '2017-05-15 10:37:14.123456');-- 3714123456(分秒毫秒)
select extract(second_microsecond from '2017-05-15 10:37:14.123456');-- 14123456(秒毫秒)
-- mysql extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。
-- 并且还具有选取‘day_microsecond' 等功能。
-- 注意这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分。
 
 
select dayname('2017-05-15 10:37:14.123456');-- monday(返回英文星期)
select monthname('2017-05-15 10:37:14.123456');-- may(返回英文月份)
select last_day('2016-02-01');-- 2016-02-29 (返回月份中最后一天)
select last_day('2016-05-01');-- 2016-05-31
 
-- date_add(date,interval expr type) 从日期加上指定的时间间隔
-- type参数可参考：http://www.w3school.com.cn/sql/func_date_sub.asp
select date_add('2017-05-15 10:37:14.123456',interval 1 year);-- 表示：2018-05-15 10:37:14.123456
select date_add('2017-05-15 10:37:14.123456',interval 1 quarter);-- 表示：2017-08-15 10:37:14.123456
select date_add('2017-05-15 10:37:14.123456',interval 1 month);-- 表示：2017-06-15 10:37:14.123456
select date_add('2017-05-15 10:37:14.123456',interval 1 week);-- 表示：2017-05-22 10:37:14.123456
select date_add('2017-05-15 10:37:14.123456',interval 1 day);-- 表示：2017-05-16 10:37:14.123456
select date_add('2017-05-15 10:37:14.123456',interval 1 hour);-- 表示：2017-05-15 11:37:14.123456
select date_add('2017-05-15 10:37:14.123456',interval 1 minute);-- 表示：2017-05-15 10:38:14.123456
select date_add('2017-05-15 10:37:14.123456',interval 1 second);-- 表示：2017-05-15 10:37:15.123456
select date_add('2017-05-15 10:37:14.123456',interval 1 microsecond);-- 表示：2017-05-15 10:37:14.123457
 
 
-- date_sub(date,interval expr type) 从日期减去指定的时间间隔
select date_sub('2017-05-15 10:37:14.123456',interval 1 year);-- 表示：2016-05-15 10:37:14.123456
select date_sub('2017-05-15 10:37:14.123456',interval 1 quarter);-- 表示：2017-02-15 10:37:14.123456
select date_sub('2017-05-15 10:37:14.123456',interval 1 month);-- 表示：2017-04-15 10:37:14.123456
select date_sub('2017-05-15 10:37:14.123456',interval 1 week);-- 表示：2017-05-08 10:37:14.123456
select date_sub('2017-05-15 10:37:14.123456',interval 1 day);-- 表示：2017-05-14 10:37:14.123456
select date_sub('2017-05-15 10:37:14.123456',interval 1 hour);-- 表示：2017-05-15 09:37:14.123456
select date_sub('2017-05-15 10:37:14.123456',interval 1 minute);-- 表示：2017-05-15 10:36:14.123456
select date_sub('2017-05-15 10:37:14.123456',interval 1 second);-- 表示：2017-05-15 10:37:13.123456
select date_sub('2017-05-15 10:37:14.123456',interval 1 microsecond);-- 表示：2017-05-15 10:37:14.123455
 
-- 经特殊日期测试，date_sub(date,interval expr type)可放心使用
select date_sub(curdate(),interval 1 day);-- 前一天：2017-05-11
select date_sub(curdate(),interval -1 day);-- 后一天：2017-05-13
select date_sub(curdate(),interval 1 month);-- 一个月前日期：2017-04-12
select date_sub(curdate(),interval -1 month);-- 一个月后日期：2017-06-12
select date_sub(curdate(),interval 1 year);-- 一年前日期：2016-05-12
select date_sub(curdate(),interval -1 year);-- 一年后日期：20178-06-12
-- mysql date_sub() 日期时间函数 和 date_add() 用法一致，并且可以用internal -1 xxx的形式互换使用；
-- 另外，mysql 中还有两个函数 subdate(), subtime()，建议，用 date_sub() 来替代。
 
-- mysql 另类日期函数：period_add(p,n), period_diff(p1,p2)
-- 函数参数“p” 的格式为“yyyymm” 或者 “yymm”，第二个参数“n” 表示增加或减去 n month（月）。
-- mysql period_add(p,n)：日期加/减去n月。
select period_add(201705,2), period_add(201705,-2);-- 201707  20170503
-- period_diff(p1,p2)：日期 p1-p2，返回 n 个月。
select period_diff(201706, 201703);--
-- datediff(date1,date2)：两个日期相减 date1 - date2，返回天数
select datediff('2017-06-05','2017-05-29');-- 7
-- timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值
select timediff('2017-06-05 19:28:37', '2017-06-05 17:00:00');-- 02:28:37
 
 
-- mysql日期转换函数
select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605);-- 01:00:05
 
-- mysql （日期、天数）转换函数：to_days(date), from_days(days)
select to_days('0000-00-00'); -- null
select to_days('2017-06-05'); -- 736850
select from_days(0);           -- '0000-00-00'
select from_days(736850);      -- '2017-06-05'
 
-- mysql str to date （字符串转换为日期）函数：str_to_date(str, format)
 
select str_to_date('06.05.2017 19:40:30', '%m.%d.%y %h:%i:%s');-- 2017-06-05 19:40:30
select str_to_date('06/05/2017', '%m/%d/%y');                  -- 2017-06-05
select str_to_date('2017/12/3','%y/%m/%d')               -- 2017-12-03
select str_to_date('20:09:30', '%h:%i:%s')               -- null(超过12时的小时用小写h，得到的结果为null)
 
-- 日期时间格式化
select date_format('2017-05-12 17:03:51', '%y年%m月%d日 %h时%i分%s秒');-- 2017年05月12日 17时03分51秒(具体需要什么格式的数据根据实际情况来;小写h为12小时制;)
select time_format('2017-05-12 17:03:51', '%y年%m月%d日 %h时%i分%s秒');-- 0000年00月00日 17时03分51秒(time_format()只能用于时间的格式化)
-- str_to_date()和date_formate()为互逆操作
 
-- mysql 获得国家地区时间格式函数：get_format()
-- mysql get_format() 语法：get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'
-- mysql get_format() 用法的全部示例：
select get_format(date,'usa');       -- '%m.%d.%y'
select get_format(date,'jis');       -- '%y-%m-%d'
select get_format(date,'iso');       -- '%y-%m-%d'
select get_format(date,'eur');       -- '%d.%m.%y'
select get_format(date,'internal');       -- '%y%m%d'
select get_format(datetime,'usa');       -- '%y-%m-%d %h.%i.%s'
select get_format(datetime,'jis');       -- '%y-%m-%d %h:%i:%s'
select get_format(datetime,'iso');       -- '%y-%m-%d %h:%i:%s'
select get_format(datetime,'eur');       -- '%y-%m-%d %h.%i.%s'
select get_format(datetime,'internal'); -- '%y%m%d%h%i%s'
select get_format(time,'usa');       -- '%h:%i:%s %p'
select get_format(time,'jis');       -- '%h:%i:%s'
select get_format(time,'iso');       -- '%h:%i:%s'
select get_format(time,'eur');       -- '%h.%i.%s'
select get_format(time,'internal');     -- '%h%i%s'
 
 
-- mysql 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)
select makedate(2017,31);   -- '2017-01-31'
select makedate(2017,32);   -- '2017-02-01'
select maketime(19,52,35);  -- '19:52:35'
 
-- mysql 时区（timezone）转换函数：convert_tz(dt,from_tz,to_tz)
select convert_tz('2017-06-05 19:54:12', '+08:00', '+00:00'); -- 2017-06-05 11:54:12
 
 
-- mysql （unix 时间戳、日期）转换函数
-- unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp,format)
-- 将具体时间时间转为timestamp
select unix_timestamp();-- 当前时间的时间戳：1494815779
select unix_timestamp('2017-05-15');-- 指定日期的时间戳：1494777600
select unix_timestamp('2017-05-15 10:37:14');-- 指定日期时间的时间戳：1494815834
 
-- 将时间戳转为具体时间
select from_unixtime(1494815834);-- 2017-05-15 10:37:14
select from_unixtime(1494815834, '%y年%m月%d日 %h时%分:%s秒');-- 获取时间戳对应的格式化日期时间
 
-- mysql 时间戳（timestamp）转换、增、减函数
select timestamp('2017-05-15');-- 2017-05-15 00:00:00
select timestamp('2017-05-15 08:12:25', '01:01:01');-- 2017-05-15 09:13:26
select date_add('2017-05-15 08:12:25', interval 1 day);-- 2017-05-16 08:12:25
select timestampadd(day, 1, '2017-05-15 08:12:25');-- 2017-05-16 08:12:25; mysql timestampadd() 函数类似于 date_add()。
 
select timestampdiff(year, '2017-06-01', '2016-05-15');-- -1
select timestampdiff(month, '2017-06-01', '2016-06-15');-- -11
select timestampdiff(day, '2017-06-01', '2016-06-15');-- -351
select timestampdiff(hour, '2017-06-01 08:12:25', '2016-06-15 00:00:00');-- -8432
select timestampdiff(minute, '2017-06-01 08:12:25', '2016-06-15 00:00:00');-- -505932
select timestampdiff(second, '2017-06-01 08:12:25', '2016-06-15 00:00:00');-- -30355945