PostgreSQL中如何从Unix时间戳提取年月实现按年月分组查询

问题原因 #

原有写法仅提取了时间的月份数值，未关联年份信息，因此不同年份的相同月份数据会被划入同一分组，且输出无法展示年份。

修改后的查询语句 #

使用PostgreSQL内置的to_char()函数，将转换后的时间戳直接格式化为你需要的月/年格式，同时分组字段使用该格式化后的结果即可：

select 
to_char(to_timestamp(e.startts), 'MM/YY') as "Date", sum(e.checked)
from entries e
where 
e.startts >= date_part('epoch', '2020-10-01T15:01:50.859Z'::timestamp)::int8
and e.stopts <  date_part('epoch', '2021-11-08T15:01:50.859Z'::timestamp)::int8
group by "Date"

可选调整说明 #

• 若需要4位年份展示，将格式符中的YY替换为YYYY即可，输出格式会变为10/2020
• 若不需要月份前导零，将MM替换为FMMM即可，输出格式会变为10/20而非09/20，此时可在语句末尾添加order by min(e.startts)保证结果按时间顺序排列，避免字符串排序错乱

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