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.