WITH 
  today AS (
    SELECT now() AS d
  ),
  yesterday AS (
    SELECT date_trunc('day', today.d - '1 day'::interval) AS d,
           extract(month from today.d - '1 day'::interval) AS m,
           extract(year from today.d - '1 day'::interval) AS y
      FROM today
  ),
  first_pv_of_yesterday AS (
    SELECT min(measurements.time) as t
      FROM measurements,
           yesterday
      WHERE time >= yesterday.d AND 
            time < yesterday.d + '1 day'::interval AND
            measurements.application = 'PV'
  ),
  last_pv_of_yesterday AS (
    SELECT max(measurements.time) as t
      FROM measurements,
           yesterday
      WHERE time >= yesterday.d AND 
            time < yesterday.d + '1 day'::interval AND
            measurements.application = 'PV'
  ),
  first_pv_energy_of_yesterday AS (
    SELECT cast(measurements.values->'ExportEnergyActive'->>'value' AS real) AS e
      FROM measurements,
           first_pv_of_yesterday
      WHERE measurements.time = first_pv_of_yesterday.t AND
            measurements.application = 'PV'
  ),
  last_pv_energy_of_yesterday AS (
    SELECT cast(measurements.values->'ExportEnergyActive'->>'value' AS real) AS e
      FROM measurements,
           last_pv_of_yesterday
      WHERE measurements.time = last_pv_of_yesterday.t AND
            measurements.application = 'PV'
  )
INSERT INTO pv_energy_by_month
  (month, year, baseline_energy)
  VALUES( 
    (SELECT m FROM yesterday),
    (SELECT y FROM yesterday),
    (SELECT e FROM first_pv_energy_of_yesterday)
  ) 
  ON CONFLICT ON CONSTRAINT pv_energy_by_month__month_year_unique
  DO UPDATE 
       SET last_changed = now(),
           update_counter = pv_energy_by_month.update_counter + 1,
           current_energy = (SELECT e FROM last_pv_energy_of_yesterday) - pv_energy_by_month.baseline_energy;

WITH 
  today AS (
    SELECT now() AS d
  ),
  yesterday AS (
    SELECT date_trunc('day', today.d - '1 day'::interval) AS d,
           extract(month from today.d - '1 day'::interval) AS m,
           extract(year from today.d - '1 day'::interval) AS y
      FROM today
  ),
  first_car_of_yesterday AS (
    SELECT min(measurements.time) as t
      FROM measurements,
           yesterday
      WHERE time >= yesterday.d AND 
            time < yesterday.d + '1 day'::interval AND
            measurements.application = 'Car'
  ),
  last_car_of_yesterday AS (
    SELECT max(measurements.time) as t
      FROM measurements,
           yesterday
      WHERE time >= yesterday.d AND 
            time < yesterday.d + '1 day'::interval AND
            measurements.application = 'Car'
  ),
  first_car_energy_of_yesterday AS (
    SELECT cast(measurements.values->'TotalImportEnergy'->>'value' AS real) AS e
      FROM measurements,
           first_car_of_yesterday
      WHERE measurements.time = first_car_of_yesterday.t AND
            measurements.application = 'Car'
  ),
  last_car_energy_of_yesterday AS (
    SELECT cast(measurements.values->'TotalImportEnergy'->>'value' AS real) AS e
      FROM measurements,
           last_car_of_yesterday
      WHERE measurements.time = last_car_of_yesterday.t AND
            measurements.application = 'Car'
  )
INSERT INTO car_energy_by_month
  (month, year, baseline_energy)
  VALUES( 
    (SELECT m FROM yesterday),
    (SELECT y FROM yesterday),
    (SELECT e FROM first_car_energy_of_yesterday)
  ) 
  ON CONFLICT ON CONSTRAINT car_energy_by_month__month_year_unique
  DO UPDATE 
       SET last_changed = now(),
           update_counter = car_energy_by_month.update_counter + 1,
           current_energy = (SELECT e FROM last_car_energy_of_yesterday) - car_energy_by_month.baseline_energy;
