一、问题背景
在实际的业务需求中，经常会遇到根据业务操作的当前时间对数据进行查询的场景，例如库存管理中对保质期的查询，以便了解即将会有多少商品即将超过保质期限，类似下图所示：
在这样的报表中，往往会存在增加一个计算字段的诉求，这个字段（临期天数）是在页面加载的时候通过系统计算自动得到的，因而在数据库中不会存储。
在这样的报表开发过程中，增加一个含有日期表达式的计算字段是一个常见的诉求，比如上图中的临期天数（expired_days），就是由“保质日期”（expiration_date）和“当前日期”（current_date()）计算得到的，其计算公式为：
expired_days = expiration_date - current_date()
在报表展示的时候，该字段由计算引擎（前端js或者server端java）实时算出expired_days的值之后展示在界面上，因此在后台数据库中也不会存储。
这时一个问题就浮出水面了：如何支持针对字段expired_days的高效过滤？这对高时效要求的业务来说是迫切需要解决的问题。
二、思考分析
思路一：条件直接拼接
直接把expired_days的公式提交给DB，假设要查询“3天内到期的数据”，过滤条件是：“expired_days ”< 3；直接把expiration_date - current_date() 拼接到sql的where条件里提交给DB。
这样会存在一个问题：由于对expiration_date字段引入了表达式，查询时没办法走索引，会导致查询效率很低下，在数据量很大的情况下，查询接口一定会超时。
思路二：条件转换
那如果在过滤的时候，由应用程序先把current_date（）的值计算好之后，再提交给DB呢？比如：
expiration_date – ‘2021-8-17’ < 3
经测试，这种条件在mysql和postgresql中，查询时同样不能使用expiration_date字段的索引，因而也不解决问题。
思路三：表达式索引
我们知道，在postgresql和mysql的高版本中已经支持了表达式索引，可不可以把expiration_date - current_date() 建立一个表达式索引呢？
这样做的结果也不行，因为这里的current_date()取值就是建立索引那一刻的值（即创建索引的DDL执行的时刻），而我们业务的实际需求是current_date()应该取查询过滤那一刻的值，不满足业务含义。
思路四：代数转换
我们的目标是在查询时能够使用到expiration_date字段的索引，而我们的过滤表达式为：
expiration_date – ‘2021-8-17’ < 3
通过前面的分析我们了解到，DB之所以不走expiration_date的索引的核心原因是：过滤表达式的左侧只能是expiration_date这个字段名，而不能是包含expiration_date的任何表达式，那么这样方案就出来了：我们可以由应用程序把上述表达式做一个简单的变化，变成以下形式：
expiration_date < 3 + ‘2021-8-17’
然后再把这个表达式提交给DB，这样就能使用索引，实现高效查询了。
三、解决方案
最终我们选择思路四来提升查询的效率，其中关键的一步是做表达式的转换，这在数学上称为“代数转换”。幸运的是，Java方面已经有开源的 代数转换 的类库，我们只要集成到我们的服务里来就可以了（参考：https://github.com/axkr/symja_android_library）。另外，在Python中也有类似的类库，我们可以根据自己的语言需要选择使用。
四、效果对比
下面将对“代数转换”前后的查询性能进行一个对比测试验证。
验证测试条件 |
|
数据库 |
PostgreSQL |
数据表 |
inventory_store |
数据量 |
2373w数据，其中expiration_date是时间戳字段，有索引 |
“代数转换”前
SQL脚本：
select * from inventory_store
where expiration_date - 1630425600000 > 748800000
limit 10
执行结果：
耗时：15.354 秒
结果分析：
由于使用了全表扫描，未能使用索引，导致查询效率较低。
“代数转换”后
SQL脚本：
select * from inventory_store
where expiration_date > 1630425600000 + 748800000
limit 10
执行结果：
耗时：0.14 毫秒
结果分析：
由于使用expiration_date索引，查询效率明显提升。
结论：通过“代数转换”方式，在本测试案例中提升查询效率99%以上，能够有效解决带有当前日期时间表达式条件查询效率问题。
