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 ?
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.
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.
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,
Oof. Welp, my colleague and I decided we would just make triggers to dynamically create separate continuous aggregates for each timezone as needed.
The only alternative we could think of was to use triggers to precompute the year, month, and day of each input row for its timestamp and timezone and use those in the aggregation group keys instead of time buckets. But continuous aggregates require a time_bucket on the hypertable timestamp so that wouldn’t work.
We’re trying to provide our customers the ability to view daily or monthly aggregated accounting information for a given site. It would also be nice to provide them a view of aggregated accounting information across all of their sites, where data for the same day of month at each site is grouped together, even though these day time ranges aren’t 100% coincident. For now, we would have to dynamically build a query to select from the continuous aggregates for the various timezones, and then group those results together. But it would be nice if we could just create one continuous aggregate that groups by customer and day.
Hey @jedwards1211, I’d like to encourage you to push this idea as a GitHub issue. If other folks want it and it gets upvoted, we have more chances to develop it.
