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

Need a 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

Need a 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

Need a 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

Need a hint?

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