TO_DATE and TO_TIMESTAMP for parsing in PostgreSQL - Mini Project: Build & Apply

Parsing Dates and Timestamps with TO_DATE and TO_TIMESTAMP in PostgreSQL

📖 Scenario: You work in a company database team. You receive raw data where dates and timestamps are stored as text strings. To use these dates in reports and calculations, you need to convert these text strings into proper date and timestamp formats.

🎯 Goal: Build SQL queries that convert text strings into DATE and TIMESTAMP data types using PostgreSQL's TO_DATE and TO_TIMESTAMP functions.

📋 What You'll Learn

Create a table called raw_events with columns event_id (integer) and event_date_text (text) and event_timestamp_text (text).

Insert specific rows with given text date and timestamp values.

Write a query using TO_DATE to convert event_date_text to a DATE column called event_date.

Write a query using TO_TIMESTAMP to convert event_timestamp_text to a TIMESTAMP column called event_timestamp.

💡 Why This Matters

🌍 Real World

Many databases receive data from external sources where dates and times are stored as text. Converting these strings to date/time types is essential for accurate filtering, sorting, and calculations.

💼 Career

Database developers and analysts often need to clean and transform raw data. Knowing how to parse dates and timestamps correctly is a fundamental skill for data preparation and reporting.

1

Create the raw_events table and insert data

Create a table called raw_events with columns event_id as integer, event_date_text as text, and event_timestamp_text as text. Then insert these exact rows: (1, '2024-06-15', '2024-06-15 14:30:00'), (2, '2024/07/01', '2024/07/01 09:15:45'), (3, '15-08-2024', '15-08-2024 20:00:00').

PostgreSQL

-- Create table raw_events and insert the given rows
-- Your code here

Need a hint?

Use CREATE TABLE to define columns with correct types. Use INSERT INTO with multiple rows.

2

Add a format string variable for date parsing

Create a variable or use a SQL expression alias called date_format with the value 'YYYY-MM-DD' to represent the date format for the first row's event_date_text. This will help parse the date string correctly.

PostgreSQL

CREATE TABLE raw_events (
    event_id INTEGER,
    event_date_text TEXT,
    event_timestamp_text TEXT
);

INSERT INTO raw_events (event_id, event_date_text, event_timestamp_text) VALUES
(1, '2024-06-15', '2024-06-15 14:30:00'),
(2, '2024/07/01', '2024/07/01 09:15:45'),
(3, '15-08-2024', '15-08-2024 20:00:00');

-- Define date_format variable
-- Your code here

Need a hint?

Use a WITH clause or a variable alias to hold the format string 'YYYY-MM-DD'.

3

Write a query using TO_DATE to parse event_date_text

Write a SELECT query that returns event_id and a new column event_date which converts event_date_text to a DATE using TO_DATE(event_date_text, 'YYYY-MM-DD') for the first row. Use the exact function TO_DATE and the format string 'YYYY-MM-DD'.

PostgreSQL

CREATE TABLE raw_events (
    event_id INTEGER,
    event_date_text TEXT,
    event_timestamp_text TEXT
);

INSERT INTO raw_events (event_id, event_date_text, event_timestamp_text) VALUES
(1, '2024-06-15', '2024-06-15 14:30:00'),
(2, '2024/07/01', '2024/07/01 09:15:45'),
(3, '15-08-2024', '15-08-2024 20:00:00');

-- We use a CTE to hold the date format for parsing
WITH date_format AS (
    SELECT 'YYYY-MM-DD'::text AS fmt
)
-- Write a SELECT query here to convert event_date_text to DATE
-- Your code here

Need a hint?

Use TO_DATE(event_date_text, 'YYYY-MM-DD') to convert the text to a date.

4

Write a query using TO_TIMESTAMP to parse event_timestamp_text

Write a SELECT query that returns event_id and a new column event_timestamp which converts event_timestamp_text to a TIMESTAMP using TO_TIMESTAMP(event_timestamp_text, 'YYYY-MM-DD HH24:MI:SS') for the first row. Use the exact function TO_TIMESTAMP and the format string 'YYYY-MM-DD HH24:MI:SS'.

PostgreSQL

CREATE TABLE raw_events (
    event_id INTEGER,
    event_date_text TEXT,
    event_timestamp_text TEXT
);

INSERT INTO raw_events (event_id, event_date_text, event_timestamp_text) VALUES
(1, '2024-06-15', '2024-06-15 14:30:00'),
(2, '2024/07/01', '2024/07/01 09:15:45'),
(3, '15-08-2024', '15-08-2024 20:00:00');

WITH date_format AS (
    SELECT 'YYYY-MM-DD'::text AS fmt
)
SELECT event_id, TO_DATE(event_date_text, 'YYYY-MM-DD') AS event_date
FROM raw_events
WHERE event_id = 1;

-- Write a SELECT query here to convert event_timestamp_text to TIMESTAMP
-- Your code here

Need a hint?

Use TO_TIMESTAMP(event_timestamp_text, 'YYYY-MM-DD HH24:MI:SS') to convert the text to a timestamp.