postgresql按照年月日统计历史数据

1.按照日

SELECT a.time,COALESCE(b.counts,0) as counts from
(
SELECT
to_char ( b, 'YYYY-MM-DD' ) AS time
FROM
generate_series ( to_timestamp ( '2024-06-01', 'YYYY-MM-DD hh24:mi:ss' ), to_timestamp ( '2024-06-30', 'YYYY-MM-DD hh24:mi:ss' ), '1 days' ) AS b
GROUP BY
time ORDER BY time asc) as aFULL OUTER JOIN(
select to_char(to_timestamp(create_time/1000)::TIMESTAMP, 'YYYY-MM-DD' ) AS starttime, count(*) as counts from t_work_order GROUP BY starttime
) as b
on a.time=b.starttime
WHERE time is not null
order by a.time asc

2.按照月

SELECT a.time,COALESCE(b.counts,0) as counts from
(
SELECT
to_char ( b, 'YYYY-MM' ) AS time
FROM
generate_series ( to_timestamp ( '2024-01-01', 'YYYY-MM' ), to_timestamp ( '2024-06-30', 'YYYY-MM' ), '1 months' ) AS b
GROUP BY
time ORDER BY time asc) as aFULL OUTER JOIN(
select to_char(to_timestamp(create_time/1000)::TIMESTAMP, 'YYYY-MM' ) AS starttime, count(*) as counts from t_work_order GROUP BY starttime
) as b
on a.time=b.starttime
WHERE time is not null
order by a.time asc

3.按照年

SELECT a.time,COALESCE(b.counts,0) as counts from
(
SELECT
to_char ( b, 'YYYY' ) AS time
FROM
generate_series ( to_timestamp ( '2022-01-01', 'YYYY' ), to_timestamp ( '2024-06-30', 'YYYY' ), '1 years' ) AS b
GROUP BY
time ORDER BY time asc) as aFULL OUTER JOIN(
select to_char(to_timestamp(create_time/1000)::TIMESTAMP, 'YYYY' ) AS starttime, count(*) as counts from t_work_order GROUP BY starttime
) as b
on a.time=b.starttime
order by a.time asc

4.表结构如下,时间为时间戳

CREATE TABLE "public"."t_work_order" ("id" int8 NOT NULL,"tenant_no" int4 NOT NULL DEFAULT 1,"order_code" varchar(64) COLLATE "pg_catalog"."default","order_type_id" int8,"order_type_name" varchar(64) COLLATE "pg_catalog"."default","order_type_code" varchar(64) COLLATE "pg_catalog"."default","order_sub_type_id" int8,"order_sub_type_name" varchar(64) COLLATE "pg_catalog"."default","order_sub_type_code" varchar(64) COLLATE "pg_catalog"."default","order_source_id" int8,"order_source_name" varchar(64) COLLATE "pg_catalog"."default","order_source_code" varchar(64) COLLATE "pg_catalog"."default","area_id" int8,"area_name" varchar(64) COLLATE "pg_catalog"."default","asset_id" int8,"asset_name" varchar(255) COLLATE "pg_catalog"."default","device_id" int8,"device_name" varchar(255) COLLATE "pg_catalog"."default","project_id" int8,"project_name" varchar(255) COLLATE "pg_catalog"."default","description" varchar(1024) COLLATE "pg_catalog"."default","risk_level_id" int8,"risk_level_name" varchar(64) COLLATE "pg_catalog"."default","risk_level_code" varchar(64) COLLATE "pg_catalog"."default","release_user_id" int8,"release_user_name" varchar(64) COLLATE "pg_catalog"."default","release_time" int8,"resolver_user_id" int8,"resolver_user_name" varchar(64) COLLATE "pg_catalog"."default","resolver_time" int8,"expected_processing_time" int8,"response_time" int8,"arrive_time" int8,"finish_time" int8,"start_time" int8,"handle_duration" int8,"hang_time" int8,"hang_duration" int8,"status" varchar(64) COLLATE "pg_catalog"."default","extra_process_id" int8,"third_code" varchar(256) COLLATE "pg_catalog"."default","hasten_status" int4,"hasten_time" int8,"create_time" int8,"create_by" varchar(64) COLLATE "pg_catalog"."default","update_time" int8,"update_by" varchar(64) COLLATE "pg_catalog"."default","del" int4,"release_user_phone" varchar(32) COLLATE "pg_catalog"."default","resolver_user_phone" varchar(32) COLLATE "pg_catalog"."default","create_user_name" varchar(64) COLLATE "pg_catalog"."default","create_user_phone" varchar(32) COLLATE "pg_catalog"."default","star_number" int4,"check_timeout_flag" int4 DEFAULT 2,"check_timeout_level" int4 DEFAULT 0,"check_timeout_start_time" int8,"star_number_quality" int4,"evaluate_task" int4,"organization_id" int8,"organization_name" varchar(255) COLLATE "pg_catalog"."default","organization_area_id" int8,"organization_area_name" varchar(256) COLLATE "pg_catalog"."default","cooperate_status" int4 DEFAULT 2,"cooperate_user_count" int4 DEFAULT 0,CONSTRAINT "pk_t_work_order" PRIMARY KEY ("id"),CONSTRAINT "t_work_order_order_code_key" UNIQUE ("order_code")
)
;ALTER TABLE "public"."t_work_order" OWNER TO "huishi";CREATE INDEX "t_work_order_create_by_idx" ON "public"."t_work_order" USING btree ("create_by" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);CREATE INDEX "t_work_order_release_user_id_idx" ON "public"."t_work_order" USING btree ("release_user_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);CREATE INDEX "t_work_order_resolver_user_id_idx" ON "public"."t_work_order" USING btree ("resolver_user_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);COMMENT ON COLUMN "public"."t_work_order"."tenant_no" IS '租户分区标识';COMMENT ON TABLE "public"."t_work_order" IS '工单记录表';