Almost every analytics query opens with "for the last N days" or "for the current month". To make those filters both correct and fast, you need to know how NOW() differs from CURRENT_DATE, and how to add intervals to dates.
NOW() and CURRENT_DATE: what they return
PostgreSQL gives you two basic ways to ask for "now":
NOW() (alias CURRENT_TIMESTAMP) returns a timestamptz — a full date-and-time moment.CURRENT_DATE returns a date — just today, with no time component.
SELECT
NOW() AS ts,
CURRENT_DATE AS today,
CURRENT_TIME AS clock;
The distinction matters when comparing. created_at >= CURRENT_DATE keeps everything from midnight today onward, whereas created_at >= NOW() keeps only the future — almost never what you want.
The "last 7 days" window
The canonical pattern subtracts an interval from the current moment:
SELECT id, email, created_at
FROM users
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;
INTERVAL '7 days' is a duration literal. You can add it to or subtract it from any timestamp or date. It accepts hours, days, months, years, and combinations like INTERVAL '1 day 6 hours'.
The same trick scales to aggregates — revenue over the last day, broken out by status:
SELECT status, COUNT(*) AS cnt, SUM(amount) AS revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '24 hours'
GROUP BY status;
Handy detail: the right-hand side is constant per query, so the planner evaluates it once and can still use an index on created_at.
"This month" and truncating to a boundary
For calendar windows, date_trunc snaps a timestamp down to the start of a period:
SELECT id, amount
FROM orders
WHERE created_at >= date_trunc('month', CURRENT_DATE)
AND created_at < date_trunc('month', CURRENT_DATE) + INTERVAL '1 month';
This is sturdier than EXTRACT(MONTH FROM created_at) = 6: the upper bound adjusts to month length automatically, and the query stays sargable (index-friendly). The same shape works for 'day', 'week', and 'year'.
NOW() versus clock_timestamp()
The key subtlety: NOW() is pinned at transaction start and does not move until the transaction ends. Every call inside one transaction returns the same value.
BEGIN;
SELECT NOW();
SELECT NOW();
SELECT clock_timestamp();
COMMIT;
NOW() / CURRENT_TIMESTAMP / transaction_timestamp() — transaction start time.statement_timestamp() — start of the current statement.clock_timestamp() — true wall clock, changes on every call.
For business logic and filters, reach for NOW(): the value is stable and reproducible. clock_timestamp() is only for measuring elapsed time within a single transaction.
Gotcha: do not wrap the column in a function just to compare it. WHERE date_trunc('day', created_at) = CURRENT_DATE disables the index on created_at. Compare the bare column against boundaries instead: created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + INTERVAL '1 day'.
MySQL and ClickHouse differences
MySQL spells intervals differently — no quotes, singular units:
SELECT id, email
FROM users
WHERE created_at >= NOW() - INTERVAL 7 DAY;
SELECT CURDATE() AS today, NOW() AS ts;
- In MySQL,
CURDATE() is the analog of CURRENT_DATE, and the unit is written INTERVAL 7 DAY (DAY, HOUR, MONTH, no s).
- MySQL's
NOW() is also stable within a statement; the live clock comes from SYSDATE().
- ClickHouse uses
now() with helpers like subtractDays(now(), 7) or now() - INTERVAL 7 DAY; the month start is toStartOfMonth(now()).
Remember three things: NOW() is the transaction-time timestamp, CURRENT_DATE is date-only, and interval math over a bare column gives you correct and fast time windows.
Almost every analytics query opens with "for the last N days" or "for the current month". To make those filters both correct and fast, you need to know how
NOW()differs fromCURRENT_DATE, and how to add intervals to dates.NOW() and CURRENT_DATE: what they return
PostgreSQL gives you two basic ways to ask for "now":
NOW()(aliasCURRENT_TIMESTAMP) returns atimestamptz— a full date-and-time moment.CURRENT_DATEreturns adate— just today, with no time component.SELECT NOW() AS ts, -- 2026-06-17 14:30:00+00 CURRENT_DATE AS today, -- 2026-06-17 CURRENT_TIME AS clock; -- 14:30:00+00The distinction matters when comparing.
created_at >= CURRENT_DATEkeeps everything from midnight today onward, whereascreated_at >= NOW()keeps only the future — almost never what you want.The "last 7 days" window
The canonical pattern subtracts an interval from the current moment:
SELECT id, email, created_at FROM users WHERE created_at >= NOW() - INTERVAL '7 days' ORDER BY created_at DESC;INTERVAL '7 days'is a duration literal. You can add it to or subtract it from anytimestampordate. It acceptshours,days,months,years, and combinations likeINTERVAL '1 day 6 hours'.The same trick scales to aggregates — revenue over the last day, broken out by status:
SELECT status, COUNT(*) AS cnt, SUM(amount) AS revenue FROM orders WHERE created_at >= NOW() - INTERVAL '24 hours' GROUP BY status;Handy detail: the right-hand side is constant per query, so the planner evaluates it once and can still use an index on
created_at."This month" and truncating to a boundary
For calendar windows,
date_truncsnaps a timestamp down to the start of a period:SELECT id, amount FROM orders WHERE created_at >= date_trunc('month', CURRENT_DATE) AND created_at < date_trunc('month', CURRENT_DATE) + INTERVAL '1 month';This is sturdier than
EXTRACT(MONTH FROM created_at) = 6: the upper bound adjusts to month length automatically, and the query stays sargable (index-friendly). The same shape works for'day','week', and'year'.NOW() versus clock_timestamp()
The key subtlety:
NOW()is pinned at transaction start and does not move until the transaction ends. Every call inside one transaction returns the same value.BEGIN; SELECT NOW(); -- 14:30:00 -- ... long-running work ... SELECT NOW(); -- still 14:30:00 SELECT clock_timestamp(); -- real wall-clock time, already advanced COMMIT;NOW()/CURRENT_TIMESTAMP/transaction_timestamp()— transaction start time.statement_timestamp()— start of the current statement.clock_timestamp()— true wall clock, changes on every call.For business logic and filters, reach for
NOW(): the value is stable and reproducible.clock_timestamp()is only for measuring elapsed time within a single transaction.MySQL and ClickHouse differences
MySQL spells intervals differently — no quotes, singular units:
-- MySQL SELECT id, email FROM users WHERE created_at >= NOW() - INTERVAL 7 DAY; SELECT CURDATE() AS today, NOW() AS ts;CURDATE()is the analog ofCURRENT_DATE, and the unit is writtenINTERVAL 7 DAY(DAY,HOUR,MONTH, nos).NOW()is also stable within a statement; the live clock comes fromSYSDATE().now()with helpers likesubtractDays(now(), 7)ornow() - INTERVAL 7 DAY; the month start istoStartOfMonth(now()).Remember three things:
NOW()is the transaction-time timestamp,CURRENT_DATEis date-only, and interval math over a bare column gives you correct and fast time windows.