Materialized view and time_bucket with dynamic timezone?

TimescaleDB and PostgreSQL
1

Hello,

I need help to create a materialized view based on a local date with a configurable timezone.

CREATE MATERIALIZED VIEW power_avg_1minute
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 minute', created_at, (select distinct timezone from site)) AS bucket,
    sensor_id,
    ROUND(AVG(value)::numeric, 2) AS avg_power
FROM 	power
WHERE 	deleted_at is null
GROUP BY bucket, sensor_id;

SQL Error [0A000]: ERROR: invalid continuous aggregate query
Détail : CTEs, subqueries and set-returning functions are not supported by continuous aggregates.

I understand that subqueries are prohibited, and it works when I replace it with a string timezone.
But I need to deploy my project on many places with different timezones, so I would like to configure the timezone…
Is there a solution ?

Thank you for your help,

Emilie

2

It’s not possible right now @Emilie. You’d need to create one materialized view per time zone. Check this: Continous Aggregate Support for different timezone - #2 by Chris_Travers

3

Thank you @jonatasdp for your quick reply.
So sad.
Anyway thanks again !