# mz_now() expressions `mz_now()` expressions can only take comparison operators. `mz_now()` expressions cannot be used with disjunctions `OR` in view definitions. ## Overview In Materialize, [`mz_now()`](/sql/functions/now_and_mz_now/) function returns Materialize's current virtual timestamp (i.e., returns [`mz_timestamp`](/sql/types/mz_timestamp/)). The function can be used in [temporal filters](/transform-data/patterns/temporal-filters/) to reduce the working dataset. `mz_now()` expression has the following form: ```mzsql mz_now() ``` ## 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 | Materialize SQL ✅ |

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

| | Anti-pattern ❌ |

Not supported

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: | mz_now() Compound Clause | Valid/Invalid | | --- | --- | |

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

✅ Valid

Ad-hoc queries do not have the same restrictions.

| |

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() ;

| ✅ Valid | |

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

| ✅ Valid | |

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

❌ Invalid

In materialized view definitions, mz_now() clause can only be combined using an AND.

| |

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

❌ Invalid

In materialized view definitions with mz_now() clauses, top-level conditions must be combined using an AND.

| |

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

❌ Invalid

To index a view whose definitions includes mz_now() clauses, top-level conditions must be combined using an AND in the view definition.

| **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 | Materialize SQL ✅ |

Rewrite as UNION ALL with possible duplicates

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() ;

Rewrite as UNION ALL that avoids duplicates across queries

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' ;

Rewrite as UNION to deduplicate any and all duplicated results

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() ;

| | Anti-pattern ❌ |

Not supported

-- 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();