CURRENT_DATE, CURRENT_TIMESTAMP, NOW() in PostgreSQL - Step-by-Step Execution

Concept Flow - CURRENT_DATE, CURRENT_TIMESTAMP, NOW()

Start Query

↓

Evaluate CURRENT_DATE

↓

Evaluate CURRENT_TIMESTAMP

↓

Evaluate NOW()

↓

Return Results

↓

End Query

The query evaluates CURRENT_DATE, CURRENT_TIMESTAMP, and NOW() functions step-by-step, returning the current date or timestamp values.

Execution Sample

PostgreSQL

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, NOW();

This query returns the current date, the current timestamp with timezone, and the current timestamp with timezone again.

Execution Table

Step	Function Evaluated	Result Type	Result Example	Notes
1	CURRENT_DATE	date	2024-06-15	Returns only the date part, no time included
2	CURRENT_TIMESTAMP	timestamp with time zone	2024-06-15 14:23:45.123456+00	Returns date and time with timezone
3	NOW()	timestamp with time zone	2024-06-15 14:23:45.123456+00	Equivalent to CURRENT_TIMESTAMP
4	Return Results	row	(2024-06-15, 2024-06-15 14:23:45.123456+00, 2024-06-15 14:23:45.123456+00)	Query returns all three values as one row

💡 All functions evaluated and results returned as a single row.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
CURRENT_DATE	NULL	2024-06-15	2024-06-15	2024-06-15	2024-06-15
CURRENT_TIMESTAMP	NULL	NULL	2024-06-15 14:23:45.123456+00	2024-06-15 14:23:45.123456+00	2024-06-15 14:23:45.123456+00
NOW()	NULL	NULL	NULL	2024-06-15 14:23:45.123456+00	2024-06-15 14:23:45.123456+00

Key Moments - 3 Insights

Why does CURRENT_DATE return only the date without time?

CURRENT_DATE is designed to return only the date part, as shown in execution_table step 1, so it excludes time information.

Are CURRENT_TIMESTAMP and NOW() different in their output?

No, both CURRENT_TIMESTAMP and NOW() return the current date and time with timezone, as shown in execution_table steps 2 and 3, they produce the same result.

Why do CURRENT_TIMESTAMP and NOW() include timezone information?

Because they return 'timestamp with time zone' type, which includes timezone data to represent the exact moment globally, as seen in execution_table steps 2 and 3.

Visual Quiz - 3 Questions

Look at the execution_table, what type of value does CURRENT_DATE return at step 1?

A Timestamp with timezone

B Timestamp without timezone

C Date only

D Time only

💡 Hint

Check the 'Result Type' column in execution_table row 1.

At which step do CURRENT_TIMESTAMP and NOW() produce the same result?

A Step 2 and Step 3

B Step 4

C Step 1

D They never produce the same result

💡 Hint

Look at the 'Result Example' and 'Notes' columns for steps 2 and 3 in execution_table.

If you only want the current date without time, which function should you use?

A CURRENT_TIMESTAMP

B CURRENT_DATE

C NOW()

D All return the same

💡 Hint

Refer to the 'Result Type' and 'Notes' for CURRENT_DATE in execution_table step 1.

Concept Snapshot

CURRENT_DATE returns the current date only (no time).
CURRENT_TIMESTAMP returns current date and time with timezone.
NOW() is equivalent to CURRENT_TIMESTAMP.
Use CURRENT_DATE for date only, NOW() or CURRENT_TIMESTAMP for full timestamp.
All return values reflect the moment the query runs.

Full Transcript

This visual execution trace shows how PostgreSQL evaluates CURRENT_DATE, CURRENT_TIMESTAMP, and NOW() functions in a query. First, CURRENT_DATE returns only the date part without time. Then, CURRENT_TIMESTAMP returns the current date and time including timezone information. NOW() returns the same timestamp as CURRENT_TIMESTAMP. The query returns all three values as one row. Beginners often wonder why CURRENT_DATE excludes time and why CURRENT_TIMESTAMP and NOW() are the same; this trace clarifies those points by showing each step and the resulting values. Use CURRENT_DATE when you want just the date, and use NOW() or CURRENT_TIMESTAMP when you need the full timestamp with timezone.

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