你的位置:首页 > 数据库

[数据库]MYSQL基础03(日期函数)

工作中对日期的处理是经常遇到的,需求可能多种多样,因此重点介绍。

1.获取当前日期

select NOW()-- 结果:2015-10-28 22:41:11select NOW(),SLEEP(3),NOW()-- 结果 2015-10-28 22:43:34 0 2015-10-28 22:43:34SELECT SYSDATE(),SLEEP(3),SYSDATE()-- 结果 2015-10-28 22:46:48 0 2015-10-28 22:46:52-- NOW()是sql执行前就得到,而SYSDATE()是该函数执行时才得到,一般来说NOW()已经足够,所以更常用

2.日期格式化

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), 这里星期一是星期的第一天 
 %% 字符% )

-- 根据上面的参数,我们可以得到各种各样的日期格式,比mssql的convert函数强大太多了,以下是常用的标准日期格式SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') -- 2015-10-28SELECT DATE_FORMAT(NOW(),'%y-%m-%d') -- 15-10-28SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%m:%s') -- 2015-10-28 22:10:56

3.日期截取

-- 获取日期当天是星期几(1=星期天,2=星期一....7=星期六)SELECT DAYOFWEEK(now()) -- 获取日期当天是几号 SELECT DAYOFMONTH('2015-10-28') -- 结果28-- 获取日期当天是一年中的第几天SELECT DAYOFYEAR('2015-10-28') -- 结果 301-- 获取日期的年份SELECT YEAR('2015-10-28') -- 结果 2015-- 获取日期第几季度SELECT QUARTER('2015-10-28') -- 结果 4-- 获取日期的月份SELECT MONTH('2015-10-28') -- 结果 10-- 获取日期是第几周SELECT WEEK('2015-10-28') -- 结果43 (默认星期天为一周的第一天)SELECT WEEK('2015-10-28',0) -- 结果43 (星期天为一周的第一天)SELECT WEEK('2015-10-28',1) -- 结果44 (星期一为一周的第一天)-- 获取日期的小时SELECT HOUR('2015-10-28 23:11:56') -- 结果 23-- 获取日期的分钟SELECT MINUTE('2015-10-28 23:11:56') -- 结果 11-- 获取日期的秒钟SELECT SECOND('2015-10-28 23:11:56') -- 结果 56
-- 获取日期部分
SELECT DATE('2015-10-28 23:11:56') -- 结果'2015-10-28'
-- 获取时间部分
SELECT TIME('2015-10-28 23:11:56') -- 结果'23:11:56'

4.日期运算

DATE_ADD(date,INTERVAL expr type) 

[type值 含义 期望的expr格式]:
 SECOND 秒 SECONDS 
 MINUTE 分钟 MINUTES 
 HOUR 时间 HOURS 
 DAY 天 DAYS 
 MONTH 月 MONTHS 
 YEAR 年 YEARS 
 MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS" 
 HOUR_MINUTE 小时和分钟 "HOURS:MINUTES" 
 DAY_HOUR 天和小时 "DAYS HOURS" 
 YEAR_MONTH 年和月 "YEARS-MONTHS" 
 HOUR_SECOND 小时, 分钟, "HOURS:MINUTES:SECONDS" 
 DAY_MINUTE 天, 小时, 分钟 "DAYS HOURS:MINUTES" 
 DAY_SECOND 天, 小时, 分钟, 秒 "DAYS HOURS:MINUTES:SECONDS"
 expr中允许任何标点做分隔符,如果所有是DATE值时结果是一个DATE值,否则结果是一个DATETIME值)
 如果type关键词不完整,则MySQL从右端取值,DAY_SECOND因为缺少小时分钟等于MINUTE_SECOND)
 如果增加MONTH、YEAR_MONTH或YEAR,天数大于结果月份的最大天数则使用最大天数) 

-- 说明 正号为加法,负号为减法-- 增加4天SELECT DATE_ADD('2015-10-28 23:11:56', INTERVAL 4 DAY ) --2015-11-01 23:11:56-- 减少3天SELECT DATE_ADD('2015-10-28 23:11:56', INTERVAL -3 DAY ) -- 2015-10-25 23:11:56-- 增加1年SELECT DATE_ADD('2015-10-28 23:11:56', INTERVAL 1 YEAR ) -- 2016-10-28 23:11:56-- 增加1天3小时SELECT DATE_ADD('2015-10-28 23:11:56', INTERVAL '1 3' DAY_HOUR ) -- 2015-10-27 20:11:56-- 减1天1小时1分1秒SELECT DATE_ADD('2015-10-28 23:11:56', INTERVAL '-1 1:1:1' DAY_SECOND) -- 2015-10-27 22:10:55

5.日期比较

-- 计算2个日期间隔的天数SELECT DATEDIFF('2015-10-28','2015-10-26') -- 结果2-- 计算2个日期间隔的时分秒SELECT TIMEDIFF('2015-10-28 23:11:56','2015-10-28 20:11:56') -- 结果 03:00:00

6.日期转化

-- 将日期转化为天数 (西元0年至今多少天)SELECT TO_DAYS('2015-10-28 23:11:56') -- 结果 736264SELECT TO_DAYS('0000-00-00 00:00:00') -- 结果 0-- 将日期转化为秒数 (从'1970-01-01 00:00:00'GMT开始的秒数)select UNIX_TIMESTAMP('2015-10-28 23:11:56') -- 结果 1446045116-- 将时间转化为秒数SELECT TIME_TO_SEC('01:00:05'); -- 结果 3605-- 将秒数转化为日期SELECT SEC_TO_TIME(3605); -- '01:00:05'

以上 ,参考资料:http://www.cnblogs.com/zeroone/archive/2010/05/05/1727659.html

注意问题

1. DATE_FORMAT

-- 错误示例SELECT * FROM mytable WHERE create_date>=DATE_FORMAT(NOW(),'%Y-%m-%d')-- 说明: DATE_FORMAT返回的是字符串,所以不能直接与日期字段比较,这个问题我工作中已经遇到过了-- 解决办法 使用DATE,返回日期类型SELECT * FROM mytable WHERE create_date>=DATE(NOW());

功能实现

-- 根据日期获取当月初与当月末SET @DATE_B:=DATE(DATE_FORMAT('2015-10-28','%Y-%m-1'));SET @DATE_E:=DATE_ADD(DATE_ADD(@DATE_B,INTERVAL 1 MONTH),INTERVAL -1 DAY);SELECT @DATE_B,@DATE_E