Query combining two tables

TimescaleDB and PostgreSQL
1

I have two tables both containing a timestamp and a value. One Table has high frequency entried for the used kWh during the time period. The other is low frequency and has only entries when the produced type changes. I know would like to have a query aggregating per hour the sum of the kWh according to type.
Result could look like

Time, kWh, Typ
5:00, 4.2, 1
5:00, 8.3, 2
6:00, 2.2, 1
6:00, 4.3, 2
7:00, 9.3, 2
8:00, 7.3, 2

and so on depending how many types were running in this hour.

Is it possible to get this data with a SQL query and what would the query look like?

Thank You

2

Hi @Tesla2k, here’s a simple POC:

-- Create the hypertables
CREATE TABLE consumption (
    time TIMESTAMPTZ NOT NULL,
    kwh DOUBLE PRECISION NOT NULL
);

CREATE TABLE type_changes (
    time TIMESTAMPTZ NOT NULL,
    type_id INTEGER NOT NULL
);

-- Convert to hypertables
SELECT create_hypertable('consumption', 'time');
SELECT create_hypertable('type_changes', 'time');

-- Insert sample data
-- Consumption data (every 15 minutes)
INSERT INTO consumption VALUES
    ('2024-01-01 05:00:00', 1.1),
    ('2024-01-01 05:15:00', 1.2),
    ('2024-01-01 05:30:00', 1.3),
    ('2024-01-01 05:45:00', 1.4),
    ('2024-01-01 06:00:00', 0.6),
    ('2024-01-01 06:15:00', 0.7),
    ('2024-01-01 06:30:00', 0.8),
    ('2024-01-01 06:45:00', 0.9),
    ('2024-01-01 07:00:00', 2.1),
    ('2024-01-01 07:15:00', 2.3),
    ('2024-01-01 07:30:00', 2.4),
    ('2024-01-01 07:45:00', 2.5);

-- Type changes (less frequent)
INSERT INTO type_changes VALUES
    ('2024-01-01 05:00:00', 1),
    ('2024-01-01 05:30:00', 2),
    ('2024-01-01 06:15:00', 1),
    ('2024-01-01 07:00:00', 2);

-- Query to get hourly consumption by type
WITH time_ranges AS (
    SELECT 
        time_bucket('1 hour', c.time) AS hour,
        c.time,
        c.kwh,
        last_value(tc.type_id) OVER (
            ORDER BY tc.time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS type_id
    FROM consumption c
    LEFT JOIN LATERAL (
        SELECT type_id, time 
        FROM type_changes 
        WHERE time <= c.time
        ORDER BY time DESC 
        LIMIT 1
    ) tc ON true
)
SELECT 
    hour,
    sum(kwh) as total_kwh,
    type_id
FROM time_ranges
GROUP BY hour, type_id
ORDER BY hour, type_id;
3

Thank You very much! The query takes quite long with my real data, so I tried an continuous aggregate for it which fails because of the window function. I modified the query like this which gives me the same result and work in the continuous aggregate.
Do you see any disadvantages or errors with this new query?

SELECT
    time_bucket('1 hour', c.time) AS hour,
    sum(c.kwh),
    type_id
   FROM consumption c
     LEFT JOIN LATERAL ( SELECT type_id,
            time
           FROM type_changes
          WHERE time <= c."time" 
          ORDER BY time DESC
         LIMIT 1) tc ON true
	GROUP BY hour,type_id
	ORDER BY hour,type_id
4

Hey @Tesla2k ! Yes, it seems the simplification works and is faster than use the WINDOW.

That’s great!