sqlbook笔录(三)日期操作
8.1 加减日月年
使用interval关键字,或者使用date_add函数,日月年,分别用day,month,year表示
SELECT date_add(releaseDate,INTERVAL 5 DAY) FROM
t_diary
WHERE diaryId=1select releaseDate + interval 5 day as d5 from t_diary where diaryId=1
8.2 计算二个日期之间相差的天数
使用datediff(day1,day2)函数即可。Mysql中返回day1-day2的数值。
- SELECT datediff(d1.releaseDate + INTERVAL 2 day,d2.releaseDate) FROM
t_diary
d1,t_diary d2 where d1.diaryId=d2.diaryId and d1.diaryId=1
很显然返回的结果就是2。
8.4 计算二个日期之间月份的差值
使用month可以计算出一个日期的月份,要计算月份的差值,还要考虑年份的不同,比如2015-11-02 和 2014-08-02 的月份差值是15个月,而不是三个月。所以先计算出**年份的差值乘以12再加上月份差值**,就好了。
- SELECT ((YEAR(‘2015-11-02’)-year(‘2014-08-02’))*12 + month(‘2015-11-02’)-month(‘2014-08-02’)) as dy
结果是15
8.5 格式化日期
使用date_format函数,把日期格式化成指定格式。也可以把字符串格式化成日期。
- SELECT DATE_FORMAT(‘2011-09-20 08:30:45’, ‘%Y-%m-%d %H:%i:%S’);
2011-09-20 08:30:45 - SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%S’);
具体日期格式
8.6常用日期函数总结
- month,year,dayofmonth返回一个日期中的年月日。day和dayofmonth一样
- dayofyear返回这是这一年中的第几天SELECT dayofyear(‘2015-08-08’) 220
- date_format(),可提取出日期中的各个部分,9.3节的解决方案。
- last_day得到一个月的最后一天
- dayname返回日期是周几,monthname返回这个月的英文名
第九章 日期操作
9.2 确定一年内的天数
解决思路如下:
1. 找出这一年的第一天比如 2015-01-01
2. 给该日期加上一年(下一年的第一天)2016-01-01
3. 求出这二个日期的差值。
SELECT datediff(firstday + INTERVAL 1 year,firstday) from (SELECT ADDdate(‘2015-08-08’,-dayofyear(‘2015-08-08’)+1) as firstday) x
里面的select 使用的标量子查询。
9.3 提取出实践中的各个部分年月日,时分秒
下面的例子提取出小时,其他的类似
SELECT date_format(CURRENT_TIMESTAMP,’%k’) as hour
得到某个月的第一天和最后一天
先得到这个日期在当前月中的天数。然后使用用当前日期减去这个天数再加1就好了。最后一天可以使用last_day得到
SELECT date_add(‘2011-08-08’,INTERVAL -day(‘2011-08-08’)+1 day) as firstday,last_day(‘2011-08-08’) as lastday
9.13 识别重叠的日期范围
比如:要查询月份和星期几都相同的记录,比如2015-12-02 和 2014-12-03都是周三,都是12月。
select * from a,b where date_format(a.date,’%w%M’)=date_format(b.date,’%w%M’)
注意使用date_format函数 对日期进行操作。