CURRENT_DATE, CURRENT_TIMESTAMP, NOW() in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of CURRENT_DATE?

Consider the following SQL query run on April 15, 2024:

SELECT CURRENT_DATE;

What will be the output?

PostgreSQL

SELECT CURRENT_DATE;

A 2024-04-15

B 2024-04-15 00:00:00

C 2024-04-15 12:00:00

D Error: function CURRENT_DATE() does not exist

💡 Hint

CURRENT_DATE returns only the date part without time.

CURRENT_DATE returns the current date in the format YYYY-MM-DD without any time component.

❓ query_result

intermediate

What does CURRENT_TIMESTAMP return?

Given the query below executed on April 15, 2024 at 14:30:45.123456:

SELECT CURRENT_TIMESTAMP;

What is the expected output format?

PostgreSQL

SELECT CURRENT_TIMESTAMP;

A Error: CURRENT_TIMESTAMP is not a valid function

B 2024-04-15

C 14:30:45

D 2024-04-15 14:30:45.123456+00

💡 Hint

CURRENT_TIMESTAMP returns date and time with timezone.

CURRENT_TIMESTAMP returns the current date and time including fractional seconds and timezone offset.

❓ query_result

advanced

How does NOW() differ from CURRENT_TIMESTAMP?

Run these two queries at the same moment:

SELECT NOW();
SELECT CURRENT_TIMESTAMP;

What is the difference in their outputs?

PostgreSQL

SELECT NOW();
SELECT CURRENT_TIMESTAMP;

A Both return the exact same timestamp with timezone.

B NOW() returns timestamp without timezone; CURRENT_TIMESTAMP returns with timezone.

C NOW() returns only the time; CURRENT_TIMESTAMP returns date and time.

D NOW() returns the time when the transaction started; CURRENT_TIMESTAMP returns the current time.

💡 Hint

In PostgreSQL, NOW() is an alias for CURRENT_TIMESTAMP.

In PostgreSQL, NOW() and CURRENT_TIMESTAMP are equivalent and return the same timestamp with timezone.

📝 Syntax

advanced

Which query correctly extracts only the date part from NOW()?

You want to get only the date (YYYY-MM-DD) from the current timestamp using NOW(). Which query is correct?

A SELECT DATE(NOW());

B SELECT NOW()::date;

C All of the above

D SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');

💡 Hint

PostgreSQL allows multiple ways to extract date from timestamp.

All options correctly extract the date part from NOW(): casting, DATE() function, and formatting.

🧠 Conceptual

expert

Why might CURRENT_TIMESTAMP and NOW() return different values inside a transaction?

In PostgreSQL, consider a transaction that runs multiple queries using NOW() and CURRENT_TIMESTAMP. Which statement is true?

A They always return the exact current time at each call, even inside a transaction.

B They return the time when the transaction started, so all calls inside the transaction return the same timestamp.

C NOW() returns the current time at each call; CURRENT_TIMESTAMP returns the transaction start time.

D They return NULL inside transactions.

💡 Hint

PostgreSQL treats NOW() and CURRENT_TIMESTAMP as transaction start time.

In PostgreSQL, NOW() and CURRENT_TIMESTAMP return the time at the start of the current transaction, so multiple calls inside the same transaction return the same timestamp.

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