UNION in continuous aggregates

[iamnhphu]

It seems like UNION operator are not supported in continuous aggregates yet. In the meantime, is there any workaround that is performant?

[mfreed]

Hi @nhphu Can you share a bit more about the type of query you'd like to make?

[iamnhphu]

I would like to convert tick by tick data to OHLC data.

CREATE VIEW candle.eurusd
WITH (timescaledb.continuous)
AS
SELECT
  time_bucket('1 minute', time) as time,
  '1' as resolution,
  first(bid, time) as open,
  max(bid) as high,
  min(bid) as low,
  last(bid, time) as close,
  sum(volume) as volume
FROM tick.eurusd
GROUP BY 1, 2
UNION
SELECT
  time_bucket('5 minutes', time) as time,
  '5' as resolution,
  first(bid, time) as open,
  max(bid) as high,
  min(bid) as low,
  last(bid, time) as close,
  sum(volume) as volume
FROM tick.eurusd
GROUP BY 1, 2;

I want a single prepared statement work with all resolution so I try to combine all the result.

[morganchristiansson]

I wanted to combine and filter 2 source tables using union...

CREATE VIEW combined
WITH (timescaledb.continuous)
AS
SELECT time_bucket('1h', time) AS time, type, value
  FROM source_one
  WHERE type <> 'data_in_source_two'
  GROUP BY 1, 2
UNION
SELECT time_bucket('1h', time) AS time, type, value
  FROM source_two
  GROUP BY 1, 2;

[aarondglover]

Hi,

I was hoping to do the same, but rather than combine two different sources, to aggregate the data into different timezones. UTC and 'Australia/Sydney'

CREATE MATERIALIZED VIEW SB2501AINTERVAL_AGG
WITH (timescaledb.continuous, timescaledb.materialized_only = false) AS

  SELECT TIME_BUCKET('1 Days', "TIMESTAMP", interval '1 SEC') - interval '1 SEC' AS TS
      ,'UTC' AS TZ
      ,MD.METER_ID
      ,SUM(VALUE::NUMERIC) AS SUM_VALUE
  FROM METER_DATA MD
  GROUP BY 
           MD.METER_ID
      ,TS
      ,TZ

  UNION ALL

  SELECT TIME_BUCKET('1 Days', "TIMESTAMP" AT TIME ZONE 'Australia/Sydney', interval '1 SEC') - interval '1 SEC' AS TS
      ,'Australia/Sydney' AS TZ
      ,MD.METER_ID
      ,SUM(VALUE::NUMERIC) AS SUM_VALUE
  FROM METER_DATA MD
  GROUP BY MD.METER_ID
      ,TS
      ,TZ

with data ;

Unfortunately

ERROR:  invalid continuous aggregate query
DETAIL:  UNION, EXCEPT & INTERSECT are not supported by continuous aggregates
SQL state: 0A000

I guess I'm back to square one - creating a continuous aggregation PER TIME ZONE :(

Note this is the support a dashboard where users can choose a Timezone in which to view the data in.