CURRENT_DATE, CURRENT_TIMESTAMP, NOW() in PostgreSQL - Mini Project: Build & Apply

Using CURRENT_DATE, CURRENT_TIMESTAMP, and NOW() in PostgreSQL

📖 Scenario: You are managing a simple event log table in a PostgreSQL database. You want to record the date and time when each event is added.

🎯 Goal: Create a table to store event names and their timestamps. Then insert a new event using CURRENT_DATE, CURRENT_TIMESTAMP, and NOW() to record the date and time of the event.

📋 What You'll Learn

Create a table called event_log with columns event_name (text), event_date (date), and event_time (timestamp).

Insert a new event named 'UserLogin' using CURRENT_DATE for the date and CURRENT_TIMESTAMP for the timestamp.

Insert another event named 'PageView' using NOW() for the timestamp and CURRENT_DATE for the date.

Select all rows from event_log to see the stored events with their dates and times.

💡 Why This Matters

🌍 Real World

Recording event times is common in logging user actions, transactions, or system events in databases.

💼 Career

Understanding how to use date and time functions in SQL is essential for database administrators and backend developers to track and analyze data changes over time.

1

Create the event_log table

Write a SQL statement to create a table called event_log with three columns: event_name as text, event_date as date, and event_time as timestamp.

PostgreSQL

-- Create the event_log table with event_name, event_date, and event_time columns
-- Your code here

Hint

Use CREATE TABLE followed by the table name and define each column with its data type.

2

Insert an event using CURRENT_DATE and CURRENT_TIMESTAMP

Write a SQL INSERT statement to add a new row to event_log with event_name as 'UserLogin', event_date as CURRENT_DATE, and event_time as CURRENT_TIMESTAMP.

PostgreSQL

CREATE TABLE event_log (
    event_name TEXT,
    event_date DATE,
    event_time TIMESTAMP
);

-- Insert 'UserLogin' event with CURRENT_DATE and CURRENT_TIMESTAMP
-- Your code here

Hint

Use INSERT INTO with the column names and VALUES with the exact values including CURRENT_DATE and CURRENT_TIMESTAMP.

3

Insert another event using NOW() and CURRENT_DATE

Write a SQL INSERT statement to add a new row to event_log with event_name as 'PageView', event_date as CURRENT_DATE, and event_time as NOW().

PostgreSQL

CREATE TABLE event_log (
    event_name TEXT,
    event_date DATE,
    event_time TIMESTAMP
);

INSERT INTO event_log (event_name, event_date, event_time)
VALUES ('UserLogin', CURRENT_DATE, CURRENT_TIMESTAMP);

-- Insert 'PageView' event with CURRENT_DATE and NOW()
-- Your code here

Hint

Use NOW() to get the current timestamp and CURRENT_DATE for the date in the INSERT statement.

4

Select all events from event_log

Write a SQL SELECT statement to retrieve all columns and rows from the event_log table.

PostgreSQL

CREATE TABLE event_log (
    event_name TEXT,
    event_date DATE,
    event_time TIMESTAMP
);

INSERT INTO event_log (event_name, event_date, event_time)
VALUES ('UserLogin', CURRENT_DATE, CURRENT_TIMESTAMP);

INSERT INTO event_log (event_name, event_date, event_time)
VALUES ('PageView', CURRENT_DATE, NOW());

-- Select all rows and columns from event_log
-- Your code here

Hint

Use SELECT * FROM event_log; to get all data from the table.

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