Timezone handling for Materialized views

RCata · November 10, 2023, 1:18pm

In brief:
I know for a fact that timescale does not support the creation of materialized continuous aggregates while using the at time zone <timezone>' being the type of the column to be aggregated timezonetz`
so, I thought on creating a bunch of columns for each timezone to add them as dimensions to the hypertable and use those columns to create materialized views based on each column with the timezone required. But I’m getting an error that the column I’m pointing is not part of the dimension of the hypertable although when i add the dimension I get the error that it is already a dimension of the hypertable. So, once more how could I create materialized views for continuos aggregates to handle timezones?

TimescaleDB version: 2.12.0
PostgreSQL version: PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.2.1 20230801, 64-bit
Other software:
OS: Linux

So basically when I do the next query:

select     time_bucket(interval '1 hour', bucket at time zone 'Etc/GMT-1') as "hourly_bucket_Etc/GMT-1",
		   time_bucket(interval '1 hour', bucket at time zone 'Etc/GMT+4') as "hourly_bucket_Etc/GMT+4",
           time_bucket(interval '1 hour', bucket at time zone 'Etc/GMT+5') as "hourly_bucket_Etc/GMT+5",
           time_bucket(interval '1 hour', bucket at time zone 'Etc/GMT+6') as "hourly_bucket_Etc/GMT+6",
           time_bucket(interval '1 hour', bucket at time zone 'Etc/GMT+7') as "hourly_bucket_Etc/GMT+7",
           time_bucket(interval '1 hour', bucket at time zone 'Etc/GMT+8') as "hourly_bucket_Etc/GMT+8",
           time_bucket(interval '1 hour', bucket at time zone 'Etc/GMT') as "hourly_bucket_Etc/GMT",

I get the result I would like so then I could create a materialized view from the query but timescale giving a error back when trying it

create materialized view "data_hours"
with (timescaledb.continuous) as 
	select time_bucket(interval '1 hour', bucket at time zone 'Etc/GMT+5') as "hourly_bucket_Etc/GMT+5",) as "hourly_bucket_Etc/GMT+5",

Timescale would say that the bucket at time zone is not part of the hypertable. What I tried then is to add from source the colums with all the timezones needed and add those column to the dimension of that hypertable using the SELECT add_dimension('<nameofthehypertable>', 'bucket_Etc/GMT-1', chunk_time_interval => INTERVAL '1 hour');

It adds the column to the hypertable as a dimension and then I use the create materialized view but timescale refuse to create the materialized view.

When only using UTC and one column to aggregate it works fine. However the requirement is to have all this timezone handling

Is there a way to create this materialized views? why add_dimension is not letting me create the materialized view, does this command add the column in order to be aggregated by timescale or I totally missunderstood it?

Please help!!!

jonatasdp · November 17, 2023, 7:01pm

It seems you’re running a very different query from the average timescaledb users

I don’t get why you have all this time_bucket in different time zones.

It’s implemented to be used with a single time zone.

github.com/timescale/timescaledb

Database continuous aggregation with time zone

opened 04:45PM - 28 May 19 UTC

closed 08:32PM - 06 Sep 22 UTC

Speedkore

continuous_aggregate feature-request

``` CREATE TABLE hypertable ( "whatever" float8 NOT NULL, "timestamp" tim…estamptz NOT NULL ); SELECT create_hypertable('hypertable', 'timestamp'); INSERT INTO hypertable VALUES (9.6,'2019-01-01 13:00'), (8.8,'2019-01-01 13:15'); create view aggregatedView with (timescaledb.continuous, timescaledb.refresh_interval = '5m', timescaledb.refresh_lag='5m') as SELECT sum("whatever")::BIGINT as "whatever", time_bucket('1 day', "timestamp" AT TIME ZONE 'Europe/Madrid') as "day" FROM hypertable GROUP BY time_bucket('1 day', "timestamp" AT TIME ZONE 'Europe/Madrid'); ``` SQL Error [XX000]: ERROR: time_bucket function for continuous aggregate query should be called on the dimension column of the hypertable I'm getting this error maybe because what i want to do is not possible, the select alone works fine. So is it possible to aggregate by day in a specific time zone with a continuous aggregation?

RCata · November 17, 2023, 7:34pm

Well, according to the issue in the repo. It would be a great deal to have timezone handling in continuous aggregates but I tried to work around creating all timezone columns and add them as dimensions but timescale refuses to create independent view from each column. Do you know why could this happen or I didn’t fully understand the add_dimension command.
By the way thanks a lot to reference the issue in git and for responding

jonatasdp · November 17, 2023, 9:52pm

Dimensions makes the chunk partition rules based on this settings. Have you checked the add_dimension docs?

Let’s say you want to have chunks separated by customers, you can add a dimension by customer ids and also set a max number of partitions by this fold.

RCata · November 20, 2023, 12:42pm

I did read the doc for add_dimension but I totally misunderstood. Thanks a lot for your guidance and support jonatasdp. I might have to re-think how to work with the timescale continuous aggregation and/or find other way to handle timezone but as this far. You help me a lot.

jonatasdp · November 20, 2023, 2:21pm

Welcome! happy to help!