Using ClickHouse Materialized Views: Creation, Testing, and Time‑Zone Issue Resolution

ClickHouse materialized views can be defined with the TO clause to write aggregated results into a target table. The basic syntax is:

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]view_name
ON CLUSTER TO [db.]target_table AS
SELECT ...

In the example, a per‑second detail table dba_test.t_1s is created, followed by a per‑minute aggregation table dba_test.t_1m using SummingMergeTree . The materialized view dba_test.t_1m_mv reads from the 1‑second table, aggregates with toStartOfMinute(ctime) , and writes into the 1‑minute table.

CREATE TABLE dba_test.t_1s (
    `ctime` DateTime64(0),
    `pv` Int64
) ENGINE = MergeTree
PARTITION BY toDate(ctime)
ORDER BY ctime;

CREATE TABLE dba_test.t_1m (
    `ctime` DateTime64(0),
    `pv` Int64
) ENGINE = SummingMergeTree
PARTITION BY toDate(ctime)
ORDER BY ctime;

CREATE MATERIALIZED VIEW dba_test.t_1m_mv TO dba_test.t_1m (
    `toStartOfMinute(ctime)` DateTime,
    `pv` Int64
) AS
SELECT
    toStartOfMinute(ctime),
    sum(pv) AS pv
FROM dba_test.t_1s
GROUP BY ctime;

Insertion tests showed that querying the materialized view returned the expected aggregated rows, but selecting from the target table initially displayed a timestamp of 1970-01-01 08:00:00 . The discrepancy was traced to a time‑zone offset applied when the source column contained empty values.

Further verification confirmed that the view’s query logic was correct; the problem lay in the mismatch between the view’s output column name ( toStartOfMinute(ctime) ) and the target table’s column name ( ctime ). Aligning these names resolves the issue.

SHOW CREATE TABLE t_1m_mv\G
statement: CREATE MATERIALIZED VIEW dba_test.t_1m_mv TO dba_test.t_1m (
    `ctime` DateTime,
    `pv` Int64
) AS
SELECT
    toStartOfTenMinutes(ctime) AS ctime,
    sum(pv) AS pv
FROM dba_test.t_1s
GROUP BY ctime;

After recreating the view with matching column names and cleaning the target table, queries on t_1m returned correct per‑minute aggregates without the erroneous 1970 timestamp.

Conclusion: The fields of a ClickHouse materialized view must exactly match the fields of the destination table; otherwise, unexpected default timestamps may appear due to mismatched column definitions.