pv-controller/schema/queries01.sql

-- current year's gain
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 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
    ),
    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,
        (select energy from last_value) - (select energy from first_value) as yield;



-- current month's gain
with 
    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 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
    ),
    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
        (select day from first_day_in_month) as v1,
        (select energy from first_value) as v2,
        (select energy from last_value) as v3,
        to_char((select day from first_day_in_month), 'Month') as period_value,
        'Month' as period_name,
        (select energy from last_value) - (select energy from first_value) as yield;


            
-- current day's gain
with
    values as (
        select
                time_bucket('1 day', time) as interval,
                first(exportenergyactive, time) as first_value,
                last(exportenergyactive, time) as last_value
            from pv_power_measurement_t
            where time >= date_trunc('day', now())
            group by interval
    )
    select
        (select interval from values)::date::text as period_value,
        'Day' as period_name,
        (select last_value from values) - (select first_value from values) as yield;


-- all in one
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', time) 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;