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

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

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

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

Hint

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

Practice

1. What does the PostgreSQL function TO_DATE('2024-06-15', 'YYYY-MM-DD') return?

easy

A. A timestamp value with time set to midnight

B. A date value representing June 15, 2024

C. A string '2024-06-15'

D. An error because format is incorrect

Solution

1. Step 1: Understand TO_DATE function

   TO_DATE converts a text string to a date type using the given format.

2. Step 2: Match input and format

   The string '2024-06-15' matches the format 'YYYY-MM-DD', so it converts correctly to a date.

3. Final Answer:

   A date value representing June 15, 2024 -> Option B

4. Quick Check:

   TO_DATE returns date type [OK]

Hint: TO_DATE returns date, TO_TIMESTAMP returns timestamp [OK]

Common Mistakes:

• Confusing TO_DATE with TO_TIMESTAMP
• Using wrong format string causing errors
• Expecting string output instead of date

2. Which of the following is the correct syntax to convert the string '15/06/2024 14:30' to a timestamp using TO_TIMESTAMP?

easy

A. TO_TIMESTAMP('15/06/2024 14:30', 'DD/MM/YYYY HH24:MI')

B. TO_TIMESTAMP('15/06/2024 14:30', 'MM/DD/YYYY HH24:MI')

C. TO_TIMESTAMP('15/06/2024 14:30', 'YYYY-MM-DD HH24:MI')

D. TO_TIMESTAMP('15/06/2024 14:30', 'DD-MM-YYYY HH12:MI')

Solution

1. Step 1: Analyze the input string format

   The input is '15/06/2024 14:30' which is day/month/year with 24-hour time.

2. Step 2: Match format string correctly

   'DD/MM/YYYY HH24:MI' matches day/month/year and 24-hour hour:minute format.

3. Final Answer:

   TO_TIMESTAMP('15/06/2024 14:30', 'DD/MM/YYYY HH24:MI') -> Option A

4. Quick Check:

   Format must match input exactly [OK]

Hint: Match format tokens exactly to input string [OK]

Common Mistakes:

• Using MM for day or DD for month
• Using HH12 instead of HH24 for 24-hour time
• Wrong separator characters in format

3. What is the result of this query?

SELECT TO_TIMESTAMP('2024-06-15 08:45:30', 'YYYY-MM-DD HH24:MI:SS');

medium

A. String '2024-06-15 08:45:30'

B. 2024-06-15 (date only)

C. 2024-06-15 08:45:30 (timestamp without time zone)

D. Error: format mismatch

Solution

1. Step 1: Understand TO_TIMESTAMP output

   TO_TIMESTAMP converts text to a timestamp type including date and time.

2. Step 2: Check format matches input

   The format 'YYYY-MM-DD HH24:MI:SS' matches the input string exactly.

3. Final Answer:

   2024-06-15 08:45:30 (timestamp without time zone) -> Option C

4. Quick Check:

   TO_TIMESTAMP returns timestamp type [OK]

Hint: TO_TIMESTAMP includes time, TO_DATE does not [OK]

Common Mistakes:

• Expecting date only from TO_TIMESTAMP
• Using wrong format causing errors
• Confusing string output with timestamp type

4. Identify the error in this query:

SELECT TO_DATE('06-15-2024', 'DD-MM-YYYY');

medium

A. TO_DATE requires timestamp format, not date

B. TO_DATE cannot parse dates with dashes

C. Missing time part in format string

D. Format string does not match input date format

Solution

1. Step 1: Analyze input and format

   Input is '06-15-2024' which is month-day-year with dashes.

2. Step 2: Check format string correctness

   Format 'DD-MM-YYYY' expects day-month-year, which does not match input.

3. Final Answer:

   Format string does not match input date format -> Option D

4. Quick Check:

   Input and format must align exactly [OK]

Hint: Input and format must match order and separators [OK]

Common Mistakes:

• Swapping day and month in format
• Assuming TO_DATE accepts any format
• Ignoring separator characters

5. You have a text column event_time storing timestamps like '2024/06/15 07:30:00'. Which query correctly converts this to a timestamp and extracts the hour?

hard

A. SELECT EXTRACT(HOUR FROM TO_TIMESTAMP(event_time, 'YYYY/MM/DD HH24:MI:SS')) FROM events;

B. SELECT EXTRACT(HOUR FROM TO_DATE(event_time, 'YYYY/MM/DD HH24:MI:SS')) FROM events;

C. SELECT TO_TIMESTAMP(event_time, 'YYYY-MM-DD HH24:MI:SS') FROM events;

D. SELECT EXTRACT(HOUR FROM TO_TIMESTAMP(event_time, 'DD/MM/YYYY HH24:MI:SS')) FROM events;

Solution

1. Step 1: Choose correct function for timestamp with time

   TO_TIMESTAMP converts text with date and time to timestamp type; TO_DATE only returns date.

2. Step 2: Match format string to input

   Input uses slashes and format 'YYYY/MM/DD HH24:MI:SS' matches exactly.

3. Step 3: Extract hour from timestamp

   EXTRACT(HOUR FROM timestamp) returns the hour part correctly.

4. Final Answer:

   SELECT EXTRACT(HOUR FROM TO_TIMESTAMP(event_time, 'YYYY/MM/DD HH24:MI:SS')) FROM events; -> Option A

5. Quick Check:

   Use TO_TIMESTAMP with matching format to extract time parts [OK]

Hint: Use TO_TIMESTAMP for time data, TO_DATE for date only [OK]

Common Mistakes:

• Using TO_DATE when time is needed
• Mismatching format separators
• Wrong format order for date parts