68 lines
2.6 KiB
SQL
68 lines
2.6 KiB
SQL
with
|
|
first_day_in_year as (
|
|
select
|
|
date_trunc('day', min(time)) as day
|
|
from pv_power_measurement_t
|
|
where
|
|
time between date_trunc('year', time) and now()
|
|
),
|
|
first_value_in_year as (
|
|
select
|
|
time_bucket('1 day', time) as interval,
|
|
first(exportenergyactive, time) as energy
|
|
from pv_power_measurement_t
|
|
where
|
|
time between (select day from first_day_in_year) and (select day from first_day_in_year) + interval '1 day' and
|
|
status = 'Ok'
|
|
group by interval
|
|
),
|
|
first_day_in_month as (
|
|
select
|
|
date_trunc('day', min(time)) as day
|
|
from pv_power_measurement_t
|
|
where
|
|
time between date_trunc('month', now()) and now()
|
|
),
|
|
first_value_in_month as (
|
|
select
|
|
time_bucket('1 day', time) as interval,
|
|
first(exportenergyactive, time) as energy
|
|
from pv_power_measurement_t
|
|
where
|
|
time between (select day from first_day_in_month) and (select day from first_day_in_month) + interval '1 day' and
|
|
status = 'Ok'
|
|
group by interval
|
|
),
|
|
first_value_in_day as (
|
|
select
|
|
time_bucket('1 day', time) as interval,
|
|
first(exportenergyactive, time) as energy
|
|
from pv_power_measurement_t
|
|
where time >= date_trunc('day', now())
|
|
group by interval
|
|
),
|
|
last_value as (
|
|
select
|
|
time_bucket('1 day', time) as interval,
|
|
last(exportenergyactive, time) as energy
|
|
from pv_power_measurement_t
|
|
where
|
|
time between date_trunc('day', now()) and date_trunc('day', now()) + interval '1 day' and
|
|
status = 'Ok'
|
|
group by interval
|
|
)
|
|
select
|
|
extract(year from (select day from first_day_in_year))::text as period_value,
|
|
'Year' as period_name,
|
|
round(((select energy from last_value) - (select energy from first_value_in_year))::numeric, 2) as yield
|
|
union
|
|
select
|
|
to_char((select day from first_day_in_month), 'Month') as period_value,
|
|
'Month' as period_name,
|
|
round(((select energy from last_value) - (select energy from first_value_in_month))::numeric, 2) as yield
|
|
union
|
|
select
|
|
now()::date::text as period_value,
|
|
'Day' as period_name,
|
|
round(((select energy from last_value) - (select energy from first_value_in_day))::numeric, 2) as yield;
|