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.