Create candlestick charts with native PostgreSQL first-last-aggregate
When you want to create Candlestick charts you need to group time series into time-frames like: Give me the highest, lowest and average value for each 1 hour of a day. To do so, you can use this native PostgreSQL query:
select min(cp.created) as created_min, max(cp.created) as created_max, cp.symbol_id, avg(cp.price) price_avg, max(cp.price) price_max, min(cp.price) price_min from generate_series(current_timestamp::timestamp-interval '7' day, current_timestamp::timestamp, interval '1' hour) as d(dt) join prices cp on (cp.created >= d.dt and cp.created < d.dt + interval '1' hour) where cp.symbol_id = 'LHA.DE' group by d.dt, cp.symbol_id order by d.dt; You will get a result like
