Billing period average (month to date)

We use the SQL Integration to calculate the current month's average from Home Assistant's Long Term Statistics.

sql:
  - name: omie_spot_pt_month_to_date
    unique_id: omie_spot_pt_month_to_date
    query: >
      SELECT datetime(range.start_billing_ts, 'unixepoch') AS window_start,
             datetime(range.start_of_day_ts, 'unixepoch') AS window_end,
             datetime(min(start_ts), 'unixepoch') AS oldest_measurement,
             datetime(max(start_ts), 'unixepoch') AS newest_measurement,
             count(1) AS measurement_count,
             round(avg(CASE WHEN strftime('%H', datetime(s.start_ts, 'unixepoch')) < '08' OR strftime('%H', datetime(s.start_ts, 'unixepoch')) >= '22' THEN s.mean ELSE NULL END), 2) AS mean_vazio,
             round(avg(CASE WHEN strftime('%H', datetime(s.start_ts, 'unixepoch')) < '08' OR strftime('%H', datetime(s.start_ts, 'unixepoch')) >= '22' THEN NULL ELSE s.mean END), 2) AS mean_fora_vazio,
             round(avg(s.mean), 2) AS mean
      FROM   statistics s JOIN statistics_meta m ON s.metadata_id = m.id, (
               SELECT
                 unixepoch(CASE
                   WHEN CAST(strftime('%d', datetime()) AS INTEGER) > billing_offset THEN datetime('now', 'start of month', billing_offset||' days')
                   ELSE datetime('now', 'start of month', '-1 month', billing_offset||' days')
                 END) AS start_billing_ts,
                 unixepoch(datetime('now', 'start of day')) AS start_of_day_ts
               FROM (
                  -- ⚠️ ADJUST HERE AS NECESSARY (e.g. 20 to start billing cycle at midnight on day 21):
                  SELECT 20 AS billing_offset
              ) cfg) range
      WHERE  statistic_id = 'sensor.omie_spot_price_pt'
        AND  start_ts >= range.start_billing_ts
        AND  start_ts < range.start_of_day_ts;
    column: 'mean'
    unit_of_measurement: '€/MWh'

MariaDB

sql:
  - name: omie_spot_pt_month_to_date
    unique_id: omie_spot_pt_month_to_date
    query: |-
      SELECT FROM_UNIXTIME(stats.start_billing_ts) AS window_start,
             FROM_UNIXTIME(stats.start_of_day_ts) AS window_end,
             FROM_UNIXTIME(MIN(start_ts)) AS oldest_measurement,
             FROM_UNIXTIME(MAX(start_ts)) AS newest_measurement,
             COUNT(*) AS measurement_count,
             ROUND(AVG(s.mean), 2) AS mean
      FROM statistics s 
      JOIN statistics_meta m ON s.metadata_id = m.id 
      CROSS JOIN (
          SELECT UNIX_TIMESTAMP(CASE 
                                WHEN DAY(NOW()) > billing_offset 
                                THEN CONCAT_WS('-', YEAR(NOW()), MONTH(NOW()), billing_offset) 
                                ELSE CONCAT_WS('-', YEAR(NOW() - INTERVAL 1 MONTH), MONTH(NOW() - INTERVAL 1 MONTH), billing_offset) 
                                END) AS start_billing_ts,
                UNIX_TIMESTAMP(CURDATE()) AS start_of_day_ts
          FROM (
              -- ⚠️ ADJUST HERE AS NECESSARY (e.g. 20 to start billing cycle at midnight on day 21):
              SELECT 20 AS billing_offset
              ) cfg
      ) AS stats
      WHERE statistic_id = 'sensor.omie_spot_price_pt' 
      AND start_ts >= stats.start_billing_ts 
      AND start_ts < stats.start_of_day_ts;
    column: 'mean'
    unit_of_measurement: '€/MWh'