PostGres常用的日期和时间函数

详细使用请参考官方文档：http://www.postgres.cn/docs/10/functions-datetime.html

一、需求

有一张工单表biz_ticket，建表SQL如下：

CREATE TABLE "public"."biz_ticket" (
"id" int4 DEFAULT nextval('"public".biz_ticket_id_seq'::regclass) primary key,
"create_time" timestamp(6),
"type" text 
)
WITH (OIDS=FALSE)
;

ALTER TABLE "public"."biz_ticket" OWNER TO "postgres";

COMMENT ON COLUMN "public"."biz_ticket"."id" IS '自增主键';

COMMENT ON COLUMN "public"."biz_ticket"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."biz_ticket"."type" IS '类型';

向表中插入数据(用户可自定义)：

INSERT INTO "biz_ticket" VALUES (1, '2019-9-26 11:06:05', 'REQ');
INSERT INTO "biz_ticket" VALUES (2, '2019-9-26 11:06:50', 'BUG');
INSERT INTO "biz_ticket" VALUES (3, '2019-9-26 11:07:00', 'REQ');
INSERT INTO "biz_ticket" VALUES (4, '2019-9-26 11:07:17', 'OPPTY');
INSERT INTO "biz_ticket" VALUES (5, '2019-9-26 11:07:29', 'BUG');
INSERT INTO "biz_ticket" VALUES (6, '2019-9-26 11:07:58', 'BUG');
INSERT INTO "biz_ticket" VALUES (7, '2019-8-26 11:13:00', 'REQ');
INSERT INTO "biz_ticket" VALUES (8, '2018-9-26 11:13:22', 'REQ');
INSERT INTO "biz_ticket" VALUES (9, '2018-9-26 11:13:49', 'REQ');
INSERT INTO "biz_ticket" VALUES (10, '2019-8-26 11:16:00', 'BUG');

要求对工单类型进行统计分析！具体要求如下：

1.1 按日查询统计

1.1.1 呈现工单创建时间为当日（含）及之前7天的数据总条数：

知识点：

SELECT CURRENT_TIMESTAMP as now, current_date - INTERVAL '7days' as pre;

实现：

select count(*) from biz_ticket t
where t.create_time<=CURRENT_TIMESTAMP
and t.create_time>=current_date - INTERVAL '7days';

1.1.2 呈现工单创建时间为当日（含）及之前7天的问题类型占比：

知识点：1、with查询；2、to_char函数；

select to_char( 3*100.0/9, '99D99'); -- 返回33.33

实现：

with t1 as(
select count(*) as tc from biz_ticket t
where t.create_time<=CURRENT_TIMESTAMP
and t.create_time>=current_date - INTERVAL '7days'
),
t2 as(
select count(*) as tc from biz_ticket t
where t.create_time<=CURRENT_TIMESTAMP
and t.create_time>=current_date - INTERVAL '7days'
and t."type"='OPPTY'
)
select t2.tc *100.0 / t1.tc as 占比 from t1,t2;

 

1.2 按周查询统计

数据呈现上一个自然周7天内的工单总数；

知识点：

1、extract函数；

2、dow：一周中的星期几，周日（0）-周六（6）

3、case；

实现方式一：

SELECT count(*) FROM biz_ticket T
WHERE 
EXTRACT (week FROM T .create_time) = EXTRACT (
		-- 上一个自然周
		week FROM CURRENT_DATE - INTERVAL '1week'
	)

实现方式二：

with now_t as(
SELECT extract(dow from CURRENT_DATE) as now
),
mon_t as (
 select 
 case when now_t.now='0' then CURRENT_DATE - INTERVAL '7days'
			when now_t.now='1' then CURRENT_DATE - INTERVAL '6days'
			when now_t.now='2' then CURRENT_DATE - INTERVAL '5days'
			when now_t.now='3' then CURRENT_DATE - INTERVAL '4days'
			when now_t.now='4' then CURRENT_DATE - INTERVAL '3days'
			when now_t.now='5' then CURRENT_DATE - INTERVAL '2days'
			when now_t.now='6' then CURRENT_DATE - INTERVAL '1days'
  end
 as mon from now_t
)
select count(*) from biz_ticket t,mon_t
where t.create_time >= mon_t.mon
and t.create_time <= mon_t.mon + INTERVAL '7days';

 

1.3 按月查询统计

数据呈现上一个自然月（含）及之前7个月的数据总数；

select count(*) from biz_ticket t 
where extract(month from t.create_time ) <= extract(month from CURRENT_DATE - INTERVAL '1month')
and 
extract(month from t.create_time ) >=extract(month from CURRENT_DATE - INTERVAL '8month')

 

1.4 按年查询统计

数据呈现当年1月-当前日期的上一个月的数据总数量

select count(*) from biz_ticket t 
where extract(month from t.create_time ) <= extract(month from CURRENT_DATE - INTERVAL '1month')
and 
extract(month from t.create_time ) >=1

本例中有失误或待优化的地方欢迎大家留言！