NOW() in SQL

Introduction

NOW() is a date-and-time function commonly used in MySQL and PostgreSQL to obtain the current timestamp according to the database's defined timing and time-zone context. It is useful for creation and update times, event comparisons, expiration, recent-activity windows, logging, and auditing.

SELECT NOW();

A result may look like 2026-11-09 10:30:45.123456. The text alone does not reveal whether the value is local, UTC, offset-aware, statement-stable, or transaction-stable. MySQL and PostgreSQL use the same function name but do not expose identical types and timing semantics.

Basic Syntax and Result

NOW() takes no ordinary arguments. MySQL optionally accepts a fractional-second precision such as NOW(3) or NOW(6). PostgreSQL uses NOW() as an alias for its standard current timestamp expression.

SELECT NOW() AS checked_at;

The returned value generally includes year, month, day, hour, minute, second, and possibly fractional seconds. Use a real temporal column or driver type rather than treating the result as an arbitrary formatted string.

NOW() Is Dynamic but Contextual

Separate executions at different times can return different values. Within one statement or transaction, the value may remain stable according to DBMS rules. This consistency is intentional because one logical operation should not receive several subtly different current times merely because its rows take time to process.

Dynamic does not mean unpredictable. Reliable systems define which clock, time zone, precision, and evaluation boundary apply. They also use explicit timestamps when historical work or tests must be reproducible.

NOW() Is Not Standard SQL

CURRENT_TIMESTAMP is the portable standard expression. NOW() is a widely recognized product function, especially in MySQL and PostgreSQL, but it is not supported as the normal current-time function in every major DBMS.

-- Portable intent
SELECT CURRENT_TIMESTAMP;

-- Common MySQL and PostgreSQL style
SELECT NOW();

Use CURRENT_TIMESTAMP in portable SQL unless the code intentionally targets a database whose NOW() behavior is understood. Portability also requires adapting intervals, types, precision, and time-zone functions.

Database Comparison

DatabaseCommon current date-time expressionNOW() support
MySQLNOW() or CURRENT_TIMESTAMPYes
PostgreSQLNOW() or CURRENT_TIMESTAMPYes
SQL ServerGETDATE() or CURRENT_TIMESTAMPNo standard NOW function
OracleSYSDATE or CURRENT_TIMESTAMPNo MySQL-style NOW function

Oracle SYSDATE and CURRENT_TIMESTAMP differ in type and time-zone context. SQL Server provides higher-precision, UTC, and offset-aware alternatives in addition to GETDATE(). Select by semantics rather than by name similarity.

MySQL NOW()

MySQL NOW() returns the current date and time in the session time zone. It is effectively synonymous with CURRENT_TIMESTAMP for normal use and is evaluated consistently from the start of query execution.

SELECT NOW(),
       CURRENT_TIMESTAMP,
       CURRENT_TIMESTAMP();

Connection-pool users should initialize the session time zone when a connection is checked out. Otherwise, a reused connection can retain a zone set by an earlier request or depend on an unknown server configuration.

PostgreSQL NOW()

PostgreSQL NOW() is equivalent to CURRENT_TIMESTAMP. It returns a timestamp with time zone value representing the start of the current transaction.

BEGIN;
SELECT NOW();
-- Work continues...
SELECT NOW();
COMMIT;

The two values remain based on the same transaction-start instant even if wall time advances. PostgreSQL provides statement_timestamp() for statement start and clock_timestamp() for a changing wall-clock value.

MySQL and PostgreSQL Are Not Identical

Both products accept NOW(), but MySQL normally ties it to query execution timing, while PostgreSQL ties it to transaction start. MySQL returns a temporal value governed by its chosen column and expression context; PostgreSQL returns an instant-aware timestamp with time zone.

Code that merely compiles on both systems can still behave differently in long transactions, pooled sessions, type mappings, and time-zone conversions. Cross-database tests should verify behavior rather than only syntax.

NOW() Versus CURRENT_TIMESTAMP

In MySQL and PostgreSQL, the expressions normally represent the same current timestamp concept. The main practical difference is portability and style.

