psql按年月天小时分钟秒汇总数据

# 时间字段：f_create_time
# 金额字段：f_trans_amount
# 开始时间：start_time
# 结束时间：end_time

# 按年汇总
select 
    to_char(f_create_time, 'YYYY') as t, 
    count(*) as total_count,
    sum(f_trans_amount) as total_amount
from table_name
where f_create_time between start_time and end_time
group by t;

# 按月汇总
select 
    to_char(f_create_time, 'YYYY-MM') as t, 
    count(*) as total_count,
    sum(f_trans_amount) as total_amount
from table_name
where f_create_time between start_time and end_time
group by t;

# 按天汇总 方式一
select 
    to_char(f_create_time, 'YYYY-MM-DD') as t, 
    count(*) as total_count,
    sum(f_trans_amount) as total_amount
from table_name
where f_create_time between start_time and end_time
group by t;

# 按天汇总 方式二
SELECT
f_create_time::date,
COUNT(*) AS count,
SUM(f_trans_amount) AS total_amount
FROM table_name
WHERE f_create_time >= '2020-01-01 00:00:00'
AND f_create_time <= '2020-03-01 23:59:59'
GROUP BY f_create_time::date
order by 1;

# 按小时汇总
select 
    to_char(f_create_time, 'YYYY-MM-DD HH24') as t, 
    count(*) as total_count,
    sum(f_trans_amount) as total_amount
from table_name
where f_create_time between start_time and end_time
group by t;

# 按分钟汇总
select 
    to_char(f_create_time, 'YYYY-MM-DD HH24:MI') as t, 
    count(*) as total_count,
    sum(f_trans_amount) as total_amount
from table_name
where f_create_time between start_time and end_time
group by t;

# 按秒汇总
select 
    to_char(f_create_time, 'YYYY-MM-DD HH24:MI:SS') as t, 
    count(*) as total_count,
    sum(f_trans_amount) as total_amount
from table_name
where f_create_time between start_time and end_time
group by t;

代元培