PostgreSQL NOW Function

The NOW() function in PostgreSQL returns the current date and time with time zone information. It returns the start time of the current transaction, making it consistent throughout a single transaction.

Syntax

NOW()
-- Returns: timestamp with time zone

Official Documentation

Example usage

-- Get current timestamp
SELECT NOW() AS current_timestamp;
-- Result: 2025-12-26 14:30:45.123456+00

-- Insert with current timestamp
INSERT INTO posts (title, content, created_at)
VALUES ('New Post', 'Content here', NOW());

-- Find recent records
SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';

-- Add default timestamp in table definition
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Compare with specific time
SELECT *
FROM sessions
WHERE login_time > NOW() - INTERVAL '1 hour'
  AND logout_time IS NULL;

-- Get different time components
SELECT
    NOW() AS full_timestamp,
    NOW()::DATE AS date_only,
    NOW()::TIME AS time_only,
    EXTRACT(YEAR FROM NOW()) AS current_year;

-- Transaction-consistent timestamp
BEGIN;
SELECT NOW(); -- Returns same value throughout transaction
-- ... other operations ...
SELECT NOW(); -- Same timestamp as above
COMMIT;

Common issues

1. Transaction scope: NOW() returns the transaction start time, not the statement execution time. Use CLOCK_TIMESTAMP() for statement-level time.
2. Time zone awareness: NOW() includes time zone. Use CURRENT_TIMESTAMP or LOCALTIMESTAMP for specific needs.
3. Comparison with dates: When comparing with DATE columns, cast appropriately to avoid time zone issues.

Best practices

1. Use NOW() for audit columns (created_at, updated_at) in applications.
2. For statement-level timestamp, use CLOCK_TIMESTAMP() instead.
3. Store timestamps in UTC and convert to local time zones in application layer.
4. Use INTERVAL arithmetic for date/time calculations: NOW() + INTERVAL '1 day'.

Frequently Asked Questions

Q: What's the difference between NOW() and CURRENT_TIMESTAMP?
A: They're functionally equivalent. Both return the transaction start time with time zone. CURRENT_TIMESTAMP is SQL standard, while NOW() is more concise and PostgreSQL-specific.

Q: Does NOW() return the same value throughout a transaction?
A: Yes, NOW() returns the transaction start time, so it's consistent within a single transaction. Use CLOCK_TIMESTAMP() if you need the current execution time for each statement.

Q: How do I get just the date or time from NOW()?
A: Cast it: NOW()::DATE for date only, NOW()::TIME for time only. Or use CURRENT_DATE and CURRENT_TIME respectively.

Q: How do I add or subtract time from NOW()?
A: Use INTERVAL: NOW() + INTERVAL '1 day', NOW() - INTERVAL '2 hours', NOW() + INTERVAL '3 months'.

Q: What time zone does NOW() use?
A: NOW() uses the session's time zone setting (SET timezone = 'America/New_York'). Check with SHOW timezone. The result includes time zone offset.