Date and time types in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the DATE data type in MySQL?

The DATE data type stores date values in the format 'YYYY-MM-DD', representing year, month, and day without time information.

intermediate

How does the DATETIME type differ from the TIMESTAMP type in MySQL?

DATETIME stores date and time without timezone conversion, while TIMESTAMP stores date and time as UTC and converts to the current timezone on retrieval.

beginner

What is the format of the TIME data type in MySQL?

TIME stores time values in the format 'HH:MM:SS', representing hours, minutes, and seconds.

intermediate

Which MySQL date/time type would you use to store only the year and month?

MySQL does not have a specific type for only year and month; you can use DATE and ignore the day or store as a string.

intermediate

What happens if you insert an invalid date like '2024-02-30' into a DATE column in MySQL?

MySQL will reject the invalid date or store '0000-00-00' depending on SQL mode settings, because '2024-02-30' is not a valid calendar date.

Which MySQL data type stores both date and time without timezone conversion?

A TIME

B DATE

C DATETIME

D TIMESTAMP

DATETIME stores date and time exactly as entered without timezone conversion.

What is the default format for the TIME data type in MySQL?

A MM-DD-YYYY

B YYYY-MM-DD

C YYYY-MM-DD HH:MM:SS

D HH:MM:SS

TIME stores time values in 'HH:MM:SS' format.

Which MySQL data type automatically converts stored time to UTC and back to local time on retrieval?

A TIMESTAMP

B TIME

C DATE

D DATETIME

TIMESTAMP stores values in UTC and converts to local time on retrieval.

If you want to store only a date without time, which MySQL type should you use?

A DATE

B TIME

C DATETIME

D TIMESTAMP

DATE stores only the date part without time.

What will MySQL do if you insert '2024-02-30' into a DATE column with strict mode enabled?

A Store '0000-00-00'

B Reject the insert with an error

C Store '2024-02-30' as is

D Automatically correct to '2024-02-29'

In strict mode, MySQL rejects invalid dates like '2024-02-30' with an error.

Explain the differences between DATE, DATETIME, and TIMESTAMP types in MySQL.

Think about what each type stores and how timezones are handled.

Describe how MySQL handles invalid dates when inserting into a DATE column.

Consider SQL mode settings and validation rules.