Does the `NOW()` Function in a Materialized View Always Represent the Current Time?
I'm working with materialized views in SQL and I'm using the NOW() function to filter records based on a time interval. However, I'm not sure if NOW() represents the time when the view was created or if it always reflects the current time when the view is queried. Here's an example of how I'm using it:
CREATE MATERIALIZED VIEW port_history AS (
SELECT
timestamp,
source,
regexp_replace(regexp_replace(prefix, '^interfaces/interface[name=', ''), ']', '') AS iface,
CAST(
COALESCE(UPDATES->0->'values'->>'state/oper-status', 'N/A') ILIKE 'UP'
AS BOOLEAN
) as up
FROM
gnmi_all
WHERE
(
"subscription-name" = 'netq-interface-state'
OR
"subscription-name" = 'junos-interface-state'
)
AND
prefix LIKE 'interfaces/interface[name=%]'
AND
timestamp > NOW() - INTERVAL '15 days'
)I'm observing that some events that I see in NATS don't appear in the view. Could this be related to how NOW() is evaluated in the view?
Josh Toft
Asked on Dec 22, 2023
Yes, the NOW() function in a materialized view always represents the current time when the view is queried, not the time when the view was created. This means that if you use NOW() - INTERVAL '2 hours', it will always refer to the last two-hour window relative to the current time of the query execution. However, keep in mind that materialized views are not updated in real-time; they need to be refreshed to reflect the latest data.