now and mz_now functions
now() returns the value of the system clock with timezone when the transaction began. It cannot be used when creating materialized views.
mz_now() returns the logical time at which the query was executed. This may be arbitrarily ahead of or behind the system clock.
For example, at 9pm, Materialize may choose to execute a 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.
The typical uses of
You can use
HAVINGclause 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
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.
SELECTclause cannot be materialized. In other words, you cannot create an index or a materialized view on a query with
SELECTclause. This is because
mz_now()change every millisecond, so if this materialization were allowed, every record in the collection would be updated every millisecond, which would be resource prohibitive.
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
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