Date and time types in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query?

Consider the table events with a column event_date of type DATE. What will this query return?

SELECT event_date + INTERVAL 1 DAY AS next_day FROM events WHERE event_date = '2024-06-15';

MySQL

SELECT event_date + INTERVAL 1 DAY AS next_day FROM events WHERE event_date = '2024-06-15';

A The original event_date values without change

B A syntax error because INTERVAL cannot be added this way

C NULL values for all rows

D A list of dates, each one day after '2024-06-15' for matching rows

💡 Hint

Think about how MySQL adds intervals to date values.

In MySQL, adding INTERVAL 1 DAY to a DATE value returns the date one day later. So the query returns dates that are one day after '2024-06-15' for matching rows.

🧠 Conceptual

intermediate

Which MySQL data type stores both date and time?

You want to store a value that includes both the date and the time of an event. Which MySQL data type should you use?

A DATETIME

B TIME

C DATE

D YEAR

💡 Hint

Think about which type includes both parts, date and time.

The DATETIME type stores both date and time values together, unlike DATE (date only) or TIME (time only). YEAR stores only the year part.

📝 Syntax

advanced

Which query correctly extracts the year from a DATETIME column?

Given a table orders with a created_at column of type DATETIME, which query correctly returns the year part of created_at?

A SELECT created_at->year FROM orders;

B SELECT EXTRACT(YEAR created_at) FROM orders;

C SELECT YEAR(created_at) FROM orders;

D SELECT DATEPART(year, created_at) FROM orders;

💡 Hint

MySQL has a built-in function named YEAR().

In MySQL, YEAR() extracts the year from a date or datetime value. EXTRACT() syntax is valid but requires different format. DATEPART() and ->year are not valid in MySQL.

❓ optimization

advanced

How to optimize a query filtering by date only on a DATETIME column?

You have a large table logs with a created_at DATETIME column. You want to find all rows from '2024-06-01' ignoring time. Which query is optimized for using an index on created_at?

A SELECT * FROM logs WHERE DATE(created_at) = '2024-06-01';

B SELECT * FROM logs WHERE created_at >= '2024-06-01 00:00:00' AND created_at < '2024-06-02 00:00:00';

C SELECT * FROM logs WHERE created_at LIKE '2024-06-01%';

D SELECT * FROM logs WHERE created_at BETWEEN '2024-06-01 00:00:00' AND '2024-06-01 23:59:59';

💡 Hint

Functions on columns can prevent index use.

Using a range condition on the column (option B) allows MySQL to use an index efficiently. Using DATE() function or LIKE on the column disables index usage. BETWEEN with '23:59:59' can miss some times with fractional seconds.

🔧 Debug

expert

Why does this query return no rows?

Given a table appointments with a start_time column of type TIME, why does this query return no rows?

SELECT * FROM appointments WHERE start_time = '09:00';

MySQL

SELECT * FROM appointments WHERE start_time = '09:00';

A Because TIME values require seconds, so '09:00' is invalid and matches nothing

B Because the column stores DATETIME, not TIME, so comparison fails

C Because '09:00' is interpreted as a string and not converted to TIME

D Because the query syntax is incorrect and causes an error

💡 Hint

Check the format expected for TIME values in MySQL.

MySQL TIME values expect 'HH:MM:SS' format. '09:00' lacks seconds, so it does not match any TIME value stored with seconds like '09:00:30'.

Practice

1. Which MySQL data type should you use to store only a date without time?

easy

A. DATE

B. DATETIME

C. TIME

D. TIMESTAMP

Solution

1. Step 1: Understand the data types

   DATE stores only the date (year, month, day). DATETIME and TIMESTAMP store date and time. TIME stores only time.

2. Step 2: Match the requirement

   Since we want to store only the date without time, DATE is the correct choice.

3. Final Answer:

   DATE -> Option A

4. Quick Check:

   Date only = DATE [OK]

Hint: Date only? Use DATE type [OK]

Common Mistakes:

• Choosing DATETIME when only date is needed
• Using TIME for date storage
• Confusing TIMESTAMP with DATE

2. Which of the following is the correct syntax to declare a column named start_time that stores only time in MySQL?

easy

A. start_time TIME;

B. start_time DATE;

C. start_time DATETIME;

D. start_time TIMESTAMP;

Solution

1. Step 1: Identify the correct data type for time only

   The TIME type stores only time values (hours, minutes, seconds).

2. Step 2: Check syntax correctness

   Declaring a column as start_time TIME; is valid syntax in MySQL.

3. Final Answer:

   start_time TIME; -> Option A

4. Quick Check:

   Time only column = TIME type [OK]

Hint: Use TIME type for time-only columns [OK]

Common Mistakes:

• Using DATE or DATETIME for time-only columns
• Missing semicolon in declaration
• Confusing TIMESTAMP with TIME

3. Given the table events with a column event_datetime DATETIME, what will the query SELECT DATE(event_datetime) FROM events WHERE event_datetime = '2024-06-15 14:30:00'; return?

medium

A. 2024-06-15 14:30:00

B. 2024-06-15

C. 14:30:00

D. Error: Invalid function

Solution

1. Step 1: Understand the DATE() function

   The DATE() function extracts only the date part (year-month-day) from a datetime value.

2. Step 2: Apply the function to the given datetime

   Applying DATE('2024-06-15 14:30:00') returns 2024-06-15.

3. Final Answer:

   2024-06-15 -> Option B

4. Quick Check:

   DATE() extracts date only [OK]

Hint: DATE() extracts date part from datetime [OK]

Common Mistakes:

• Expecting full datetime as output
• Confusing DATE() with TIME() function
• Thinking DATE() causes error

4. You wrote this query to insert a time value: INSERT INTO schedule (meeting_time) VALUES ('25:00:00'); but it gives an error. What is the problem?

medium

A. Using DATETIME instead of TIME column type

B. Missing quotes around time value

C. Time value exceeds valid hour range (0-23)

D. Incorrect table name

Solution

1. Step 1: Check the time value format

   Valid time values must have hours between 00 and 23. '25:00:00' has 25 hours, which is invalid.

2. Step 2: Understand MySQL time validation

   MySQL rejects time values outside the valid range, causing an error on insert.

3. Final Answer:

   Time value exceeds valid hour range (0-23) -> Option C

4. Quick Check:

   Hours must be 0-23 in TIME values [OK]

Hint: Hours in TIME must be 0-23 [OK]

Common Mistakes:

• Assuming 25 is valid hour
• Forgetting quotes around time
• Confusing column type with error cause

5. You want to store the exact moment a user logs in, including date and time, and also want automatic timezone conversion to UTC. Which MySQL data type should you use?

hard

A. DATETIME

B. DATE

C. TIME

D. TIMESTAMP

Solution

1. Step 1: Understand the data types and timezone behavior

   TIMESTAMP stores date and time and automatically converts values to UTC for storage and back to local time on retrieval. DATETIME stores date and time but does not handle timezone conversion.

2. Step 2: Match requirement for timezone conversion

   Since automatic timezone conversion is needed, TIMESTAMP is the correct choice.

3. Final Answer:

   TIMESTAMP -> Option D

4. Quick Check:

   TIMESTAMP handles timezone conversion [OK]

Hint: Use TIMESTAMP for timezone-aware datetime [OK]

Common Mistakes:

• Choosing DATETIME and expecting timezone conversion
• Using DATE or TIME for full datetime
• Ignoring timezone needs