MySQL日期函数总结
===========
一.具体的某一个时间的获取
1. 获取当前时间
① 当前具体时间
函数:
now()
current_timestamp()
current_timestamp
localtime()
localtime
localtimestamp – (v4.0.6)
localtimestamp() – (v4.0.6)
select now(),current_timestamp(),current_timestamp,localtime(),
localtime,localtimestamp,localtimestamp() from dual;
sysdate()函数也能达到上面的效果,now() 在执行开始时值就得到了, sysdate() 在函数执行时动态得到值;可以通过以下sql语句进行测试:
注:dual 为伪表
select now(), sleep(1), now();
select sysdate(), sleep(1), sysdate();
获取unix时间戳
select unix_timestamp(now());
将当前时间字符串转回datetime格式
select str_to_date('2017-09-11 22:25:00','%Y-%m-%d %H:%i:%s');
② 当前天
函数:
curdate()
current_date()
current_date
select curdate(),current_date(),current_date;
效果图:
③ 当前时间
函数:
curtime()
current_time()
current_time
select curtime(),current_time(),current_time;
④ 获得当前 UTC 日期时间
函数:
utc_date()
utc_time()
utc_timestamp()
select utc_timestamp(), utc_date(), utc_time(), now();
二、获取某个区间的所有数据
实际应用中常用到的
① 获取今天的数据
select * from sys_user where date_format(create_date,'%Y-%m-%d') = curdate();
② 获取昨天的数据
select * from sys_user where to_days(now()) - to_days(create_date) <= 1;
注 : to_days(now()) 获取的是从0年开始到现在的天数
③ 获取近七天的数据
select * from sys_user where date_sub(curdate(), INTERVAL 7 DAY) <= date(CREATE_DATE);
或者使用date_format() 函数格式化
④ 获取近七年的数据
select * from sys_user where date_sub(curdate(), INTERVAL 7 year) <= date(CREATE_DATE);
⑤ 获取本月的数据
select * from sys_user where date_format(CREATE_DATE, '%Y%m') = date_format(curdate(), '%Y%m');
⑥获取上个月的数据
select * from sys_user where period_diff(date_format(now(), '%Y%m') , date_format(CREATE_DATE, '%Y%m')) =1;
注: period_diff()函数返回两参数之间的月数
DATE_FORMAT()函数介绍
DATE_FORMAT(date,format)
根据format字符串格式化date值
(在format字符串中可用标志符:
%M 月名字(January……December)
%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), 这里星期一是星期的第一天
%% 字符% )
SEC_TO_TIME(seconds) 函数进行毫秒与时分秒的转换
mysql> select SEC_TO_TIME(2378);
-> '00:39:38'
mysql> select SEC_TO_TIME(2378) 0;
-> 3938
TIME_TO_SEC(time) 返回time值有多少秒
mysql> select TIME_TO_SEC('22:23:00');
-> 80580
mysql> select TIME_TO_SEC('00:39:38');
-> 2378
三、周,日期名称等函数
MySQL 返回星期和月份名称函数:dayname(), monthname()
select dayname('2017-09-12');
select monthname('2017-09-12');
MySQL last_day() 函数:返回月份中的最后一天。
select last_day('2017-09-12');
last_day() 也可以用作求月份中的天数:
select now(), day(last_day(now())) as days;
dayof… 函数:dayofweek(), dayofmonth(), dayofyear()
分别返回日期参数,在一周、一月、一年中的位置。
select dayofweek(now()), dayofmonth(now()), dayofyear(now());
week… 函数:week(), weekofyear(), dayofweek(), weekday(), yearweek()
select week(now()), weekofyear(now()), dayofweek(now()), weekday(now()), yearweek(now());
注 : week() 函数,可以有两个参数,具体可看手册。 weekofyear() 和 week() 一样,都是计算“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3);
weekday() 函数和 dayofweek() 类似,都是返回“某天”在一周中的位置。不同点在于参考的标准, weekday:(0 = Monday, 1 = Tuesday, …, 6 = Sunday); dayofweek:(1 = Sunday, 2 = Monday, …, 7 = Saturday);
yearweek() 函数,返回 year(2008) + week 位置(31)。
返回星期和月份名称函数:dayname(), monthname()
select dayname(now()), monthname(now());
时间计算函数
1.
为日期增加一个时间间隔:date_add()
为日期减去一个时间间隔:date_sub()
2.
period_add(P,N)
函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”,第二个参数“N” 表示增加或减去 N month(月)。
select period_add(20170912,2), period_add(20170912,-2);
period_diff(P1,P2)
函数,获取两时间的差值,参数格式为“YYYYMM” 或者 “YYMM”
select period_diff(20170912, 20170910);
3.
时间相减函数,
datediff(date1,date2), timediff(time1,time2)
select datediff('2017-09-12','2017-09-1'), timediff('2017-09-12 22:00:01','2017-09-12 09:00:00');
本文只是对一部分函数的总结,并不是很全面;