Mysql日期操作

前面两天主要介绍了limit分页以及count函数获取行数两个高频语法，分页在我们日常开发几乎是必会的一个技术点，所以做好分页的优化是势在必行的。本篇谈谈日期处理我们如何操作，在订单类型业务中我们经常需要对时间做处理，通过时间来分页显示订单等，所以不可避免的需要对日期处理操作滚瓜烂熟。首先我们创建一个订单表，字段大约20多个字段，然后使用存储过程插入1万条数据。

SUBSTR函数

首先我们来设想这么一个业务需求：用户下单在数据库保存用户下单时间pay_date，使用的datetime格式，用户下单成功需要发货，但是datetime显示付款时间精确到秒，我们商家后台发货系统只需要精确到日期，这个时候我们其实就可以使用到substr函数，来分割字符串，我们来看下substr函数的基本用法：

select substr(datetime, startChar, endChar) as pay_date from table;

可以很明显看到，substr函数携带3个参数：

• datetime：数据列时间字段，比如我们截图中的pay_date。
• startChar：开始截取的起始字符，从1开始。
• endChar：结束截取的字符位置。

比如我们上面需要将时间精确到天，也就是要将时分秒剔除，我们都知道数据库时间保存一般格式为：2019-01-01 00::00:00，所以我们可以使用以下语句去截取：

select substr(pay_date, 1, 10) from aok_score_info limit 1;

很简单的就从datetime格式中成功提取到日期了，那我们来设想另外一种需求：现在很多公司都拥有招商团队，需要统计周一到周五工作日的业绩，那我这条订单下单时间如何转化成星期几呢？所以我们这时候可以使用dayofweek函数。

dayofweek函数

我们先来看下dayofweek函数的基本语法：

select pay_date, DAYOFWEEK(pay_date) from aok_score_info limit 1;

dayofweek函数很好理解，就是传入一个日期，返回日期对应星期几。那我们再来设想一种需求：比如外卖平台一般会有创建订单后15分钟若未进行付款则自动取消订单的操作，那我们如何操作呢？可能大多数人的做法是从数据库取出这条待付款订单，然后和当前时间对比判断是否超过15分钟来完成这个需求，但是这样数据库读写逻辑加上业务逻辑就为了完成这么一个功能未免小题大做，这时候我们可以很简单的使用timestampdiff函数来实现统计两个时间之间的间隔。

timestampdiff函数

我们先看下timestamp函数的基本语法：

select created_date, pay_date, timestampdiff(minute, create_date, pay_date) from aok_score_info limit 1; 

timestampdiff函数有三个参数：

• minute：取值有5个，second表示计算秒级间隔，minute表示计算分钟间隔，hour表示小时间隔，day表示天数间隔，month表示月份间隔，year表示年份间隔。
• created_date：开始时间，比如订单创建时间。
• pay_date：结束时间，比如订单付款时间。

在数据库我们可以看到日期格式为2019-01-01 00:00:00这种标准格式，但是我们通过代码查询出的数据经常日起都不是标准格式，比如这是我通过代码读书来的一条商品数据：

{
                "id": 2143,
                "pictUrl": "/shop_goods/2019/2/26/0/0/92122639.jpg",
                "shopTitle": "宜佳生活家居",
                "title": "梦洁家居 斜纹全棉四件套体面 浅黄色",
                "score": 1980,
                "pics": [
                    "/shop_goods/2019/2/26/0/0/9212263910.jpg",
                    "/shop_goods/2019/2/26/0/0/9212263911.jpg",
                    "/shop_goods/2019/2/26/0/0/9212263912.jpg",
                    "/shop_goods/2019/2/26/0/0/9212263913.jpg",
                    "/shop_goods/2019/2/26/0/0/9212263914.jpg"
                ],
                "detail": {
                    "pics": [
                        "/shop_goods/2019/2/26/0/0/9212263915.jpg",
                        "/shop_goods/2019/2/26/0/0/9212263916.jpg",
                        "/shop_goods/2019/2/26/0/0/9212263917.jpg"
                    ]
                },
                "created_date": "2019-04-08T09:42:48.000Z",
                "belong_id": "2143",
                "plat_link": "https://item.m.jd.com/product/31807819009.html",
                "location_type": 1
            }

可以明显看到商品上传时间created_date不是正常时间格式，所以我们肯定需要对日期进行格式化，这时候就可以使用日期处理最常用的函数：date_format函数。

date_format函数

date_format可以对时间做一些很常规的格式化，比如它可以将上面的2019-04-08T09:42:48.000Z格式化为标准格式，它也可以将时间转换成各种格式。我们先看下date_format函数的一般语法：

select created_date, date_format(created_date, '%Y-%m-%d-%T') from aok_score_info limit 1;

参数很容易理解，第一个参数指定时间，第二个参数指定格式，通过date_format我们可以轻松实现将时间只取日期，也可以将时间转化为星期几。我们可以简单看几种date_format指定不同格式的用法：

可以看到我们通过date_format可以轻松将时间格式转换成我们想要输出的形式，当然date_format第二个参数可以指定更多各种输出格式，我这里不一一讲解，贴下列表可以自行测试：

日期操作的函数有很多，还有date_add函数可以对时间相加，date_sub可以对时间相减，还有timestamp函数转化时间戳等等，但是最常用的应该还是上面讲到的几个函数。接下来篇末主要谈谈如果查询中where使用时间间隔查询如何能够优化呢？

时间间隔查询如何优化

这里针对时间查询优化我主要觉得有以下几点：

• 使用between...and范围查询，然后在时间段添加索引可以命中索引。
• 可以使用timestamp代替datetime，可以更节省空间。
• 对于统计类的查询【如查询连续几个月的数据总量，或查询同比、环比等】，可以通过定时查询并统计到统计表的方式提高查询速度