Date, time, and timestamp types in PostgreSQL - Mini Project: Build & Apply

Working with Date, Time, and Timestamp Types in PostgreSQL

📖 Scenario: You are managing a small event scheduling database. You need to store and work with dates and times for events.

🎯 Goal: Create a table to store event names with their dates and times, then write queries to extract and manipulate date and time information.

📋 What You'll Learn

Create a table named events with columns for event name, event date, event time, and event timestamp.

Insert specific event data with exact date and time values.

Write a query to select events happening on a specific date.

Write a query to select events happening after a specific timestamp.

💡 Why This Matters

🌍 Real World

Managing event schedules, appointments, or logs requires storing and querying dates and times accurately.

💼 Career

Database developers and analysts often work with date and time data to generate reports, filter records, and maintain accurate records.

1

Create the events table with date, time, and timestamp columns

Write a SQL statement to create a table called events with these columns: event_name as text, event_date as date, event_time as time, and event_timestamp as timestamp.

PostgreSQL

-- Write the CREATE TABLE statement for the events table
-- Your code here

Need a hint?

Use CREATE TABLE with the specified column names and types.

2

Insert three events with exact date and time values

Insert these three rows into the events table: ('Meeting', '2024-07-01', '09:00:00', '2024-07-01 09:00:00'), ('Lunch', '2024-07-01', '12:30:00', '2024-07-01 12:30:00'), and ('Conference', '2024-07-02', '15:00:00', '2024-07-02 15:00:00').

PostgreSQL

CREATE TABLE events (
    event_name TEXT,
    event_date DATE,
    event_time TIME,
    event_timestamp TIMESTAMP
);
-- Insert the three specified events below
-- Your code here

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Select events happening on July 1, 2024

Write a SQL query to select all columns from events where event_date is exactly '2024-07-01'.

PostgreSQL

CREATE TABLE events (
    event_name TEXT,
    event_date DATE,
    event_time TIME,
    event_timestamp TIMESTAMP
);

INSERT INTO events (event_name, event_date, event_time, event_timestamp) VALUES
('Meeting', '2024-07-01', '09:00:00', '2024-07-01 09:00:00'),
('Lunch', '2024-07-01', '12:30:00', '2024-07-01 12:30:00'),
('Conference', '2024-07-02', '15:00:00', '2024-07-02 15:00:00');

-- Select events where event_date is '2024-07-01'
-- Your code here

Need a hint?

Use WHERE event_date = '2024-07-01' to filter by date.

4

Select events happening after July 1, 2024 at 10:00 AM

Write a SQL query to select all columns from events where event_timestamp is greater than '2024-07-01 10:00:00'.

PostgreSQL

CREATE TABLE events (
    event_name TEXT,
    event_date DATE,
    event_time TIME,
    event_timestamp TIMESTAMP
);

INSERT INTO events (event_name, event_date, event_time, event_timestamp) VALUES
('Meeting', '2024-07-01', '09:00:00', '2024-07-01 09:00:00'),
('Lunch', '2024-07-01', '12:30:00', '2024-07-01 12:30:00'),
('Conference', '2024-07-02', '15:00:00', '2024-07-02 15:00:00');

SELECT * FROM events WHERE event_date = '2024-07-01';

-- Select events where event_timestamp is after '2024-07-01 10:00:00'
-- Your code here

Need a hint?

Use WHERE event_timestamp > '2024-07-01 10:00:00' to filter by timestamp.