简单的问题描述：

1. Django后台配置的上海时间

2. postgresql采用了默认UTC时间

3. 后台跟数据库差了8个小时，页面显示没得问题

4. 当通过orm执行原生SQL的时候发现按照小时统计的结果还是晚了八个小时

 

网上找了很多教程，有让改postgresql配置文件，timezone改为PRC。改完之后数据库显示没问题。但是通过SQL分组查询还是会有问题

SELECT
  (to_char(take_photo_time, 'yyyy-mm-dd:HH')) AS "take_photo_time",
  COUNT(take_photo_time) AS "count"
FROM
  "device_devicephotomodel"
WHERE
  "device_devicephotomodel".take_photo_time BETWEEN '2020-12-23T00:00:00' :: TIMESTAMP
  AND '2020-12-24T00:00:00' :: TIMESTAMP
GROUP BY
  (to_char(take_photo_time, 'yyyy-mm-dd:HH'))
ORDER BY
  "take_photo_time" DESC;

统计到的结果，依然是晚了8个小时

postgresql-按照小时分组统计_sql

 

 通过date_trunte可以解决。但是还得调整接口，太麻烦

SELECT
    DATE_TRUNC( 'HOUR', take_photo_time ) AS "take_photo_time",
    COUNT ( take_photo_time ) AS "count" 
FROM
    "device_devicephotomodel" 
WHERE
    "device_devicephotomodel".take_photo_time BETWEEN '2020-12-23T00:00:00' :: TIMESTAMP 
    AND '2020-12-24T00:00:00' :: TIMESTAMP 
GROUP BY
    (
    DATE_TRUNC('HOUR', take_photo_time))
ORDER BY
    "take_photo_time" DESC;

于是找了一些比较官方的文档，说是需要·····················。。直接贴解决方式

SELECT
  (to_char(take_photo_time, 'yyyy-mm-dd:HH24')) AS "take_photo_time",
  COUNT(take_photo_time) AS "count"
FROM
  "device_devicephotomodel"
WHERE
  "device_devicephotomodel".take_photo_time BETWEEN '2020-12-23T00:00:00' :: TIMESTAMP
  AND '2020-12-24T00:00:00' :: TIMESTAMP
GROUP BY
  (to_char(take_photo_time, 'yyyy-mm-dd:HH24'))
ORDER BY
  "take_photo_time" DESC;

上边飘红的地方，就是重点。两个数字解决了一天·····有点无语

postgresql-按照小时分组统计_sql_02

 

 最后发现。正确了。满足我的需求了。。可以回家了