On Thu, Jul 30, 2015 at 4:51 AM, Ram N <yramiyer@xxxxxxxxx> wrote:
select sum(a), count(id), a.ts, st from table1 a, table2 b where a.ts > b.start_date and a.ts < b.end_date and a.ts > '2015-01-01 20:50:44.000000 +00:00:00' and a.ts < '2015-07-01 19:50:44.000000 +00:00:00' group by a.ts, st order by a.ts
You could try to use a range type:
CREATE INDEX ON table2 USING gin (tstzrange(start_date, end_date, '()'));
Then:
select sum(a), count(id), a.ts, st
from table1 a, table2 b
where tstzrange(b.start_date, b.end_date, '()') @> a.ts
and a.ts < '2015-07-01 19:50:44.000000 +00:00:00'
group by a.ts, st
order by a.ts
select sum(a), count(id), a.ts, st
from table1 a, table2 b
where tstzrange(b.start_date, b.end_date, '()') @> a.ts
and a.ts < '2015-07-01 19:50:44.000000 +00:00:00'
group by a.ts, st
order by a.ts
Regards,
--
Matheus de Oliveira
