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 !

4

Welcome @Emilie !

I know it’s sad. I’m often thinking about how to overcome it too.

One idea you can also try is to check the time zones that you want and see if you have hourly or half-hour resolutions between your views.

Let’s say you create the 30-minute continuous aggregate materialized view that will be set on UTC, and then you build the views with the extra time zones that are hierarchically using the previous one but for a specific time zone.

Good luck and happy coding :nerd_face:

5

Hi @jonatasdp ,

Unfortunately, I have to manage various time zones, including some with a 1 hour and 30-minute offset from UTC, for example.
This means I risk missing some data in my aggregations.
For now, I am forced to manually handle all my aggregations for each new deployment, which prevents automation.

I hope a solution will be found in the future to utilize the global timezone variable—that would be really useful. :slightly_smiling_face:

Have a nice day !

6

Hi @Emilie, It is possible but you should use function with input parameter for timezone. You also can take timezone from website. Something like this:

CREATE OR REPLACE FUNCTION public.select_rtv_aggregates_interpolate(
	p_devid bigint,
	p_varid bigint,
	p_time_begin bigint,
	p_time_end bigint,
	p_time_interval integer,
	p_timezone text DEFAULT 'Europe/Sofia'::text)
-- You can make materialize view on your table power and use the view to read. You can show your bucket like that
SELECT  time_bucket_gapfill(p_time_interval * '1 second'::interval, 
                                 rtv.time_ts AT TIME ZONE p_timezone, -- take timezone of user which open website .
                                 vbegin_date,
                                 vend_date
                                ) AS bucket_time,