Home  /  Reference  /  SQL functions & operators

now and mz_now functions

In Materialize, now() returns the value of the system clock when the transaction began as a timestamp with time zone value.

By contrast, mz_now() returns the logical time at which the query was executed as a mz_timestamp value.

Details

mz_now() clause

mz_now() <comparison_operator> <numeric_expr | timestamp_expr>
  • 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.

  • mz_now() can only be compared to either a numeric expression or a timestamp expression not containing mz_now().

Usage patterns

The typical uses of now() and mz_now() are:

  • Temporal filters

    You can use mz_now() in a WHERE or HAVING clause to limit the working dataset. This is referred to as a temporal filter. See the temporal filter pattern for more details.

  • Query timestamp introspection

    An ad hoc SELECT query with now() and mz_now() can be useful if you need to understand how up to date the data returned by a query is. The data returned by the query reflects the results as of the logical time returned by a call to mz_now() in that query.

Logical timestamp selection

When using the serializable isolation level, the logical timestamp may be arbitrarily ahead of or behind the system clock. For example, at a wall clock time of 9pm, Materialize may choose to execute a serializable query as of logical time 8:30pm, perhaps because data for 8:30–9pm has not yet arrived. In this scenario, now() would return 9pm, while mz_now() would return 8:30pm.

When using the strict serializable isolation level, Materialize attempts to keep the logical timestamp reasonably close to wall clock time. In most cases, the logical timestamp of a query will be within a few seconds of the wall clock time. For example, when executing a strict serializable query at a wall clock time of 9pm, Materialize will choose a logical timestamp within a few seconds of 9pm, even if data for 8:30–9pm has not yet arrived and the query will need to block until the data for 9pm arrives. In this scenario, both now() and mz_now() would return 9pm.

Limitations

Materialization

  • Queries that use now() cannot be materialized. In other words, you cannot create an index or a materialized view on a query that calls now().

  • Queries that use mz_now() can only be materialized if the call to mz_now() is used in a temporal filter.

These limitations are in place because now() changes every microsecond and mz_now() changes every millisecond. Allowing these functions to be materialized would be resource prohibitive.

mz_now() restrictions

The mz_now() clause has the following restrictions:

  • 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.

    For alternatives, see Disjunction (OR) alternatives.

  • If part of a WHERE clause, the WHERE clause cannot be an aggregate FILTER expression.

Examples

Temporal filters

It is common for real-time applications to be concerned with only a recent period of time. In this case, we will filter a table to only include records from the last 30 seconds.

-- Create a table of timestamped events.
CREATE TABLE events (
    content TEXT,
    event_ts TIMESTAMP
);

-- Create a view of events from the last 30 seconds.
CREATE VIEW last_30_sec AS
SELECT event_ts, content
FROM events
WHERE mz_now() <= event_ts + INTERVAL '30s';

Next, subscribe to the results of the view.

COPY (SUBSCRIBE (SELECT event_ts, content FROM last_30_sec)) TO STDOUT;

In a separate session, insert a record.

INSERT INTO events VALUES (
    'hello',
    now()
);

Back in the first session, watch the record expire after 30 seconds. Press Ctrl+C to quit the SUBSCRIBE when you are ready.

1686868190714   1       2023-06-15 22:29:50.711 hello
1686868220712   -1      2023-06-15 22:29:50.711 hello

You can materialize the last_30_sec view by creating an index on it (results stored in memory) or by recreating it as a MATERIALIZED VIEW (results persisted to storage). When you do so, Materialize will keep the results up to date with records expiring automatically according to the temporal filter.

Query timestamp introspection

If you haven’t already done so in the previous example, create a table called events and add a few records.

-- Create a table of timestamped events.
CREATE TABLE events (
    content TEXT,
    event_ts TIMESTAMP
);
-- Insert records
INSERT INTO events VALUES (
    'hello',
    now()
);
INSERT INTO events VALUES (
    'welcome',
    now()
);
INSERT INTO events VALUES (
    'goodbye',
    now()
);

Execute this ad hoc query that adds the current system timestamp and current logical timestamp to the events in the events table.

SELECT now(), mz_now(), * FROM events

            now            |    mz_now     | content |       event_ts
---------------------------+---------------+---------+-------------------------
 2023-06-15 22:38:14.18+00 | 1686868693480 | hello   | 2023-06-15 22:29:50.711
 2023-06-15 22:38:14.18+00 | 1686868693480 | goodbye | 2023-06-15 22:29:51.233
 2023-06-15 22:38:14.18+00 | 1686868693480 | welcome | 2023-06-15 22:29:50.874
(3 rows)

Notice when you try to materialize this query, you get errors:

CREATE MATERIALIZED VIEW cant_materialize
    AS SELECT now(), mz_now(), * FROM events;

ERROR:  cannot materialize call to current_timestamp
ERROR:  cannot materialize call to mz_now
Back to top ↑