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.

Practice

1. Which PostgreSQL function returns only the current date without the time component?

easy

A. CURRENT_DATE

B. CURRENT_TIMESTAMP

C. NOW()

D. DATE_PART()

Solution

1. Step 1: Understand the function outputs

   CURRENT_DATE returns only the date part (year-month-day) without time.

2. Step 2: Compare with other functions

   CURRENT_TIMESTAMP and NOW() return both date and time, while DATE_PART() extracts parts but is not a current date function.

3. Final Answer:

   CURRENT_DATE -> Option A

4. Quick Check:

   Only date, no time = CURRENT_DATE [OK]

Hint: Only date? Use CURRENT_DATE [OK]

Common Mistakes:

• Confusing CURRENT_TIMESTAMP with CURRENT_DATE
• Using NOW() when only date is needed
• Thinking DATE_PART() returns current date

2. Which of the following is the correct syntax to get the current timestamp in PostgreSQL?

easy

A. SELECT CURRENT_DATE();

B. SELECT CURRENT_TIMESTAMP;

C. SELECT CURRENT_TIME();

D. SELECT NOW;

Solution

1. Step 1: Check function syntax

   CURRENT_TIMESTAMP is a special function and does not require parentheses.

2. Step 2: Validate other options

   SELECT CURRENT_DATE(); uses parentheses incorrectly with CURRENT_DATE. SELECT CURRENT_TIME(); returns only the time part (time type), not full timestamp. SELECT NOW; misses required parentheses for NOW(). SELECT CURRENT_TIMESTAMP; is the correct and preferred standard syntax.

3. Final Answer:

   SELECT CURRENT_TIMESTAMP; -> Option B

4. Quick Check:

   Standard timestamp function = CURRENT_TIMESTAMP [OK]

Hint: CURRENT_TIMESTAMP has no parentheses [OK]

Common Mistakes:

• Adding parentheses to CURRENT_DATE
• Omitting parentheses for NOW()
• Confusing function syntax

3. What will be the output type of the following query?
SELECT CURRENT_DATE, CURRENT_TIMESTAMP, NOW();

medium

A. Error due to mixing functions

B. Three columns: date, date, date

C. Three columns: timestamp without time zone, timestamp without time zone, timestamp without time zone

D. Three columns: date, timestamp with time zone, timestamp with time zone

Solution

1. Step 1: Identify each function's return type

   CURRENT_DATE returns a date type (no time). CURRENT_TIMESTAMP and NOW() return timestamp with time zone.

2. Step 2: Confirm output columns

   The query returns three columns: one date and two timestamps with time zone.

3. Final Answer:

   Three columns: date, timestamp with time zone, timestamp with time zone -> Option D

4. Quick Check:

   Types: date, timestamptz, timestamptz = Three columns: date, timestamp with time zone, timestamp with time zone [OK]

Hint: CURRENT_DATE = date; others = timestamp with time zone [OK]

Common Mistakes:

• Assuming all return date only
• Thinking NOW() returns date
• Expecting syntax error

4. You wrote this query but it gives an error:
SELECT CURRENT_DATE();
What is the problem?

medium

A. You must cast CURRENT_DATE to text

B. CURRENT_DATE requires a parameter inside parentheses

C. CURRENT_DATE is not a function and should not have parentheses

D. CURRENT_DATE is deprecated and replaced by NOW()

Solution

1. Step 1: Check function usage

   CURRENT_DATE is a special keyword, not a function, so it does not accept parentheses.

2. Step 2: Understand error cause

   Using parentheses causes syntax error because PostgreSQL expects no parentheses for CURRENT_DATE.

3. Final Answer:

   CURRENT_DATE is not a function and should not have parentheses -> Option C

4. Quick Check:

   No parentheses with CURRENT_DATE [OK]

Hint: No parentheses with CURRENT_DATE [OK]

Common Mistakes:

• Adding () to CURRENT_DATE
• Thinking CURRENT_DATE needs parameters
• Confusing with NOW() syntax

5. You want to insert the current date and time into a table with columns created_date DATE and created_time TIMESTAMP. Which INSERT statement is correct?

hard

A. INSERT INTO table_name (created_date, created_time) VALUES (CURRENT_DATE, NOW());

B. INSERT INTO table_name (created_date, created_time) VALUES (NOW(), CURRENT_DATE);

C. INSERT INTO table_name (created_date, created_time) VALUES (CURRENT_TIMESTAMP, CURRENT_DATE);

D. INSERT INTO table_name (created_date, created_time) VALUES (CURRENT_DATE(), CURRENT_TIMESTAMP());

Solution

1. Step 1: Match data types with values

   created_date is DATE type, so CURRENT_DATE fits. created_time is TIMESTAMP, so NOW() fits.

2. Step 2: Check syntax correctness

   INSERT INTO table_name (created_date, created_time) VALUES (CURRENT_DATE, NOW()); uses correct functions and order. INSERT INTO table_name (created_date, created_time) VALUES (NOW(), CURRENT_DATE); swaps types incorrectly. INSERT INTO table_name (created_date, created_time) VALUES (CURRENT_TIMESTAMP, CURRENT_DATE); uses timestamp for date column (wrong type). INSERT INTO table_name (created_date, created_time) VALUES (CURRENT_DATE(), CURRENT_TIMESTAMP()); uses parentheses incorrectly with CURRENT_DATE and CURRENT_TIMESTAMP.

3. Final Answer:

   INSERT INTO table_name (created_date, created_time) VALUES (CURRENT_DATE, NOW()); -> Option A

4. Quick Check:

   Date = CURRENT_DATE, Timestamp = NOW() [OK]

Hint: Match DATE with CURRENT_DATE, TIMESTAMP with NOW() [OK]

Common Mistakes:

• Swapping date and timestamp values
• Using parentheses with CURRENT_DATE
• Using CURRENT_TIMESTAMP for DATE column