Using non-immutable functions in continuous aggregate view warning when creating CAGG

TimescaleDB and PostgreSQL
1

Hi! I’ve got this kind of table:

CREATE TABLE raw_m1 (
    datetime timestamp NOT NULL,
    symbol varchar NOT NULL,
    open double precision NOT NULL,
    high double precision NOT NULL,
    low double precision NOT NULL,
    close double precision NOT NULL,
    volume double precision NOT NULL
) WITH (
    tsdb.hypertable,
    tsdb.partition_column='datetime',
    tsdb.segmentby='symbol', 
    tsdb.orderby='datetime DESC'
);

And when I’m creating a view on it like this:

CREATE MATERIALIZED VIEW m15(datetime, symbol, cs)
WITH (tsdb.continuous,
      tsdb.materialized_only = FALSE) AS
    SELECT time_bucket('15 min', datetime) AS bucket,
        symbol,
        ROLLUP(candlestick((datetime)::timestamp without time zone, open, high, low, close, volume))
    FROM raw_m1
    GROUP BY bucket, symbol
    ORDER BY bucket DESC;

I’ve got warning

using non-immutable functions in continuous aggregate view may lead to inconsistent results on rematerialization

I’m aware of the fact that this warning is due to timestamp type of datetime field instead of timestamptz, but I don’t really understand the reason for this. The underlying types in both timestamps are the same and all calculations with them should be equivalent.
I’d like to know, does this warning have serious meaning or may I use timestamp without caring for this?

2

The reason for the warning is that the cast from timestamp to timestamptz is not marked immutable since it depends on the timezone setting.

set timezone to 'Europe/Berlin';
SELECT '2025-01-01 0:00:00+00'::timestamptz::timestamp;
      timestamp      
---------------------
 2025-01-01 01:00:00
(1 row)

set timezone to 'America/New_York';
SELECT '2025-01-01 0:00:00+00'::timestamptz::timestamp;
      timestamp      
---------------------
 2024-12-31 19:00:00
(1 row)

To not invalidate already materialized continuous aggregrate data you must never change your timezone. Keep in mind that using timestamp without time zone is not recommended and not following postgres best practices.

3

Thank you for explanation.
I’m going to store market data for securities from different exchanges. Usually exchanges provide market data with their local time and that’s why I’d like to store time as it is without conversions. Moreover, I will get same time from database as it was put there no matter what my own local timezone is, and that’s what I need.
Considering that exchanges unlikely ever change their timezones, can I be sure that materialized data will always be correct if using plain timestamp?