SELECT NOW() AS product_style,
       CURRENT_TIMESTAMP AS standard_style;

Prefer the standard expression in shared SQL. Prefer NOW() when following a product-specific codebase that already depends on its documented semantics. Consistency inside a project can be more valuable than mixing synonyms without a reason.

NOW() Versus CURRENT_DATE and CURRENT_TIME

NOW() supplies date and time. CURRENT_DATE supplies a calendar date, while CURRENT_TIME supplies time-of-day information where supported.

If a rule is date-only, such as a birthday or due date, use a DATE expression. Converting NOW() to a date is less direct and can introduce an unnecessary implicit conversion. Use the narrowest temporal concept that matches the business meaning.

NOW() in SELECT

A query can expose when it was evaluated or use that timestamp in a calculated status.

SELECT order_id,
       created_at,
       NOW() AS checked_at
FROM customer_order;

For a report, a single generated-at value in metadata may be clearer than repeating it for every row. A historical report should also include a separate effective period or as-of timestamp.

NOW() in INSERT

A database-generated creation time avoids reliance on an application machine's clock.

INSERT INTO customer_order (
    order_id,
    customer_id,
    ordered_at
)
VALUES (
    5001,
    101,
    NOW()
);

This records database timing context, not necessarily the moment the customer clicked a button. For queued or offline events, preserve the source occurrence time separately from database ingestion time.

NOW() in UPDATE

An UPDATE can explicitly set the latest modification time.

UPDATE employee
SET salary = 75000,
    updated_at = NOW()
WHERE employee_id = 101;

Do not modify created_at. Decide whether a no-op update, technical maintenance, or retry should change updated_at. The answer depends on whether the column tracks database writes or meaningful business changes.

Default Creation Timestamps

A schema can often populate creation time automatically with the standard expression.

