问题:
#User模型类对象过滤
Users.objects.filter(date_joined__year='2022',date_joined__month = '7', date_joined__day = '22')
单独输入date_joined__year(年查询)没有问题,但是输入月后,无法查询得到数据。通过debug得到SQL语句:
SELECT
`t_user`.`id`, `t_user`.`password`, `t_user`.`last_login`, `t_user`.`is_superuser`,
`t_user`.`username`, `t_user`.`first_name`, `t_user`.`last_name`, `t_user`.`email`,
`t_user`.`is_staff`, `t_user`.`is_active`, `t_user`.`date_joined`, `t_user`.`phone`
FROM
`t_user`
WHERE
(EXTRACT(DAY FROM CONVERT_TZ(`t_user`.`date_joined`, 'UTC', 'Asia/Shanghai')) = 22
AND
EXTRACT(MONTH FROM CONVERT_TZ(`t_user`.`date_joined`, 'UTC', 'Asia/Shanghai')) = 7
AND
`t_user`.`date_joined` BETWEEN 2021-12-31 16:00:00 AND 2022-12-31 15:59:59.999999)
发现时区既有UTC 也有Asia/Shanghai,这里会导致找不到数据,但是为什么找不到数据,以后水平足够再进行补充,也欢迎大家在评论区讨论。
解决方案:
将USE_TZ的值设置为False。
LANGUAGE_CODE = 'zh-Hans'
TIME_ZONE = 'Asia/Shanghai'
USE_I18N = True
USE_L10N = True
USE_TZ = False
之后继续查询,SQL语句恢复正常:
SELECT
`t_user`.`id`, `t_user`.`password`, `t_user`.`last_login`, `t_user`.`is_superuser`,
`t_user`.`username`, `t_user`.`first_name`, `t_user`.`last_name`, `t_user`.`email`,
`t_user`.`is_staff`, `t_user`.`is_active`, `t_user`.`date_joined`, `t_user`.`phone`
FROM
`t_user`
WHERE
(EXTRACT(DAY FROM `t_user`.`date_joined`) = 22
AND
EXTRACT(MONTH FROM `t_user`.`date_joined`) = 7
AND
`t_user`.`date_joined` BETWEEN 2022-01-01 00:00:00 AND 2022-12-31 23:59:59.999999)