MySQL日期函数总结

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');

---

本文只是对一部分函数的总结，并不是很全面；