# 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

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