Postgresql 时间统计

参考博客： Postgresql 按本年度,本月份,本周,今天统计数据_学习真香-CSDN博客

pg关于时间的统计  一些好的学习网址：                         PostgreSQL: Documentation: 9.1: Date/Time Functions and Operators

PostgreSQL 时间/日期函数和操作符 | 菜鸟教程

下面是一些自己做项目时的sql笔记，再此记录下

现在表结构创建时间乃是基础字段，想要练习，随便找个表，更换下字段就能进行运行了

1. 按日进行统计

SELECT date_trunc('DAY', create_time) as createTime,count(eqp_code) from equipment_info group by  createTime order by  createTime

当天时间 某时间段预警

select *
from equipment_info
where create_time>=current_date and date_part('hour',create_time)>=8 and date_part('hour',create_time)<10

 2. 周

按周进行统计，create_time显示为每周周一

SELECT date_trunc('WEEK', create_time) as createTime,count(eqp_code) from equipment_info group by  createTime

变态一点的需求，每周进行统计，显示日期为每周的周日

SELECT  (date_trunc( 'week', create_time :: TIMESTAMP ) + '6 days' :: INTERVAL ) :: DATE AS week_last_date,COUNT ( 1 ) 
FROM equipment_info 
where del_flag=0 
group by week_last_date
order by week_last_date

只统计本周的的数据，today 3为周三，为2则为周二

SELECT COUNT(tfn.*), EXTRACT ( isodow FROM tfn.create_time )  as today
   FROM fault_alert_record_processing tfn 
   WHERE
	 EXTRACT (YEAR FROM tfn.create_time ) = EXTRACT ( YEAR FROM NOW( ) ) 
	 AND EXTRACT ( WEEK FROM tfn.create_time ) = EXTRACT ( WEEK FROM NOW( ) ) 
   GROUP BY  today
	 order by  today

按周几的维度去统计

1. 创建日期折算成 周几
  SELECT EXTRACT(DOW FROM CURRENT_DATE)   
	SELECT EXTRACT(ISODOW FROM CURRENT_DATE)  
2. 创建日期折算成 周几
   SELECT EXTRACT(isodow FROM tfn.create_time) 周几
   FROM equipment_info tfn 
	 SELECT EXTRACT(dow FROM tfn.create_time) 周几
   FROM equipment_info tfn
3. 挑选创建日期 是周一的
  select * from   where extract(dow from equipment_info.create_time)=1 

 3.判定积压时间小于24小时的

select CURRENT_CATALOG
 2021-11-08 16:53:21+08
 select *
 from equipment_info 
 where (CURRENT_TIMESTAMP-equipment_info.create_time)<'1 DAY 00:00:00'

java获取当前月，每周最后一天的date和 每月的最后一天

 //获取当前月，每周最后一天的date和 每月的最后一天
        LocalDate localDate = LocalDate.of(year, month, 1);
        LocalDate lastDayOfMonth = localDate.with(TemporalAdjusters.lastDayOfMonth());
        LocalDate startDayOfMonth = lastDayOfMonth.with(TemporalAdjusters.firstDayOfMonth());
        LocalDate weekDay = startDayOfMonth.with(DayOfWeek.SUNDAY);
        Set<LocalDate> weekSet = new TreeSet<>();
        while (!weekDay.isAfter(lastDayOfMonth)) {
            weekSet.add(weekDay);
            weekDay = weekDay.plusWeeks(1);
            /*if (weekDay.isAfter(lastDayOfMonth)){
                set.add(lastDayOfMonth);
            }*/
        }
        List<LocalDate>  localDateList=new ArrayList(weekSet);
        List<Date>  weekList= localDateList.stream().map(r->{
            ZonedDateTime zonedDateTime = r.atStartOfDay(ZoneId.systemDefault());
            Date date = Date.from(zonedDateTime.toInstant());
            return date;
        }).collect(toList());