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'.