I have a query that produces a timestamp and a value at that timestamp.
select timestamp, value
from table1;
So, a sample from the data would look like this:
timestamp | value |
---------------------------------------
2019-02-28 01:00:00 | 1 |
2019-02-28 01:10:00 | 1 |
2019-02-28 01:20:00 | 6 |
2019-02-28 01:30:00 | 17 |
2019-02-28 01:40:00 | 4 |
2019-02-28 01:50:00 | 4 |
I have a second query that produces a start timestamp and an end timestamp.
select start, end
from table2;
A sample from that would look like this:
start | end |
-------------------------------------------
2019-02-28 01:00:00 | 2019-02-28 01:30:00 |
2019-02-28 01:40:00 | 2019-02-28 01:50:00 |
How would I be able to combine the two queries to produce an output like this?
start | end | values |
-------------------------------------------------------------
2019-02-28 01:00:00 | 2019-02-28 01:30:00 | {1,1,6} |
2019-02-28 01:40:00 | 2019-02-28 01:50:00 | {4} |
Aggregation and join:
select t2.start, t2.end, array_agg(t1.value)
from table2 t2 left join
table1 t1
on t1.timestamp >= t2.start and t1.timestamp < t2.end
group by t2.start, t2.end;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments