Navigating MySQL data types: date and time

Introduction

Despite the seemingly simple topic, I often get questions from colleagues about the data types in MySQL. And these questions are frequently related to the cases of storing dates and times. What's more, even I, having more than 15 years of experience working with various versions of MySQL, sometimes get into trouble because of some subtle features of this database.

In this article I will analyze the features of various data types that can be used to store dates and times, and also give interesting tips on how to simplify working with them. But the most interesting thing is that I will give examples of documented behavior that few people usually know.

Types Overview

MySQL provides various data types for storing time data, namely DATETIME, TIMESTAMP, DATE, TIME, and YEAR. Some of them allow you to store time only partially, for example, you can use the DATE type to store a date, the TIME type to store time within a day, or, for example, the YEAR type if you only need to store the year value. However, the most widely used data types are DATETIME and TIMESTAMP, which nevertheless have significant differences, which I will discuss below.

DATETIME

• Stores date and time in the YYYY-MM-DD HH:MM:SS format.
• Accepts dates from 1000-01-01 00:00:00 through 9999-12-31 23:59:59.
• Can include fractional seconds up to six digits (e.g., DATETIME(6)) from MySQL 5.6 onward.
• Does not store or automatically convert time zone data.

TIMESTAMP

• Internally represented as a Unix epoch, the number of seconds passed since 1970-01-01 00:00:00 UTC.
• Accepts dates from 1970-01-01 00:00:01 through 2038-01-19 03:14:07. For MySQL 8.0+ running on 64-bit systems, this limit can be extended after 2038 year.
• Automatically adapts to the time zone of the server and, if provided, the session.
• Ideal for storing UTC by default, then converting to local time on demand.

Known Unexpected Features

Using NULL to Initialize TIMESTAMP Columns

When explicit_defaults_for_timestamp is disabled, designating NULL for a TIMESTAMP field (but not a DATETIME) can set or refresh it to the current date and time—unless the column explicitly allows NULL. TIMESTAMP's unique oddity may surprise users who think all date-time types act the same. When explicit_defaults_for_timestamp is enabled, TIMESTAMP columns align more with DATETIME and require a clause like DEFAULT CURRENT_TIMESTAMP for auto-initialization.

Zero Dates

MySQL sometimes accepts a zero date (0000-00-00 00:00:00). Depending on your sql_mode (notably NO_ZERO_DATE), inserting this value might trigger warnings or revert silently to a default when NULL is not permitted.

Time Zone Quirks

• Entering a value into a TIMESTAMP column launches a conversion from the session’s time zone to UTC. Incorrect session time zone settings can yield unexpected outcomes.
• Comprehensive time zone tables must be loaded to use location-based time zones (e.g., America/New_York). If not, MySQL may revert to simple offsets like +00:00.

Server Configuration Pitfalls

• Global time zone settings can be overridden by session-level configurations, causing inconsistencies.
• sql_mode can heavily influence how date-time data is either accepted or rejected, especially under strict conditions where invalid values are disallowed.

Summary

Knowing the specifics of different MySQL data types, understanding zero dates, time zone mechanics, and server settings will help you avoid important mistakes. This applies to both usability and performance, and most importantly, the correctness of the data you store.

Hopefully, this article will help you be more confident the next time you need to decide how to store data or time in MySQL.