CURRENT_DATE, CURRENT_TIMESTAMP, NOW() in PostgreSQL - Deep Dive

Overview - CURRENT_DATE, CURRENT_TIMESTAMP, NOW()

What is it?

CURRENT_DATE, CURRENT_TIMESTAMP, and NOW() are special functions in PostgreSQL that return the current date and time from the database server. CURRENT_DATE gives just the date, while CURRENT_TIMESTAMP and NOW() provide the date and time with fractional seconds. These functions help you track when data is created or modified.

Why it matters

Knowing the current date and time is essential for many applications like logging events, scheduling tasks, or recording when transactions happen. Without these functions, you would have to manually input dates and times, which is error-prone and inconsistent. They ensure your data reflects real-world timing accurately.

Where it fits

Before learning these, you should understand basic SQL queries and data types like DATE and TIMESTAMP. After mastering these functions, you can explore time zones, interval arithmetic, and date/time formatting in PostgreSQL.

Mental Model

Core Idea

These functions provide the exact current date and time from the database server whenever you ask for it.

Think of it like...

It's like looking at a clock on the wall in a room; no matter who looks, the clock shows the same current time for everyone in that room.

┌───────────────┐
│ Database Time │
└──────┬────────┘
       │
       ▼
┌───────────────┐   ┌────────────────────────┐
│ CURRENT_DATE  │   │ CURRENT_TIMESTAMP / NOW() │
│ (date only)   │   │ (date + time)           │
└───────────────┘   └────────────────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding CURRENT_DATE Basics

Concept: CURRENT_DATE returns the current date without time.

When you use CURRENT_DATE in a query, PostgreSQL returns the date part of the current moment according to the server's clock. For example, SELECT CURRENT_DATE; might return '2024-06-15'. This value changes only once per day at midnight.

Result

A single date value like '2024-06-15' is returned.

Understanding that CURRENT_DATE gives only the date helps you avoid confusion when you need time information separately.

2

Foundation

Exploring CURRENT_TIMESTAMP and NOW()

Concept: CURRENT_TIMESTAMP and NOW() return the current date and time with fractional seconds.

Both CURRENT_TIMESTAMP and NOW() provide the full timestamp including hours, minutes, seconds, and fractions of a second. For example, SELECT CURRENT_TIMESTAMP; might return '2024-06-15 14:23:45.123456'. NOW() is a synonym for CURRENT_TIMESTAMP in PostgreSQL.

Result

A timestamp value like '2024-06-15 14:23:45.123456' is returned.

Knowing that CURRENT_TIMESTAMP and NOW() are the same helps you write clearer and consistent queries.

3

Intermediate

Using These Functions in Table Columns

🤔Before reading on: do you think CURRENT_TIMESTAMP changes for each row in a multi-row insert, or stays the same for the whole statement? Commit to your answer.

Concept: CURRENT_TIMESTAMP and NOW() return the same timestamp for the entire statement execution, not per row.

If you insert multiple rows in one statement using CURRENT_TIMESTAMP or NOW(), all rows get the same timestamp value. This is because PostgreSQL evaluates these functions once per statement, not per row. For example, INSERT INTO logs (event_time) VALUES (NOW()), (NOW()); will store the same timestamp twice.

Result

All inserted rows share the exact same timestamp value.

Understanding this prevents bugs where you expect slightly different times per row but get identical timestamps instead.

4

Intermediate

Time Zones and CURRENT_TIMESTAMP

🤔Before reading on: does CURRENT_TIMESTAMP always return UTC time, or the server's local time? Commit to your answer.

Concept: CURRENT_TIMESTAMP returns the current time adjusted to the session's time zone setting.

PostgreSQL stores timestamps with time zone information. CURRENT_TIMESTAMP returns the time according to the current session's time zone, which can be different from the server's system time zone. You can check or set the time zone with SET TIME ZONE 'Europe/London';

Result

Timestamp values reflect the session's time zone, not just server time.

Knowing this helps you handle applications used across different regions correctly.

5

Advanced

Difference Between NOW() and clock_timestamp()

🤔Before reading on: do you think NOW() and clock_timestamp() return the same time if called multiple times in one query? Commit to your answer.

Concept: NOW() returns a fixed time per statement, while clock_timestamp() returns the actual current time each time it is called.

In PostgreSQL, NOW() and CURRENT_TIMESTAMP return the same timestamp for the entire statement execution. clock_timestamp() returns the real current time at the moment of the call, which can differ if called multiple times in a query. For example, SELECT NOW(), clock_timestamp(); might show different times.

Result

NOW() is stable per statement; clock_timestamp() varies per call.

Understanding this distinction is crucial for precise timing measurements or logging within complex queries.

6

Expert

Internal Evaluation Timing of Timestamp Functions

🤔Before reading on: do you think CURRENT_TIMESTAMP is evaluated once per statement or once per transaction? Commit to your answer.

Concept: CURRENT_TIMESTAMP and NOW() are evaluated once per statement, not per transaction or per row.

PostgreSQL evaluates CURRENT_TIMESTAMP and NOW() at the start of each statement execution. This means if you run multiple statements in a transaction, each statement gets its own timestamp. This behavior ensures consistency within a statement but allows different statements to have different times.

Result

Timestamp values are consistent within a statement but can differ between statements in the same transaction.

Knowing this helps design applications that rely on precise timing and consistency guarantees.

Under the Hood

