PostgreSQL如何按日期GROUP BY/ORDER BY且保留to_char格式化日期列

解决方案 #

你可以直接使用转换时区后的DATE类型字段做分组和排序，仅在返回字段时转成指定格式的字符串，写法如下：

SELECT 
  to_char(TRANSACTED AT TIME ZONE '{session['time_zone_3']}', 'DD-MM-YYYY') AS date,
  SUM(glasses) AS glasses,
  SUM(sleep) AS sleep,
  SUM(calories) AS calories
FROM history
GROUP BY DATE(TRANSACTED AT TIME ZONE '{session['time_zone_3']}')
ORDER BY DATE(TRANSACTED AT TIME ZONE '{session['time_zone_3']}') DESC;

如果想要避免重复写时区转换逻辑，也可以用子查询拆分逻辑，可读性更高：

SELECT 
  to_char(local_date, 'DD-MM-YYYY') AS date,
  SUM(glasses) AS glasses,
  SUM(sleep) AS sleep,
  SUM(calories) AS calories
FROM (
  SELECT 
    DATE(TRANSACTED AT TIME ZONE '{session['time_zone_3']}') AS local_date,
    glasses,
    sleep,
    calories
  FROM history
) AS history_with_local_date
GROUP BY local_date
ORDER BY local_date DESC;

错误原因说明 #

1. 最初的写法使用字符串格式的date别名排序，字符串的字典序会优先匹配第一位的数字，因此会出现30-08排在01-09之前的逻辑错误。
2. 第二次的写法没有为date别名定义对应取值来源，且GROUP BY和SELECT的字段逻辑不匹配，导致数据库无法识别date字段，抛出字段不存在的错误。

内容的提问来源于stack exchange，提问作者Manaswi Sharma