CURRENT_DATE, CURRENT_TIMESTAMP, NOW() in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the CURRENT_DATE function return in PostgreSQL?

CURRENT_DATE returns the current date from the system clock without the time part. It shows the year, month, and day only.

beginner

How is CURRENT_TIMESTAMP different from CURRENT_DATE?

CURRENT_TIMESTAMP returns the current date and time including hours, minutes, seconds, and fractional seconds, while CURRENT_DATE returns only the date.

beginner

What does the NOW() function return in PostgreSQL?

NOW() returns the current date and time, similar to CURRENT_TIMESTAMP. It shows the exact moment the query runs.

intermediate

Can CURRENT_TIMESTAMP and NOW() return different values in the same query?

No, both CURRENT_TIMESTAMP and NOW() return the same timestamp value within a single query execution because they use the transaction start time.

beginner

Why might you use CURRENT_DATE instead of CURRENT_TIMESTAMP or NOW()?

You use CURRENT_DATE when you only need the date without the time, for example, to record birthdays or due dates where time is not important.

Which function returns only the date part without time in PostgreSQL?

A GETDATE()

B CURRENT_TIMESTAMP

C CURRENT_DATE

D NOW()

CURRENT_DATE returns only the date part. CURRENT_TIMESTAMP and NOW() include time. GETDATE() is not standard in PostgreSQL.

What does NOW() return in PostgreSQL?

A Current date and time

B Current time only

C Current date only

D Current timestamp without time zone

NOW() returns the current date and time including hours, minutes, and seconds.

Are CURRENT_TIMESTAMP and NOW() different in PostgreSQL?

A Yes, they return different times

B No, they return the same timestamp

C CURRENT_TIMESTAMP returns date only

D NOW() returns date only

Both return the same timestamp value during a query execution.

If you want to store only the date of a user's birthday, which function is best?

A CURRENT_DATE

B NOW()

C CURRENT_TIMESTAMP

D SYSDATE

CURRENT_DATE returns only the date, which is suitable for birthdays.

Which of these is true about CURRENT_TIMESTAMP in PostgreSQL?

A It returns only the date

B It returns the current system time at call

C It returns a random timestamp

D It returns the time when the query started

CURRENT_TIMESTAMP returns the timestamp at the start of the current transaction or query.

Explain the difference between CURRENT_DATE, CURRENT_TIMESTAMP, and NOW() in PostgreSQL.

Think about what part of the date/time each function returns.

When would you choose to use CURRENT_DATE instead of NOW() in your database queries?

Consider situations where time details are unnecessary.

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