mz_now() expressions

Overview

In Materialize, mz_now() function returns Materialize’s current virtual timestamp (i.e., returns mz_timestamp). The function can be used in temporal filters to reduce the working dataset.

mz_now() expression has the following form:

mz_now() <comparison_operator> <numeric_expr | timestamp_expr>

Idiomatic Materialize SQL

mz_now() expressions to calculate past or future timestamp

Idiomatic Materialize SQL: mz_now() must be used with one of the following comparison operators: =, <, <=, >, >=, or an operator that desugars to them or to a conjunction (AND) of them (for example, BETWEEN...AND...). That is, you cannot use date/time operations directly on mz_now() to calculate a timestamp in the past or future. Instead, rewrite the query expression to move the operation to the other side of the comparison.

Examples

WHERE mz_now() > order_date + INTERVAL '5min';

WHERE mz_now() - INTERVAL '5min' > order_date;

Disjunctions (OR)

When used in a materialized view definition, a view definition that is being indexed (i.e., although you can create the view and perform ad-hoc query on the view, you cannot create an index on that view), or a SUBSCRIBE statement:

• mz_now() clauses can only be combined using an AND, and

• All top-level WHERE or HAVING conditions must be combined using an AND, even if the mz_now() clause is nested.

For example:

SELECT * FROM orders
WHERE status = 'Shipped'
OR order_date + interval '1' days <= mz_now()
;

CREATE MATERIALIZED VIEW forecast_completed_orders AS
SELECT * FROM orders
WHERE order_date + interval '3' days > mz_now()
AND order_date + interval '1' days < mz_now()
;

CREATE MATERIALIZED VIEW forecast_completed_orders AS
SELECT * FROM orders
WHERE (status = 'Complete' OR status = 'Shipped')
AND order_date + interval '1' days <= mz_now()
;

CREATE MATERIALIZED VIEW forecast_completed_orders AS
SELECT * FROM orders
WHERE status = 'Shipped'
OR order_date + interval '1' days <= mz_now()
;

CREATE MATERIALIZED VIEW forecast_completed_orders AS
SELECT * FROM orders
WHERE status = 'Complete'
OR (status = 'Shipped' AND order_date + interval '1' days <= mz_now())

CREATE VIEW forecast_completed_orders AS
SELECT * FROM orders
WHERE status = 'Complete'
OR (status = 'Shipped' AND order_date + interval '1' days <= mz_now())
;

CREATE INDEX idx_forecast_completed_orders ON forecast_completed_orders
(order_date); -- Unsupported because of the `mz_now()` clause

Idiomatic Materialize SQL: When mz_now() is included in a materialized view definition, a view definition that is being indexed, or a SUBSCRIBE statement, instead of using disjunctions (OR) when using mz_now(), rewrite the query to use UNION ALL or UNION instead, deduplicating as necessary:

• In some cases, you may need to modify the conditions to deduplicate results when using UNION ALL. For example, you might add the negation of one input’s condition to the other as a conjunction.

• In some cases, using UNION instead of UNION ALL may suffice if the inputs do not contain other duplicates that need to be retained.

Examples

CREATE MATERIALIZED VIEW forecast_completed_orders_duplicates_possible AS
SELECT item, quantity, status from orders
WHERE status = 'Shipped'
UNION ALL
SELECT item, quantity, status from orders
WHERE order_date + interval '30' minutes >= mz_now()
;

CREATE MATERIALIZED VIEW forecast_completed_orders_deduplicated_union_all AS
SELECT item, quantity, status from orders
WHERE status = 'Shipped'
UNION ALL
SELECT item, quantity, status from orders
WHERE order_date + interval '30' minutes >= mz_now()
AND status != 'Shipped' -- Deduplicate by excluding those with status 'Shipped'
;

CREATE MATERIALIZED VIEW forecast_completed_orders_deduplicated_results AS
SELECT item, quantity, status from orders
WHERE status = 'Shipped'
UNION
SELECT item, quantity, status from orders
WHERE order_date + interval '30' minutes >= mz_now()
;

-- Unsupported
CREATE MATERIALIZED VIEW forecast_completed_orders_unsupported AS
SELECT item, quantity, status from orders
WHERE status = 'Shipped'
OR order_date + interval '30' minutes >= mz_now();