关于Mysql格式化等常见日期处理相关函数说明,大家可以参考之前的一篇博文:
mysql 根据出生日期计算年龄、日期格式化等常见日期处理相关函数使用汇总。根据以往的项目经验,本文总结并汇总了一些Mysql日期处理Hack。
1、根据出生日期计算年龄
比如员工信息表,基本上都会有出生日期字段,一来可知晓员工年龄,二来员工过生日了可以提前发点福利。根据出生日期计算年龄,本文只提供最简单也是高效的方法。
比如,出生日期为1986-10-12,则年龄(周岁)为:
SELECT TIMESTAMPDIFF(YEAR, '1986-10-12', CURDATE()) -> 32
如果纠结0岁问题,则只要在应用中判断获取结果是<=0情况下,取1即可。
2、查询某个时间段内的订单列表
订单创建时间:create_time (datetime类型),列出2019-02-01到2019-02-15的所有销售订单
select * from sale_orders where create_time >= '2019-02-01' and create_date < date_add('2019-02-15', INTERVAL 1 day)
3、判断起始日期是否在某个有效时间段内
商品价格有个有效期,比如:产品A价格30元,有效期开始日期(s_date) 2019-01-01,有效期截止日期(e_date) 2019-06-30
现在要查询2019-02-01到2019-02-15时间段的有效价格,如何处理?
从时间轴来看,我们需要取3段:
1)与s_date有交叉,2019-02-01 < s_date and 2019-02-15 >= s_date
2)在s_date,e_date时间段内,s_date < 2019-02-01 and 2019-02-15<=e_date
3)与e_date有交叉,2019-02-01 < e_date and 2019-02-15 >= e_date
我们对上述3段进行合并,即可得到如下sql:
select * from table where s_date <= '2019-02-15' and e_date >= '2019-02-01'
4、计算用户每个月登录天数
sys_login数据准备
登录ID | 登录时间 |
---|---|
1001 | 2019-01-03 23:07:11 |
1001 | 2019-02-03 13:07:11 |
1001 | 2019-02-03 23:07:11 |
1001 | 2019-02-11 23:07:11 |
1001 | 2019-02-13 23:07:11 |
SELECT year(login_time) year, month(login_time) month,BIT_COUNT(BIT_OR(1<<day(login_time))) AS days
FROM sys_login GROUP by year, month;
输出结果:
year | month | days |
---|---|---|
2019 | 1 | 1 |
2019 | 2 | 3 |