Skip to content
Advertisement

Round timestamp to 30 minutes interval to group by

Problem

select currency,
       MAX (CASE WHEN type = 'Bank A' THEN rate ELSE null END) as bank_a_rate,
       MAX (CASE WHEN type = 'Bank B' THEN rate ELSE null END) as bank_b_rate
from rates
group by currency, created

I want to group my data by currency, timestamp and show all type value like table of comparison with interval of 30 minutes, for now my created time is 1 minute or less different so if i group with created time it will still showing 4 rows cause of different timestamp, is there a way to round the timestamp ?

Data Source

Type Currency Rate Created
Bank A USD 3.4 2020-01-01 12:29:15
Bank B USD 3.34 2020-01-01 12:30:11
Bank A EUR 4.92 2020-01-01 12:31:01
Bank B EUR 5.03 2020-01-01 12:31:14

Expected Result

Timestamp Currency Bank A Rate Bank B Rate
2020-01-01 12:30:00 USD 3.4 3.34
2020-01-01 12:30:00 EUR 4.92 5.03

Advertisement

Answer

Truncate/round created to 30 minutes (the ts expression) and group by it. Your query with this amendment:

select date_trunc('hour', created) + 
       interval '1 minute' * (extract(minute from created)::integer/30)*30 AS ts, 
       currency,
       MAX (CASE WHEN "type" = 'Bank A' THEN rate ELSE null END) as bank_a_rate,
       MAX (CASE WHEN "type" = 'Bank B' THEN rate ELSE null END) as bank_b_rate
from rates
group by currency, ts;

SQL Fiddle

‘Inherit’ previous rate

select ts, currency, 
  coalesce(bank_a_rate, lag(bank_a_rate) over w) bank_a_rate,
  coalesce(bank_b_rate, lag(bank_b_rate) over w) bank_b_rate
from
(           
 select date_trunc('hour', created) + 
       interval '1 minute' * (extract(minute from created)::integer/30)*30 ts, 
       currency,
       MAX (CASE WHEN "type" = 'Bank A' THEN rate ELSE null END) as bank_a_rate,
       MAX (CASE WHEN "type" = 'Bank B' THEN rate ELSE null END) as bank_b_rate
 from rates
 group by currency, ts
) t
window w as (partition by currency order by ts);

SQL Fiddle

6 People found this is helpful
Advertisement