CREATE OR REPLACE VIEW vit.values_bihourly AS
WITH date_range AS (
    SELECT 
        MIN(datetimeoffset) AS min_date,
        MAX(datetimeoffset) AS max_date
    FROM vit.meter_read
),
bihourly_grid AS (
    SELECT generate_series(
        (SELECT min_date FROM date_range),
        (SELECT max_date FROM date_range) + INTERVAL '1 day',
        INTERVAL '2 hours'
    ) AS grid_time
)
SELECT
    h.street,
    h.number AS house,
    m.manufacturer,
    m.model,
    m.serialnumber AS serial_number,
    g.grid_time AS date,
    readings.firsttariffvalue AS t1,
    readings.secondtariffvalue AS t2,
    readings.thirdtariffvalue AS t3
FROM vit.meter m
JOIN vit.house h ON h.id = m.house_id
CROSS JOIN bihourly_grid g
LEFT JOIN LATERAL (
    SELECT 
        mr.firsttariffvalue,
        mr.secondtariffvalue,
        mr.thirdtariffvalue
    FROM vit.meter_read mr
    WHERE mr.meter_id = m.id
        AND mr.datetimeoffset <= g.grid_time::date
    ORDER BY mr.datetimeoffset DESC
    LIMIT 1
) AS readings ON true
WHERE m.apartments_id IS NULL  
  AND m.house_id IS NOT NULL
  AND readings.firsttariffvalue IS NOT NULL 
ORDER BY 
    street, 
    house, 
    manufacturer, 
    model, 
    serial_number, 
    date;
Select * from vit.values_bihourly