energyanalysis/function_total_energy_by_day_and_device.sql

create table rt_totalizer_result (
  day date,
  device text,
  total double precision
);

select total_energy_by_day_and_device('2021-05-01','2021-05-10','Computer');

create or replace function total_energy_by_day_and_device(p_start_day date, p_end_say date, p_device varchar)
  returns setof rt_totalizer_result 
  language plpgsql
  as $$
  declare
    v_firstEnergy double precision;
    v_lastEnergy double precision;
    v_total double precision;
    v_day date;
    v_result rt_totalizer_result%rowtype;
  begin
    v_day := p_start_day;
    loop
      select energy
        into v_lastEnergy
        from power_measurement_t
          where time = (
                        select max(time)
                          from power_measurement_t
                          where time between v_day + time '00:00' and v_day + time '00:00' + interval '24h' and
                                deviceid = p_device and
                                status = 'Ok'
                      ) and
                deviceid = p_device;
      select energy
        into v_firstEnergy
        from power_measurement_t
          where time = (
                        select min(time)
                          from power_measurement_t
                          where time between v_day + time '00:00' and v_day + time '00:00' + interval '24h' and 
                                deviceid = p_device and 
                                status = 'Ok'
                      ) and
                deviceid = p_device;
      v_total := v_lastEnergy - v_firstEnergy;
      
      v_result.day := v_day;
      v_result.device := p_device;
      v_result.total := v_total;
      return next v_result;
      v_day := v_day + interval '1 day';
      exit when v_day > p_end_say;
    end loop;
    return;
  end;
$$;


select total_energy('2021-05-01','2021-05-10');

create or replace function total_energy(p_start_day date, p_end_say date)
  returns setof rt_totalizer_result 
  language plpgsql
  as $$
  declare
    v_firstEnergy double precision;
    v_lastEnergy double precision;
    v_total double precision;
    v_day date;
    v_deviceid text;
    v_result rt_totalizer_result%rowtype;
  begin
    for v_deviceid in select distinct deviceid 
                        from power_measurement_t 
    loop
      v_day := p_start_day;
      loop
        select energy
          into v_lastEnergy
          from power_measurement_t
            where time = (
                          select max(time)
                            from power_measurement_t
                            where time between v_day + time '00:00' and v_day + time '00:00' + interval '24h' and
                                  deviceid = v_deviceid and
                                  status = 'Ok'
                        ) and
                  deviceid = v_deviceid;
        select energy
          into v_firstEnergy
          from power_measurement_t
            where time = (
                          select min(time)
                            from power_measurement_t
                            where time between v_day + time '00:00' and v_day + time '00:00' + interval '24h' and 
                                  deviceid = v_deviceid and 
                                  status = 'Ok'
                        ) and
                  deviceid = v_deviceid;
        v_total := v_lastEnergy - v_firstEnergy;
        
        v_result.day := v_day;
        v_result.device := v_deviceid;
        v_result.total := v_total;
        return next v_result;
        v_day := v_day + interval '1 day';
        exit when v_day > p_end_say;
      end loop;
    end loop;
    return;
  end;
$$;