问题描述
我们自己的数据库的时区是UTC+0 ,但是客户的是UTC+8, 月底对账单的时候,会有每天八小时的误差。
所以我们要按照 UTC + 8 给出账单,
是要把数据库中的查询时间加上八小时
统计时减去八小时统计, 因为 UTC+0的第二天前八小时的数据,是UTC+8的第一天末尾的数据
WITH mtdetailC AS (
SELECT M.price,
dateadd(hour,'-8',M.receiveTime) :: DATE as receiveTimeDay
FROM
mtdetail_2022 M
WHERE
receivetime > '2022-08-01 08:00:00'
AND receivetime < '2022-08-03 08:00:00'
AND apikey = 'ggtaut2j'
AND countryname = 'KR'
AND price in (0.028,0.009)
) SELECT price, receiveTimeDay, count(price)
FROM mtdetailC
GROUP BY 1,2 ORDER BY 1,2
其他时间单位见文档 hour ,day ,month,minute