TO_DATE and TO_TIMESTAMP for parsing in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this TO_DATE query?

Given the query:

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

What is the output?

PostgreSQL

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

A Error: invalid input syntax for type date

B 2024-06-15 00:00:00

C 2024-06-15

D NULL

💡 Hint

TO_DATE returns a date type without time part.

TO_DATE converts a string to a date type. It returns only the date part without time.

❓ query_result

intermediate

What does this TO_TIMESTAMP query return?

Consider this query:

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

What is the output?

PostgreSQL

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

A 2024-06-15 14:30:45

B Error: invalid input syntax for type timestamp

C 2024-06-15 00:00:00

D 2024-06-15 14:30:45+00

💡 Hint

TO_TIMESTAMP returns a timestamp without time zone.

TO_TIMESTAMP parses the string into a timestamp without time zone.

📝 Syntax

advanced

Which option causes a syntax error in TO_DATE usage?

Identify which query will cause a syntax error:

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

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

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

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

💡 Hint

Check if the format matches the input string exactly.

Option D uses format 'YYYY-DD-MM' but input is '2024-06-15' which is 'YYYY-MM-DD'. This mismatch causes an error.

❓ query_result

advanced

What is the output of this TO_TIMESTAMP with fractional seconds?

Given:

SELECT TO_TIMESTAMP('2024-06-15 14:30:45.123456', 'YYYY-MM-DD HH24:MI:SS.US');

What is the output?

PostgreSQL

SELECT TO_TIMESTAMP('2024-06-15 14:30:45.123456', 'YYYY-MM-DD HH24:MI:SS.US');

A 2024-06-15 14:30:45.123456

B 2024-06-15 14:30:45

C 2024-06-15 14:30:45.123

D Error: invalid input syntax for type timestamp

💡 Hint

US means microseconds in the format string.

The format 'SS.US' parses seconds and microseconds, so fractional seconds are preserved.

🧠 Conceptual

expert

Why does TO_DATE discard time information while TO_TIMESTAMP preserves it?

Choose the best explanation:

A TO_DATE always converts input to midnight time; TO_TIMESTAMP converts input to noon time.

B TO_DATE returns a date type which stores only year, month, and day; TO_TIMESTAMP returns a timestamp type that stores date and time.

C TO_DATE converts strings to text format; TO_TIMESTAMP converts strings to binary format.

D TO_DATE and TO_TIMESTAMP both preserve time, but TO_DATE hides it by default.

💡 Hint

Think about the data types returned by each function.

TO_DATE returns a date type that stores only date parts. TO_TIMESTAMP returns a timestamp type that stores date and time parts.

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