Why CURRENT_DATE, CURRENT_TIMESTAMP, NOW() in PostgreSQL? - Purpose & Use Cases

The Big Idea

Discover how your database can become your personal timekeeper, saving you from messy date mistakes!

The Scenario

Imagine you need to write down the exact date and time every time a customer places an order, but you do it by hand on paper or in a simple text file.

Later, you want to find all orders from today or see when the last order was made.

The Problem

Manually writing dates and times is slow and easy to mess up.

You might forget to note the time, write the wrong date, or mix formats.

Searching or sorting these handwritten dates is almost impossible without errors.

The Solution

Using CURRENT_DATE, CURRENT_TIMESTAMP, and NOW() in your database automatically captures the exact date and time when data is added or queried.

This means no mistakes, no extra work, and easy filtering or sorting by date and time.

Before vs After

✗ Before

INSERT INTO orders (order_id, order_date) VALUES (1, '2023-04-01'); -- You type the date yourself

✓ After

INSERT INTO orders (order_id, order_date) VALUES (1, CURRENT_TIMESTAMP); -- Database adds current date and time automatically

What It Enables

You can track and analyze events by exact date and time effortlessly, making your data reliable and timely.

Real Life Example

An online store records the exact moment each purchase happens to manage stock and send timely delivery updates.

Key Takeaways

Manual date/time entry is slow and error-prone.

Database functions like CURRENT_DATE and NOW() automate accurate timestamps.

This makes data tracking and analysis simple and reliable.

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