日期分页算法

吃瓜群众

作为吃瓜群众的陈达,一向觉得分页算法不难,瞧不起人家,直到最近才发现,分页算法也需要细致的考虑,做得不好是会出现问题滴。

问题

需求如下:用户指定开始时间和结束时间,获取时间区间内的记录,然后(重点来了),分页显示出每一天的记录数量。

在列表里,如果每一个item是一条记录,那得有多好办呀,根据前端传来的pageIndex和pageSize算出offset和limit,加上startTime和endTime作为限制条件,甩出sql查询就好,示意图如下:

本需求的难点在于:每一个item是一天内的记录数量之和。
列表示意图如下:

分析解决

大方向大环境

一个item是一天内验证的数量,意味着前端传来的pageIndex和pageSize代表着日期,而不是具体的验证记录。而数据库中并没有以天作为单位存储某天的验证数量,仅存储着具体的验证记录。所以得想个方法,结合开始时间和结束时间,将前端传来的pageIndex和pageSize,转换成数据库里的offset和limit。

细分

前端传回的变量有4个,分别是pageIndex、pageSize、startTime和endTime,前2个指定了某一页的日期,后2个约定了总共参与分页的日期。
最理想的状态是pageIndex为1,pageSize为10,startTime和endTime的时间间隔正好为10天,第一天早上10点到第10天早上10点,这样我们将直接用时间间隔作为sql的限制条件,查询相应的记录并做整合就好了。

但是现实终究会让吃瓜群众难过,很多情况下,吃瓜群众遇到的都是难题。
a.如果pageIndex为1,pageSize为10,startTime和endTime的时间间隔为5,用户要第一页而天数铺不满一页,该怎么办呢?
b.如果pageIndex为1,pageSize为10,startTime和endTime的时间间隔为50,用户要第一页而天数远超一页,该怎么办呢?
c.如果pageIndex为10,pageSize为10,startTime和endTime的时间间隔为95,用户要第十页而天数远超十页,该怎么办呢?
d.如果pageIndex为20,pageSize为10,startTime和endTime的时间间隔为195,用户要第二十页而天数铺不满二十页,该怎么办呢?

用户要第一页而天数铺不满一页(最后一页)

使用场景是,前端默认pageIndex为1,pageSize为10,用户打开页面后想要看最近3天的记录。此时,sql里的offset和limit该取什么值?sqlStartTime和sqlEndTime该取什么值?
仔细想一想,page取值范围大(10),用户想看的范围小(3),利用用户指定的startTime和endTime即可查出他想要的东西,也就是说

sqlStartTime=startTime

sqlEndTime=endTime

画个示意图如下:

用户要第一页而天数远超一页

使用场景是,前端默认pageIndex为1,pageSize为10,用户打开页面后想要看最近一个月(30天)的记录。
这种情况下,不能指定『sqlStartTime=startTime && sqlEndTime=endTime』,因为我们不知道offset和limit到底该怎么设置,设的值该是多少。
分析到这里,陈达渐渐明白,不能拿用offset作为sql限制条件,得算出sqlStartTime和sqlEndTime,用时间去查数据库。
page取值范围小,用户想看的范围大,那么可以让

sqlStartTime=endTime-pageSize * (pageIndex - 1) - pageSize + 1【sqlStartTime取0点0分0秒】

sqlEndTime=endTime

举个例子,startTime=2017-10-2 12:00:00,endTime=2017-11-2 11:32:02,我们让sqlEndTime=2017-11-2 11:32:02,sqlStartTime=endTime=2017-10-24 00:00:00,这样的话,查询结果根据时间倒序,展示出来正好是最近10天的内容。(sqlStartTime要加1是因为天数-2后,时间间隔是2天,但却是3天自然天)
画个示意图如下:

用户要第十页而天数远超十页

使用场景是,用户打开页面后想要看最近半年(180天)的记录,而看过第一页后,用户不断地往后翻页,翻到了第十页。
这时的sqlStartTime和sqlEndTime该取什么值呢?
结合上一种情况分析,上一种情况的sqlStartTime就是此场景下的sqlEndTime的下一秒,所以

sqlStartTime=endTime-pageSize * (pageIndex - 1) - pageSize + 1【sqlStartTime取0点0分0秒】

sqlEndTime=endTime-pageSize * (pageIndex - 1)【sqlEndTime取23点59分59秒】

画个示意图如下:

用户要第二十页而天数铺不满二十页(最后一页)

使用场景是,用户打开想看最近195天的记录,翻到了最后一页(第2页),但最后这一页明显只会有5个item,

sqlEndTime=endTime-pageSize * (pageIndex - 1)【sqlEndTime取23点59分59秒】

sqlStartTime=startTime

画个示意图如下:

总结规律

陈达发现,对于sqlEndTime,如果是第一页,那么取用户输入的endTime,不是第一页的话,取endTime-pageSize (pageIndex - 1) 取23点59分59秒;
对于sqlStartTime,如果是最后一页,那么取用户输入的startTime,不是最后一页的话,取endTime-pageSize
(pageIndex - 1) - pageSize + 1 取0点0分0秒;