PostgreSQL's backend fetches the current system time from the operating system when a statement starts. It stores this timestamp internally and returns it for all calls to CURRENT_TIMESTAMP or NOW() during that statement. This avoids repeated system calls and ensures consistent timing within the statement execution.

Why designed this way?

Evaluating the timestamp once per statement improves performance by reducing system calls and ensures all rows affected by the statement share the same timestamp, which is important for data consistency and auditing.

┌─────────────────────────────┐
│ Start of SQL Statement       │
├───────────────┬─────────────┤
│ System Clock  │             │
│ (OS Time)    │             │
└──────┬────────┘             │
       │                      │
       ▼                      ▼
┌───────────────┐      ┌────────────────────────┐
│ Store Timestamp│      │ Use Stored Timestamp   │
│ for Statement  │─────▶│ for CURRENT_TIMESTAMP / NOW() │
│ Execution      │      └────────────────────────┘
└───────────────┘      

Myth Busters - 4 Common Misconceptions

Quick: Does CURRENT_DATE include time information? Commit to yes or no.

Common Belief: CURRENT_DATE returns the current date and time.

Reality: CURRENT_DATE returns only the date part without any time information.

Why it matters: Using CURRENT_DATE when you need time can cause bugs, like missing time details in logs or calculations.

Quick: Do NOW() and clock_timestamp() always return the same value in a query? Commit to yes or no.

Common Belief: NOW() and clock_timestamp() are interchangeable and always return the same current time.

Reality: NOW() returns a fixed timestamp per statement, while clock_timestamp() returns the actual current time at each call, which can differ within the same statement.

Why it matters: Confusing these can lead to incorrect timing data, especially in performance measurements or event logging.

Quick: Does CURRENT_TIMESTAMP always return UTC time? Commit to yes or no.

Common Belief: CURRENT_TIMESTAMP always returns time in UTC regardless of settings.

Reality: CURRENT_TIMESTAMP returns time adjusted to the current session's time zone setting, which may not be UTC.

Why it matters: Assuming UTC can cause wrong time displays or calculations in applications used across time zones.

Quick: Is the timestamp from NOW() different for each row in a multi-row insert? Commit to yes or no.

Common Belief: NOW() returns a new timestamp for each row inserted in a multi-row statement.

Reality: NOW() returns the same timestamp for all rows in a single statement execution.

Why it matters: Expecting different timestamps per row can cause confusion and errors in data auditing or event sequencing.

Expert Zone

1

CURRENT_TIMESTAMP and NOW() are synonyms but their behavior depends on statement execution timing, which affects multi-row operations.

2

The session time zone setting influences the output of CURRENT_TIMESTAMP, so changing it can alter query results without changing the system clock.

3

clock_timestamp() is useful for measuring elapsed time within a statement because it returns the actual current time on each call.

When NOT to use

Avoid using NOW() or CURRENT_TIMESTAMP when you need the exact current time for each row in a multi-row operation; instead, use clock_timestamp() or generate timestamps in the application layer. For timezone-independent storage, consider using TIMESTAMP WITHOUT TIME ZONE and handle time zones explicitly.

Production Patterns

In production, CURRENT_TIMESTAMP is often used for automatic timestamp columns like created_at or updated_at. Developers rely on its statement-level consistency for batch inserts. For precise timing or profiling, clock_timestamp() is used. Time zone settings are carefully managed to ensure consistent user experience across regions.

Connections

Time Zones

CURRENT_TIMESTAMP output depends on session time zone settings.

Understanding time zones helps you interpret timestamp values correctly and avoid bugs in global applications.

Transaction Isolation

CURRENT_TIMESTAMP is evaluated once per statement, which aligns with transaction boundaries for consistency.

Knowing this connection helps design reliable transactional systems that use timestamps for concurrency control.

Real-Time Clocks in Operating Systems

PostgreSQL relies on the OS clock to provide current time for these functions.

Understanding OS clock behavior explains why database time can drift or differ from application server time.

Common Pitfalls

#1 Using CURRENT_DATE when time information is needed.

Wrong approach:

SELECT CURRENT_DATE; -- expecting time included

Correct approach:

SELECT CURRENT_TIMESTAMP; -- includes date and time

Root cause:

Misunderstanding that CURRENT_DATE returns only the date without time.

#2 Expecting NOW() to return different timestamps for each row in multi-row inserts.

Wrong approach:

INSERT INTO logs (event_time) VALUES (NOW()), (NOW()); -- expecting different times

Correct approach:

Use clock_timestamp() if you need different times per row or generate timestamps in application code.

Root cause:

Not knowing that NOW() is evaluated once per statement, not per row.

#3 Assuming CURRENT_TIMESTAMP always returns UTC time.

Wrong approach:

SELECT CURRENT_TIMESTAMP; -- assuming UTC output

Correct approach:

SET TIME ZONE 'UTC'; SELECT CURRENT_TIMESTAMP; -- explicitly set time zone

Root cause:

Ignoring session time zone settings that affect timestamp output.

Key Takeaways

CURRENT_DATE returns only the current date without any time information.

CURRENT_TIMESTAMP and NOW() return the current date and time, evaluated once per statement.

The session's time zone setting affects the output of CURRENT_TIMESTAMP and NOW().

clock_timestamp() returns the actual current time at each call, unlike NOW() which is fixed per statement.

Understanding when and how these functions are evaluated prevents common bugs in timing and logging.