mysql 日期函数

--返回当前时间

select now();　　　　　　　      　 -- 年月日时分秒 2018-07-14 00:57:20
select sysdate(); 　　　　　　       -- 返回系统时间 2018-07-14 00:57:20
select current_timestamp(); 　　    -- 和 now()相同 2018-07-14 02:33:20
select curdate();　　　　　　　　 -- 年月日 2018-07-14
select curtime(); 　　　　　　        -- 时分秒 00:57:20

---

 -- 返回时间戳, 可以传参，也可以不传参

select unix_timestamp(); 1531501105
select unix_timestamp(now()); 1531501125
select unix_timestamp(curdate()); 1531497600
select unix_timestamp('1997-7-1'); 867686400
select unix_timestamp('1997=7=1'); 867686400
select unix_timestamp(19970701); 867686400

---

 -- unixtime时间戳转日期

select from_unixtime(1531492170); 2018-07-13 22:29:30
select from_unixtime('1531492170'); 2018-07-13 22:29:30.000000

---

-- 日期转unixtime时间戳（这个函数就是上面的函数）
select unix_timestamp(now()); 1531501270

---

 -- 下面这几个函数必须传递参数

select year(now());             --  年　　　　　　　　　　 2018
select year(curdate());               　　　　　　　　　　 2018
select week(now());            --  一年中的第几周 　　　　 27
select week(curdate());              　　　　　　　　　　  27
select hour(curtime());        --  小时    　　　　　　　     1
select hour(now());                   　　　　　　　　　        1
select minute(curtime());    --  分钟 　　　　　　　　　  4
select minute(now());                   　　　　　　　　　    4
select monthname(now()); --  英文月份名称                    July

---

 -- 计算两个日期之间相差的天数

select datediff(now(), '2018-7-1'); 　　　　　　　　　   13

---

 -- date_add(date,interval expr type);

type 可以是：
YEAR 年、MONTH 月、DAY 日、HOUR 时、MINUTE 分、SECOND 秒
YEAR_MONTH 年月、DAY_HOUR 日时、DAY_MINUTE 日分、DAY_SECOND 日秒
HOUR_MINUTE 时分、HOUR_SECOND 时秒、MINUTE_SECOND 分秒

select now(), date_add(now(),INTERVAL 31 day) after31days, date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;
2018-07-14 01:57:40 2018-08-14 01:57:40 2019-09-14 01:57:40
select now(), date_add(now(),INTERVAL -31 day) after31days,date_add(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth;
2018-07-14 01:58:01 2018-06-13 01:58:01 2017-05-14 01:58:01

---

 -- 返回指定格式的日期字符串

select date_format(now(),'%M,%D,%Y'); // July,14th,2018
select date_format(now(),'%b,%d,%y'); // Jul,14,18
select date_format(now(),'%m,%e,%Y'); // 07,14,2018
select date_format(now(),'%c,%e,%Y'); // 7,14,2018

select date_format(now(), '%Y-%m-%d %T'); // 2018-07-14 00:28:10
select date_format(now(), '%Y-%m-%d %r'); // 2018-07-14 12:27:40 AM
select date_format(now(), '%Y-%m-%d %H:%i:%s'); // 2018-07-14 00:32:49
select date_format(now(), '%Y-%m-%d %H%i%s'); // 2018-07-14 003110

%Y -- 4位数字表示的年份
%y -- 2位数字表示的年份

%M -- 英文月名
%b -- 缩写的英文月名
%m -- 2位数字表示的月份
%c -- 数字表示的月份（1,2,...,12）

%D -- 英文后缀表示月中的天数,1st,2nd,3rd
%d -- 2位数字表示的日期
%e -- 数字形式表示的日期（1,2，...,31）

%T 24 -- 小时的时间形式（hh:mm:ss）
%r 12 -- 小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM）
%H -- 2位数字形式的小时，24小时
%h,%I -- 2位数字形式的小时，12小时（这两个一样）
%k -- 数字形式的小时，24 小时（0,1,...,23）
%l -- 数字形式的小时，12 小时（1,2,...,12）
%p -- AM 或 PM

%i -- 2位数字表示的分

%S,%s 两位数字形式的秒（这两个一样）

%W -- 一周中每一天的名称（Sunday,Monday,...,Saturday）
%a -- 一周中每一天名称的缩写（Sun,Mon,...,Sat）

%j -- 以 3 位数字表示年中的天数（001,002,...,366）
%U -- 一年中的第几周，其中 Sunday 为周中的第一天
%u -- 一年中的第几周，其中 Monday 为周中的第一天
%w -- 以数字形式表示周中的天数（0=Sunday,1=Monday,...,6=Saturday）

%% -- %
select date_format(now(), '%%'); // %

---

类型转换

now()、字符串、数字转datetime类型

create table t(dt datetime);
insert into t values(now());
insert into t values('2007-9-3 12:10:10');
insert into t values('2007/9/3 12+10+10');
insert into t values('2007#9#3 12+10+10');
insert into t values('2007+9+3 12+10+10');
insert into t values('20070903121010');
insert into t values(20080903121010);

now()、字符串、数字转date类型
create table test(dt date);
insert into test values(now());
insert into test values('2007-9-3 12:10:10');
insert into test values('2007/9/3 12+10+10');
insert into test values('2007#9#3 12+10+10');
insert into test values('2007+9+3 12+10+10');
insert into test values('20070903121010');
insert into test values(20080903121010);

now()、字符串、数字转time类型
create table t3(dt time);
insert into t3 values(now());
insert into t3 values('2007-9-3 12:10:10');
insert into t3 values('2007/9/3 12+10+10');
insert into t3 values('2007#9#3 12+10+10');
insert into t3 values('2007+9+3 12+10+10');
insert into t3 values('20070903121010');
insert into t3 values(20080903121010);

now()、字符串、数字转timestamp类型
create table t4(dt timestamp);
insert into t4 values(now());
insert into t4 values('2007-9-3 12:10:10');
insert into t4 values('2007/9/3 12+10+10');
insert into t4 values('2007#9#3 12+10+10');
insert into t4 values('2007+9+3 12+10+10');
insert into t4 values('20070903121010');
insert into t4 values(20080903121010);

在任何时间，now()、任意分隔符的年月日时分秒字符串、年月日时分秒数字串都可以拿来当日期类型、时间戳使用。但月日时分秒最好保持2位数。

日期类型可以转时间戳，时间戳不能转时期类型

drop table test;
create table test(dt date);
insert into test values(unix_timestamp());

drop table test;
create table test(dt timestamp);
insert into test values(CURDATE());