Date and time types in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a column that stores date values only.

MySQL

CREATE TABLE events (event_date [1]);

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

ADATE

BDATETIME

CTIMESTAMP

DTIME

Attempts:

3 left

💡 Hint

Common Mistakes

Using DATETIME when only date is needed.

Using TIME which stores only time, not date.

The DATE type stores only date values (year, month, day) without time.

2fill in blank

medium

Complete the code to insert the current date and time into a DATETIME column.

MySQL

INSERT INTO logs (log_time) VALUES ([1]());

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

ANOW

BCURDATE

CCURRENT_DATE

DSYSDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using CURDATE() which returns only the date.

Using CURRENT_DATE which is similar to CURDATE().

NOW() returns the current date and time, suitable for DATETIME columns.

3fill in blank

hard

Fix the error in the query to select rows where the time is after 14:00:00.

MySQL

SELECT * FROM shifts WHERE shift_start > '[1]';

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

A14:00

B14

C2 PM

D14:00:00

Attempts:

3 left

💡 Hint

Common Mistakes

Using '14:00' without seconds.

Using '2 PM' which is not a valid MySQL time format.

MySQL TIME values require the format HH:MM:SS. So '14:00:00' is correct.

4fill in blank

hard

Fill both blanks to create a table with a TIMESTAMP column that defaults to the current timestamp.

MySQL

CREATE TABLE user_logins (login_time [1] DEFAULT [2]);

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

ATIMESTAMP

BCURRENT_TIMESTAMP

CNOW()

DDATETIME

Attempts:

3 left

💡 Hint

Common Mistakes

Using DATETIME with CURRENT_TIMESTAMP default (not always supported).

Using NOW() as default which is a function call, not a constant.

TIMESTAMP columns can have a default value of CURRENT_TIMESTAMP to store the current time automatically.

5fill in blank

hard

Fill all three blanks to select rows where the date is after 2023-01-01 and time is before 18:00:00.

MySQL

SELECT * FROM appointments WHERE appointment_date > '[1]' AND appointment_time < '[2]' AND appointment_date < '[3]';

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

A2023-01-01

B18:00:00

C2024-01-01

D17:00:00

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect date or time formats.

Mixing up the order of conditions.

The query filters dates after 2023-01-01, times before 18:00:00, and dates before 2024-01-01.

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