Related Articles Date, Time & Timestamp (this one)
Integer, Float and Bit
Enums and Sets Date, Time & Timestamp (this one) (this one) Integer, Float and Bit Integer, Float and Bit Enums and Sets Enums and Sets 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 TIMESTAMP DATE TIME YEAR DATE TIME YEAR DATETIME TIMESTAMP 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. Stores date and time in the YYYY-MM-DD HH:MM:SS format. YYYY-MM-DD HH:MM:SS Accepts dates from 1000-01-01 00:00:00 through 9999-12-31 23:59:59. 1000-01-01 00:00:00 9999-12-31 23:59:59 Can include fractional seconds up to six digits (e.g., DATETIME(6)) from MySQL 5.6 onward. DATETIME(6) 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. Internally represented as a Unix epoch, the number of seconds passed since 1970-01-01 00:00:00 UTC. 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. 1970-01-01 00:00:01 2038-01-19 03:14:07 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. explicit_defaults_for_timestamp NULL TIMESTAMP DATETIME NULL TIMESTAMP TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP 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. 0000-00-00 00:00:00 sql_mode NO_ZERO_DATE NULL 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. 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. TIMESTAMP 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. America/New_York +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. 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. sql_mode 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.

This story contains new, firsthand information uncovered by the writer.

Arweave

Navigating MySQL data types: date and time

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Asynchronous Runtimes: A Primer for the Perplexed Developer

What Are Convolution Neural Networks? [ELI5]

The Noonification: Have U Been Pwned? (1/12/2023)

Goldman Sachs, Data Lineage, and Harry Potter Spells

People are still crazy about Python after twenty-five years

10 Questions to Consider when Setting up a Corporate A.I project

Asynchronous Runtimes: A Primer for the Perplexed Developer

What Are Convolution Neural Networks? [ELI5]

The Noonification: Have U Been Pwned? (1/12/2023)

Goldman Sachs, Data Lineage, and Harry Potter Spells

People are still crazy about Python after twenty-five years

10 Questions to Consider when Setting up a Corporate A.I project

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps