Django date lookup 查询不到数据

问题描述

最近在使用 Django 的时候，发现 DateTimeField__date__range 查询不到数据。

class FootPrint(models.Model):
    """
    我的足迹
    """
    goodsbase = models.ForeignKey(GoodsBase, on_delete=models.CASCADE)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    visited_at = models.DateTimeField(
        auto_now_add=True,
        help_text="访问时间",
        verbose_name="访问时间"
    )

end_date = datetime.now().date()
start_date = end_date - timedelta(days=30)

queryset = FootPrint.objects.filter(
    user_id=user_id,
    visited_at__date__range=[start_date, end_date],
).order_by('-visited_at')

我想查询某个用户30天之内的访问足迹，但是传入正确的条件，却总是查询不出数据。

问题排查及解决

首先我通过原生 SQL 直接查询数据库，发现能查询到结果，排除条件及 MySQL DATA 函数的问题。

SELECT * FROM `footprints_footprint` WHERE (`user_id` = 1 AND DATE(`visited_at`) BETWEEN '2019-07-22' AND '2019-08-21');

我打印 Django orm 生成的 SQL 语句，发现 DATE 部分如下：

 DATE(CONVERT_TZ(`footprints_footprint`.`visited_at`, 'UTC', 'Asia/Shanghai')) BETWEEN '2019-07-22' AND '2019-08-21')

到这里，可以猜测问题出在 CONVERT_TZ 函数上。查询 MySQL 官方文档，发现 CONVERT_TZ 有两种传参方式，一种是时区名，如上面 Django 生成的 SQL 语句。另一种是直接写时差。

SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

MySQL 默认是不支持写时区名的，如需支持时区名方式，需执行如下命令

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

执行完上面的命令，发现就能正常查询出数据了。

杂谈

问题解决了，但是有一个限制。如果将代码部署到其他机器，又还得执行一遍上面的命令。可以将 range 查询方式优化为大于小于比较。

end_date = datetime.now()
start_date = end_date - timedelta(days=30)

queryset = FootPrint.objects.filter(
    user_id=user_id,
    visited_at__gte=start_date, 
    visited_at_lte=end_date,
).order_by('-visited_at')

如果仍查询不到数据，请确保你的 TIME_ZONE 和 USE_TZ设置正确。参考 Django Time zones。在中国，数据库使用 MySQL，这两个值应该设置如下：

TIME_ZONE = 'Asia/Shanghai'
USE_TZ = False

如果仍想用 range 方式，可以自定义 lookup

from django.db.models import Lookup

from django.db.models import DateTimeField, DateField


class DateEqLookup(Lookup):
    """
    自定义 lookup，解决Django __date 转换时区默认用时区名，而 MySQL 默认不支持
    """

    lookup_name = 'date_eq'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)

        params = lhs_params + rhs_params
        return f'DATE({(lhs, rhs)}) = DATE({params})'

DateField.register_lookup(DateEqLookup)
DateTimeField.register_lookup(DateEqLookup)

不过 lookup 方式不太灵活，需确保查询之前 lookup 正确被注册。