CREATE TABLE app_user (
    user_id INT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Using a default avoids repeating NOW() in every INSERT. Exact type, precision, NULL behavior, and DDL support differ by DBMS and version, so test the real schema.

MySQL Automatic Update Timestamps

MySQL supports an automatic update clause on suitable temporal columns.

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

This behavior is MySQL-specific. It may update for any qualifying row change, not only a meaningful business modification. Other databases commonly use triggers or explicit UPDATE statements.

Created, Updated, Event, and Processing Times

One row can require several timestamps. created_at identifies database creation, updated_at identifies modification, occurred_at identifies the source event, and processed_at identifies worker handling.

Do not overwrite one concept with another. Late messages and retries can arrive long after the event occurred. Separate columns make delays measurable and preserve the original timeline.

Past and Future Records

Timestamp comparison can separate events around the current instant.

SELECT event_id,
       event_time
FROM event
WHERE event_time < NOW();

SELECT reservation_id,
       reservation_time
FROM reservation
WHERE reservation_time > NOW()
ORDER BY reservation_time;

Exact equality with NOW() is rarely meaningful because stored values differ by fractions and evaluation occurs later. Use ranges, IDs, or business keys.

Active and Expired Ranges

An offer is commonly active from its start instant until, but not including, its expiration instant.

SELECT offer_id
FROM offer
WHERE starts_at <= NOW()
  AND expires_at > NOW();

This half-open interval avoids overlap between adjacent periods. An expired query uses expires_at <= NOW(). If NULL means no expiration, include an explicit expires_at IS NULL branch.

Three-Way Event Status

A CASE expression can derive upcoming, active, and completed states from a single stable current timestamp value.

SELECT event_id,
       CASE
           WHEN NOW() < starts_at THEN 'Upcoming'
           WHEN NOW() < ends_at THEN 'Active'
           ELSE 'Completed'
       END AS event_status
FROM event;

When product semantics guarantee a stable value within the query, repeated calls classify each row against the same reference instant. A CTE or parameter can make that reference more visible in complex SQL.

Capturing One Reference Time Explicitly

Complex reports are easier to review when the reference timestamp is named once. A common table expression can expose that value to every calculation and make it obvious that all rows use the same cutoff.

WITH reference_time AS (
    SELECT NOW() AS as_of_timestamp
)
SELECT e.event_id,
       e.starts_at,
       e.ends_at,
       r.as_of_timestamp
FROM event e
CROSS JOIN reference_time r
WHERE e.starts_at <= r.as_of_timestamp
  AND e.ends_at > r.as_of_timestamp;

For historical reruns and tests, replace the CTE with a parameter. The query's business logic remains unchanged while the time source becomes deterministic.

NOW() in Views and Stored Routines

A view can calculate live status with NOW(), but the result changes over time even when base rows do not. Consumers should understand that such a view is dynamic and may return a different status on the next execution.

Stored procedures can use NOW() for processing timestamps, cutoffs, and audit rows. Capture it once in a local variable when the routine's steps must share one reference time. Product transaction semantics can otherwise affect whether separate statements see identical or advancing values.

NOW() in Generated Columns and Constraints

Current time is volatile, so databases commonly restrict its use in generated columns, indexed expressions, and CHECK constraints that must remain stable. A generated is_expired value based on NOW() would need to change as the clock advances even though the row was not updated.

Store stable inputs such as expires_at and derive expiration in queries. A constraint can validate expires_at > created_at because that relationship is stable. Evaluate the moving relationship between expires_at and now at read or processing time.

Adding and Subtracting Intervals

MySQL and PostgreSQL both support interval arithmetic but use different literal syntax in common examples.

-- MySQL
SELECT NOW() + INTERVAL 1 HOUR;
SELECT NOW() - INTERVAL 1 DAY;

-- PostgreSQL
SELECT NOW() + INTERVAL '1 hour';
SELECT NOW() - INTERVAL '1 day';

Adding one calendar day and adding 24 elapsed hours can differ around daylight-saving transitions when local-zone semantics are involved. Define whether the requirement is calendar-based or duration-based.

Recent Activity Windows

A rolling recent window starts at a timestamp relative to now.

-- MySQL
SELECT activity_id,
       created_at
FROM activity_log
WHERE created_at >= NOW() - INTERVAL 15 MINUTE
  AND created_at <= NOW()
ORDER BY created_at DESC;

The upper bound excludes accidental future timestamps when included. In PostgreSQL, use an interval such as INTERVAL '15 minutes'. For a high-volume log, index the timestamp and verify the range plan.

Last 24 Hours Is Not Today

Last 24 hours is a rolling duration ending now. Today is a calendar interval from local midnight to the next local midnight in a chosen zone. At 7:45 PM, the first starts at 7:45 PM yesterday, while the second starts at midnight.

Use NOW() - INTERVAL 24 HOUR in MySQL-style syntax for the rolling window. Use current-date boundaries for today's records. The product requirement should say which meaning it intends.

Today's Timestamp Records

Do not search today's rows by comparing a timestamp directly with NOW(). Use start-of-day and next-day boundaries.

-- MySQL
SELECT order_id,
       created_at
FROM customer_order
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL 1 DAY;

The half-open upper boundary handles fractional seconds safely. Derive boundaries in the required business time zone rather than assuming the database session zone is correct.

Elapsed-Time Calculations

MySQL TIMESTAMPDIFF() can calculate whole units between a stored time and NOW().

SELECT order_id,
       TIMESTAMPDIFF(
           MINUTE,
           created_at,
           NOW()
       ) AS minutes_old
FROM customer_order;

The result follows the function's unit and truncation semantics. Service-level calculations may also need business hours, pauses, holidays, or fractional units.

Session and Token Expiration

A session can expire after inactivity, while a token can expire at a stored instant.

-- MySQL session inactivity
SELECT session_id
FROM user_session
WHERE last_activity_at < NOW() - INTERVAL 30 MINUTE;

SELECT token_id
FROM access_token
WHERE expires_at > NOW()
  AND revoked_at IS NULL;

Time comparison is only one control. Authentication systems also require secure token generation, revocation, atomic use, and a trusted clock.

Trusting the Time Source

A client can manipulate its local clock, so security decisions should rely on a trusted server or database source. Database NOW() provides a centralized reference for rows and comparisons executed there, but database infrastructure still needs reliable clock synchronization and monitoring.

Do not accept a client-provided current timestamp as proof that a token is valid or an action occurred before a deadline. Retain the client time separately if it is useful evidence, and record the trusted receipt time independently.

NOW() Precision in MySQL

MySQL can request fractional-second precision.

SELECT NOW(3) AS milliseconds,
       NOW(6) AS microseconds;

The receiving column must support the intended precision, and drivers must preserve it. More displayed digits do not guarantee equivalent physical clock accuracy or unique timestamps.

Precision Conversion and Round Trips

An application can hold nanoseconds while MySQL stores microseconds or a schema stores only whole seconds. Writing and reading the value can therefore truncate or round fractional digits. Exact equality with the original application object may fail even when the stored instant is acceptable.

Define one supported precision at every boundary and normalize before comparison. Integration tests should write representative fractional values, read them back through the production driver, and assert the documented precision rather than the application's maximum capability.

Precision and Ordering Ties

Fast writes can share the same stored timestamp, particularly when column precision is limited. A timestamp alone is not a unique event identifier.

SELECT event_id,
       created_at
FROM audit_event
ORDER BY created_at DESC,
         event_id DESC;

Add a deterministic tie-breaker. Distributed event order may also require sequence numbers, versions, or logical clocks because physical clocks can drift.

MySQL Session Time Zone

MySQL NOW() uses the session time zone. Relevant settings can be inspected directly.

SELECT @@session.time_zone,
       @@global.time_zone,
       NOW(),
       UTC_TIMESTAMP();

The setting may be SYSTEM, a named zone, or an offset depending on configuration. Connection pools should establish a known session context instead of inheriting it accidentally.

NOW() Is Not Automatically UTC

In MySQL, UTC_TIMESTAMP() explicitly requests a UTC value, while NOW() follows the session time zone. In PostgreSQL, NOW() represents an instant and is rendered according to the session zone.

A global system should define whether it stores UTC instants, offset-aware timestamps, or local date-times paired with named zones. Do not infer the contract solely from a column named timestamp.

Timestamp With and Without Time Zone

A local timestamp without zone information cannot uniquely identify a global instant. During daylight-saving fall-back, the same wall time can occur twice. During spring-forward, some local times do not occur.

Use a time-zone-aware type or convert with a named zone when the value must represent an instant. Preserve the named zone for recurring local schedules because a fixed numeric offset does not encode seasonal rules.

Extracting Date and Time Parts

MySQL can derive components from NOW().

SELECT DATE(NOW()) AS current_date_part,
       TIME(NOW()) AS current_time_part,
       YEAR(NOW()) AS current_year,
       MONTH(NOW()) AS current_month,
       DAY(NOW()) AS current_day,
       HOUR(NOW()) AS current_hour;

If only the current date is needed, CURRENT_DATE or CURDATE() communicates intent more directly. Component extraction is useful when the complete timestamp is also relevant or when grouping and labeling require parts.

Formatting NOW()

MySQL DATE_FORMAT() converts a timestamp into presentation text.

SELECT DATE_FORMAT(
           NOW(),
           '%Y-%m-%d %H:%i:%s'
       ) AS display_timestamp;

Keep the unformatted value for comparisons, sorting, arithmetic, APIs, and storage. Formatting belongs at the final report or interface boundary and should follow the user's locale.

NOW() in API Responses

An API should serialize an instant with an explicit offset or UTC marker. Returning a bare value such as 2026-11-09 10:30:45 leaves clients unsure which zone applies. ISO 8601-style representations are common, but the contract must define precision and offset handling.

Do not append Z to a session-local NOW() result unless it has actually been converted to UTC. That marker asserts UTC and changes the meaning. Preserve typed values through the database driver and serialize them deliberately.

NOW() and Data Migrations

Adding a current-time default to an existing table can assign migration time to old rows, falsely suggesting that every record was created during deployment. Backfill from trustworthy historical evidence where possible, or leave the value unknown and document the limitation.

When moving from local timestamps to UTC, determine the original zone for each row. A bare local timestamp around a daylight-saving transition can be ambiguous. Validate representative dates, row counts, and precision before switching application behavior.

Database Clock Versus Application Clock

Database-generated timestamps give database writes a consistent source. Application-generated timestamps can preserve user or source event time and are easier to control in unit tests. They can also differ when servers are unsynchronized.

Choose ownership per field. It is common for the source to provide occurred_at while the database generates ingested_at. Monitor infrastructure clock drift and do not use timestamp order alone as proof of causality.

Replication and Read-Replica Timing

A row timestamp records the chosen source time, not when every replica made the row visible. Replication lag can cause a read replica to expose a row seconds or minutes after its created_at value.

Do not estimate replication health by subtracting created_at from the reader's NOW() unless the field was designed as a replication marker. Use database replication metrics or explicit ingestion timestamps. The primary and replica also need synchronized clocks for cross-node comparisons.

Clock Corrections and Leap Seconds

Operating systems and database platforms can adjust wall clocks through synchronization. Some environments step the clock, while others smear adjustments over time. Leap-second handling also varies by platform.

For ordinary business auditing, a wall-clock timestamp remains appropriate. For strict elapsed measurement or sequence guarantees, pair it with monotonic duration data or ordered identifiers. Document platform behavior when subsecond or regulatory timing requirements are unusually strict.

NOW() in Long Transactions

PostgreSQL NOW() remains fixed at transaction start. MySQL normally establishes NOW() at query start. A long process that expects a newly advancing clock can therefore behave differently across products.

For a cutoff that must remain consistent, capture one reference timestamp intentionally. For a timeout that must observe live progression, use the product's clock-oriented function or enforce it in the application with a monotonic timer.

Wall Clock Versus Elapsed Duration

NOW() represents civil time. Clock synchronization can adjust wall time, and local zones undergo daylight-saving changes. It is not always the right tool for measuring short execution duration.

Use a monotonic timer for benchmarking and timeout measurement. Store wall-clock start and finish timestamps for audit, but keep elapsed duration as a separately measured value when accuracy matters.

Queue and Scheduled Job Eligibility

Workers commonly select pending jobs whose run time has arrived.

SELECT job_id
FROM background_job
WHERE status = 'Pending'
  AND next_run_at <= NOW()
ORDER BY next_run_at, job_id;

The query must be paired with locking or an atomic claim operation so multiple workers do not process the same job. Time defines eligibility; it does not solve concurrency.

Real-World E-Commerce Timeline

An order workflow can record ordered_at, paid_at, packed_at, shipped_at, and delivered_at. Each timestamp represents a distinct state transition and should be set when that transition succeeds.

Do not infer every event from updated_at; unrelated edits can change it. Dedicated event timestamps and an append-only status history make customer communication, fulfillment metrics, and investigations more reliable.

Real-World Login and Security Timeline

A login system may record an authentication attempt, successful login, token issuance, last activity, logout, revocation, and expiration. These events should not collapse into one vague timestamp column.

Use clear names and store the trusted processing time for each security-relevant transition. Pair timestamps with actor, client, request, result, and correlation information so the record can support both operations and incident analysis.

Real-World Job Execution Timeline

A background job can have scheduled_at, claimed_at, started_at, heartbeat_at, completed_at, and failed_at. The differences between them reveal queue delay, execution duration, and stalled work.

Record attempts separately when retries are possible. Replacing the first failure time with a later success erases operational history and makes reliability metrics misleading.

Indexing NOW() Predicates

Indexes on created_at, expires_at, and next_run_at can support range scans. Comparing the stored column directly with a current-time boundary is often more index-friendly than wrapping every row in a conversion function.

A compound index can support status plus time for queues or active records, but order depends on workload and selectivity. Verify with execution plans and realistic data volume.

Partitioning and Retention

Large event tables may be partitioned by timestamp. Clear start and end boundaries help the optimizer prune unrelated partitions. Partition keys and query boundaries must use compatible time-zone semantics.

Retention jobs can calculate one cutoff from NOW() and remove data older than policy permits. Record that cutoff for audit, honor legal holds, and prefer partition removal when an entire partition has the same retention status.

Testing NOW()-Dependent SQL

Tests that call the live clock can fail nondeterministically. Pass an as_of_timestamp parameter, use a clock abstraction, or isolate current-time logic behind a repository or view.

WHERE expires_at <= :as_of_timestamp

Test exact boundaries, fractional precision, transaction length, session time zones, daylight-saving transitions, NULL endpoints, and values just before and after expiration. Confirm database-driver round trips at the supported precision.

Historical Reruns and Auditability

A query using NOW() answers a live question. Rerunning it tomorrow can produce a different population and status. Historical reports, failed-job retries, and regulated decisions should store or parameterize the original effective timestamp.

Keep generation time separate from as-of time. The first explains when a report was produced; the second explains the temporal point the report describes.

Common Mistakes

Frequent mistakes include assuming NOW() works in every database, assuming it is UTC, using it when only a date is required, and searching with exact equality. Other errors include ignoring transaction stability, session time zones, precision loss, daylight-saving ambiguity, and duplicate timestamp values.

Developers also overwrite created_at, treat updated_at as a full audit trail, confuse today with the last 24 hours, and use a wall clock as a stopwatch. Each mistake reflects an undefined time concept.

Best-Practice Checklist

  1. Use NOW() only where the target DBMS supports its required semantics.
  2. Prefer CURRENT_TIMESTAMP when portability matters.
  3. Document statement-start or transaction-start timing behavior.
  4. Define whether values represent UTC, an offset instant, or local time.
  5. Use proper temporal types and adequate fractional precision.
  6. Keep event, ingestion, creation, update, and processing times separate.
  7. Use half-open ranges rather than fragile timestamp equality.
  8. Initialize time-zone settings for pooled connections.
  9. Add deterministic tie-breakers when ordering events.
  10. Use a monotonic timer for elapsed-performance measurements.
  11. Parameterize time in tests, retries, and historical reports.
  12. Verify indexes and partition pruning with execution plans.

Interview Questions and Answers

What does NOW() return? In MySQL and PostgreSQL, it returns the current date and time according to the product's timestamp semantics.

Is NOW() standard SQL? No. CURRENT_TIMESTAMP is the standard expression.

How does PostgreSQL NOW() behave? It represents the start time of the current transaction.

How does MySQL NOW() behave? It uses the session time zone and normally provides a value established at query start.

Does SQL Server support NOW()? Use GETDATE() or CURRENT_TIMESTAMP, with other functions available for UTC or higher precision.

Does Oracle support NOW()? Not as the MySQL/PostgreSQL function; common alternatives are SYSDATE and CURRENT_TIMESTAMP.

Is NOW() UTC? Not necessarily. Verify the database and session configuration.

Why avoid equality with NOW()? The exact timestamp and fractional precision are unlikely to match a stored row. Use an appropriate range.

Real-World Logging Example

INSERT INTO api_log (
    endpoint,
    status_code,
    request_id,
    recorded_at
)
VALUES (
    '/api/orders',
    200,
    'req-5001',
    NOW()
);

The database records its ingestion time. A complete observability record may also include source event time, duration, service, trace ID, tenant, and normalized time-zone semantics.

Key Takeaway

NOW() is a convenient current date-and-time function in MySQL and PostgreSQL, but its shared spelling hides important differences. MySQL commonly uses query-start and session-zone behavior, while PostgreSQL uses transaction-start semantics and an instant-aware type.

RELIABLE NOW() USAGE = KNOWN DBMS SEMANTICS
                     + EXPLICIT TEMPORAL TYPE
                     + DOCUMENTED TIME ZONE
                     + PRECISE RANGE BOUNDARIES
                     + ADEQUATE PRECISION
                     + DETERMINISTIC TESTS
                     + PORTABILITY DECISION

Use the standard expression when portability matters, use ranges instead of exact equality, separate event time from processing time, and define UTC or local-zone behavior explicitly. Those choices make current-time SQL reliable for auditing, expiration, scheduling, recent activity, and global applications.