试试看：

select
    extract(year from activity_date) as year,
    to_char(activity_date, 'Mon') as month,
    count(*) as count
from
    crimes
group by
    1,extract(month from activity_date);

字符串

如何在PostgreSQL中从时间戳（带/不带时区）中获取月份？

使用函数EXTRACT（字段FROM source）

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2

字符串
文档链接：https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

我喜欢用途：

SELECT DATE_TRUNC('month', activity_date AT TIME ZONE 'UTC') AS month

字符串
那么结果会是这样的：

month                              count
2023-10-01 00:00:00.000              1
2023-11-01 00:00:00.000              2

型
如果你想用week来计数，你可以把它改成：

SELECT DATE_TRUNC('week', activity_date AT TIME ZONE 'UTC') AS week

型
或按year计数：

SELECT DATE_TRUNC('year', activity_date AT TIME ZONE 'UTC') AS year

型
但是如果你想按date计数，上面的SQL是不起作用的，请坚持：

SELECT DATE(conversation.created_date) AS day

型

Declare @zipcode int;
set @zipcode=12345;
select format(activity_date,'yyyy-MM') month ,count(1) count from ct
where zipcode=@zipcode
group by format(ad,'yyyy-MM')

字符串