TO_DATE and TO_TIMESTAMP for parsing in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to convert the string '2024-06-15' to a date using TO_DATE.

PostgreSQL

SELECT TO_DATE('2024-06-15', [1]);

Drag options to blanks, or click blank then click option'

A'YYYY-MM-DD'

B'MM-DD-YYYY'

C'DD-MM-YYYY'

D'YY-MM-DD'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong order of year, month, and day in the format string.

Using two-digit year 'YY' instead of four-digit 'YYYY'.

The format string 'YYYY-MM-DD' matches the input string '2024-06-15' for correct date parsing.

2fill in blank

medium

Complete the code to convert the string '15/06/2024 14:30:00' to a timestamp using TO_TIMESTAMP.

PostgreSQL

SELECT TO_TIMESTAMP('15/06/2024 14:30:00', [1]);

Drag options to blanks, or click blank then click option'

A'DD-MM-YYYY HH24:MI:SS'

B'DD/MM/YYYY HH24:MI:SS'

C'MM/DD/YYYY HH12:MI:SS'

D'YYYY/MM/DD HH24:MI:SS'

Attempts:

3 left

💡 Hint

Common Mistakes

Using dashes '-' instead of slashes '/' in the format string.

Using 12-hour format 'HH12' without AM/PM indicator.

The format string 'DD/MM/YYYY HH24:MI:SS' matches the input string with day, month, year separated by slashes and 24-hour time.

3fill in blank

hard

Fix the error in the code to correctly parse '2024-06-15 09:45:00' as a timestamp.

PostgreSQL

SELECT TO_TIMESTAMP('2024-06-15 09:45:00', [1]);

Drag options to blanks, or click blank then click option'

A'YYYY-MM-DD HH24:MI:SS'

B'YYYY-MM-DD HH12:MI:SS'

C'YYYY/MM/DD HH24:MI:SS'

D'YY-MM-DD HH24:MI:SS'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'HH12' without AM/PM indicator causes wrong parsing.

Using slashes '/' instead of dashes '-' in the date format.

The input uses 24-hour time format, so 'HH24' is needed instead of 'HH12'. Also, the date separator is a dash '-'.

4fill in blank

hard

Fill all three blanks to parse '06-15-2024 11:59 PM' as a timestamp correctly.

PostgreSQL

SELECT TO_TIMESTAMP('06-15-2024 11:59 PM', [1] || ' ' || [2] || ' ' || [3]);

Drag options to blanks, or click blank then click option'

A'MM-DD-YYYY'

B'HH12:MI'

C'PM'

D'HH24:MI'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 24-hour format 'HH24' when the input has 'PM'.

Forgetting to include the 'PM' meridian indicator in the format string.

The date part is 'MM-DD-YYYY', the time part is 'HH12:MI', and the meridian indicator is 'PM'. The full format string 'MM-DD-YYYY HH12:MI PM' matches the input exactly.

5fill in blank

hard

Fill all three blanks to create a query that parses '2024/06/15 23:59:59' as a timestamp.

PostgreSQL

SELECT TO_TIMESTAMP('2024/06/15 23:59:59', [1] || ' ' || [2] || ':' || [3]);

Drag options to blanks, or click blank then click option'

A'YYYY/MM/DD'

B'HH24'

C'MI:SS'

D'HH12'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 12-hour format 'HH12' without AM/PM.

Separating minutes and seconds incorrectly.

The date uses slashes with year/month/day, the hour is 24-hour format 'HH24', and minutes and seconds are 'MI:SS'.

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