# now and mz_now functions
Details the differences between the `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>
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() ;
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() ;
| ❌ 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](http://localhost:1313/docs/transform-data/idiomatic-materialize-sql/mz_now/#disjunctions-or).
- If part of a `WHERE` clause, the `WHERE` clause cannot be an [aggregate
`FILTER` expression](/sql/functions/filters).
## 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.
```mzsql
-- 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.
```mzsql
COPY (SUBSCRIBE (SELECT event_ts, content FROM last_30_sec)) TO STDOUT;
```
In a separate session, insert a record.
```mzsql
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.
```nofmt
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.
```mzsql
-- 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.
```mzsql
SELECT now(), mz_now(), * FROM events
```
```nofmt
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:
```mzsql
CREATE MATERIALIZED VIEW cant_materialize
AS SELECT now(), mz_now(), * FROM events;
```
```nofmt
ERROR: cannot materialize call to current_timestamp
ERROR: cannot materialize call to mz_now
```
[`mz_timestamp`]: /sql/types/mz_timestamp
[`timestamp with time zone`]: /sql/types/timestamptz