Date and time types in MySQL - Deep Dive

Overview - Date and time types

What is it?

Date and time types in MySQL are special formats used to store dates, times, or both together. They help keep track of when events happen, like birthdays or appointments. These types include DATE for dates, TIME for times, DATETIME for both date and time, TIMESTAMP for automatic time tracking, and YEAR for storing years. They make it easy to organize and calculate time-related information in databases.

Why it matters

Without date and time types, storing time information would be messy and error-prone, like writing dates as plain text that can be misunderstood or sorted incorrectly. This would make it hard to find events by date or calculate durations. Using these types ensures data is stored consistently and can be used for important tasks like scheduling, logging, or reporting, which are essential in many real-world applications.

Where it fits

Before learning date and time types, you should understand basic MySQL data types and how to create tables. After mastering date and time types, you can learn about date and time functions to manipulate and query these values, and then explore time zones and intervals for advanced time calculations.

Mental Model

Core Idea

Date and time types are structured containers that store time information in a consistent way so computers can understand and work with dates and times accurately.

Think of it like...

Think of date and time types like special boxes with labels for calendar dates or clock times. Just like you wouldn’t put a clock inside a shoe box, you put time data in the right box so you can find and use it easily later.

┌───────────────┐
│ Date and Time │
│   Types in    │
│    MySQL      │
├───────────────┤
│ DATE          │  YYYY-MM-DD (e.g., 2024-06-15)
│ TIME          │  HH:MM:SS (e.g., 14:30:00)
│ DATETIME      │  YYYY-MM-DD HH:MM:SS
│ TIMESTAMP     │  Like DATETIME but auto-updates and stores UTC
│ YEAR          │  YYYY (e.g., 2024)
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding the DATE type

Concept: Introduce the DATE type which stores calendar dates in a fixed format.

The DATE type stores only the date part: year, month, and day. It uses the format 'YYYY-MM-DD'. For example, '2024-06-15' means June 15, 2024. This type does not store time or timezone information. You use it when you only need the day, like birthdays or holidays.

Result

You can store and retrieve dates like '2024-06-15' reliably and sort them chronologically.

Understanding DATE helps you store pure calendar dates without confusion or extra data, making date-only queries simple and efficient.

2

Foundation

Exploring the TIME type

Concept: Learn about the TIME type which stores time of day without date.

The TIME type stores hours, minutes, and seconds in the format 'HH:MM:SS'. For example, '14:30:00' means 2:30 PM. It can also store negative or large values to represent durations, like '-01:00:00' for minus one hour. TIME does not include any date or timezone info.

Result

You can store times or durations like '14:30:00' or '-01:00:00' and use them in calculations.

Knowing TIME lets you handle clock times or durations separately from dates, which is useful for schedules or measuring intervals.

3

Intermediate

Combining date and time with DATETIME

🤔Before reading on: do you think DATETIME stores timezone info or just local date and time? Commit to your answer.

Concept: DATETIME stores both date and time together but does not track timezones.

DATETIME uses the format 'YYYY-MM-DD HH:MM:SS' to store a specific moment in local time. For example, '2024-06-15 14:30:00' means June 15, 2024 at 2:30 PM. It is useful when you want to record exact moments without worrying about timezone shifts.

Result

You can save and query full timestamps like '2024-06-15 14:30:00' and sort or filter by them.

Understanding DATETIME helps you capture precise moments in time locally, which is common in many applications like appointments or logs.

4

Intermediate

Using TIMESTAMP for automatic time tracking

🤔Before reading on: do you think TIMESTAMP stores time in local time or UTC? Commit to your answer.

Concept: TIMESTAMP stores date and time in UTC and can auto-update on changes.

TIMESTAMP also stores 'YYYY-MM-DD HH:MM:SS' but always in UTC (Coordinated Universal Time). MySQL converts it to local time when displaying. TIMESTAMP columns can automatically update to the current time when a row is inserted or updated, which is great for tracking changes.

Result

You get automatic recording of when data changes, and consistent time storage across time zones.

Knowing TIMESTAMP’s UTC storage and auto-update features is key for reliable logging and syncing data across different regions.

5

Intermediate

Storing just the year with YEAR type

Concept: YEAR type stores only the year part as a 4-digit number.

The YEAR type stores years in 'YYYY' format, like '2024'. It is useful when you only need to record the year, such as a birth year or model year of a product. It saves space compared to full dates.

Result

You can efficiently store and query years like '2024' without extra date or time details.

Using YEAR type optimizes storage and clarifies intent when only the year matters.

6

Advanced

Handling time zones and conversions

🤔Before reading on: do you think MySQL automatically adjusts DATETIME values for time zones? Commit to your answer.

Concept: MySQL stores DATETIME as-is without timezone conversion; TIMESTAMP converts between UTC and local time.

DATETIME stores the exact date and time you give it, without changing for time zones. TIMESTAMP stores time in UTC and converts to local time on retrieval based on server or connection settings. This means TIMESTAMP is better for global applications needing consistent time, while DATETIME is simpler for local times.

Result

You understand when to use DATETIME or TIMESTAMP depending on your timezone needs.

Knowing the difference prevents bugs where times appear shifted or inconsistent across users in different zones.

7

Expert

Surprising behaviors and storage details

🤔Before reading on: do you think DATE, TIME, and DATETIME all use the same storage size? Commit to your answer.

Concept: Date and time types differ in storage size and have quirks like zero dates and fractional seconds support.

DATE uses 3 bytes, TIME uses 3 bytes, DATETIME uses 8 bytes, and TIMESTAMP uses 4 bytes. MySQL allows 'zero' dates like '0000-00-00' which can cause confusion. Since MySQL 5.6.4, fractional seconds (microseconds) can be stored with precision for TIME, DATETIME, and TIMESTAMP. These details affect storage and precision in applications.

Result

You can optimize storage and avoid pitfalls with zero dates and fractional seconds.

Understanding storage and quirks helps design efficient schemas and avoid subtle bugs in time calculations.

Under the Hood

MySQL stores date and time types as binary numbers internally, not as text. For example, DATE stores year, month, and day as 3 bytes, encoding each part in fixed bits. TIMESTAMP stores seconds since the Unix epoch (1970-01-01 00:00:00 UTC) as a 4-byte integer, enabling automatic timezone conversion. DATETIME stores date and time parts separately without timezone conversion. When you query, MySQL converts these binary values back to readable strings.

Why designed this way?

These types were designed to balance storage efficiency, precision, and ease of use. Storing dates as numbers allows fast sorting and comparison. TIMESTAMP’s UTC storage supports global applications with timezone shifts. Allowing zero dates and fractional seconds was a tradeoff to support legacy data and high-precision needs. Alternatives like storing dates as strings would be slower and error-prone.

┌───────────────┐
│ MySQL Storage │
├───────────────┤
│ DATE          │ 3 bytes: year, month, day
│ TIME          │ 3 bytes: hours, minutes, seconds
│ DATETIME      │ 8 bytes: date + time parts
│ TIMESTAMP     │ 4 bytes: seconds since 1970-01-01 UTC
│ Fractional    │ Optional microseconds stored separately
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does DATETIME automatically adjust for time zones? Commit yes or no.

Common Belief: DATETIME automatically adjusts stored times based on the server's time zone.

Reality: DATETIME stores the exact date and time given without any timezone conversion.

Why it matters: Assuming automatic adjustment leads to wrong times shown to users, causing confusion and errors in scheduling.

Quick: Is TIMESTAMP storage size the same as DATETIME? Commit yes or no.

Common Belief: TIMESTAMP and DATETIME use the same amount of storage space.

Reality: TIMESTAMP uses 4 bytes, while DATETIME uses 8 bytes, making TIMESTAMP more storage-efficient.

Why it matters: Choosing DATETIME over TIMESTAMP without need can waste storage and reduce performance in large datasets.

Quick: Can TIME type only store times within 24 hours? Commit yes or no.

Common Belief: TIME type only stores times from 00:00:00 to 23:59:59 like a clock.

Reality: TIME can store values beyond 24 hours and even negative times, useful for durations.

Why it matters: Misunderstanding TIME limits can prevent using it for duration calculations, leading to complex workarounds.

Quick: Does MySQL reject '0000-00-00' as a date? Commit yes or no.

Common Belief: MySQL does not allow zero dates like '0000-00-00'.

Reality: MySQL allows zero dates by default, which represent 'no date' but can cause logic errors.

Why it matters: Ignoring zero dates can cause bugs in date comparisons and calculations, especially in legacy systems.

Expert Zone

1

TIMESTAMP columns automatically convert between UTC and session time zone, but this depends on server and client settings, which can cause subtle bugs if not managed carefully.

2

Fractional seconds precision in TIME, DATETIME, and TIMESTAMP types can be specified (up to microseconds), but it increases storage size and is not supported in all MySQL versions.

3

Zero dates like '0000-00-00' are allowed for backward compatibility but can break strict SQL modes or cause unexpected behavior in date functions.

When NOT to use

Avoid TIMESTAMP when you need to store dates outside the range 1970-2038 or when you want to store local times without timezone conversion. Use DATETIME instead. For durations longer than 24 hours, use TIME or integer types rather than DATETIME. When high precision is not needed, avoid fractional seconds to save space.

Production Patterns

In production, TIMESTAMP is commonly used for created_at and updated_at columns to track changes automatically. DATETIME is preferred for user-entered event times where local time matters. YEAR is used for birth years or model years to save space. Applications often convert between time zones in the application layer rather than relying on MySQL for complex timezone logic.

Connections

Unix Timestamp

TIMESTAMP type in MySQL stores time as Unix timestamp internally.

Understanding Unix timestamps helps grasp how TIMESTAMP stores and converts time values efficiently.

Time Zones in Networking

Date and time types must handle time zones correctly to avoid errors in distributed systems.

Knowing networking time zone protocols clarifies why TIMESTAMP uses UTC and how local times are derived.

Human Memory and Event Sequencing

Storing dates and times accurately helps computers mimic how humans remember event order and timing.

Understanding human event sequencing helps appreciate why precise date and time storage is crucial for meaningful data.

Common Pitfalls

#1 Storing dates as VARCHAR or TEXT instead of DATE type.

Wrong approach:

CREATE TABLE events (event_date VARCHAR(10)); INSERT INTO events VALUES ('2024-06-15');

Correct approach:

CREATE TABLE events (event_date DATE); INSERT INTO events VALUES ('2024-06-15');

Root cause:

Not understanding that DATE type enforces format and allows date functions, while VARCHAR stores plain text prone to errors.

#2 Using DATETIME when TIMESTAMP is better for tracking changes.

Wrong approach:

CREATE TABLE logs (updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

Correct approach:

CREATE TABLE logs (updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

Root cause:

Confusing DATETIME and TIMESTAMP capabilities, missing TIMESTAMP’s automatic timezone conversion and update features.

#3 Assuming TIME type cannot store durations longer than 24 hours.

Wrong approach:

CREATE TABLE durations (length TIME); INSERT INTO durations VALUES ('25:00:00'); -- error or unexpected behavior

Correct approach:

CREATE TABLE durations (length TIME); INSERT INTO durations VALUES ('25:00:00'); -- valid in MySQL

Root cause:

Misunderstanding TIME type range and its use for durations, not just clock times.

Key Takeaways

Date and time types in MySQL store time information in structured formats that computers can understand and manipulate easily.

DATE stores only calendar dates, TIME stores times or durations, DATETIME stores local date and time, and TIMESTAMP stores UTC time with automatic updates.

Choosing the right type depends on whether you need timezone handling, storage efficiency, or precision.

Understanding how MySQL stores and converts these types prevents common bugs with time zones and sorting.

Advanced features like fractional seconds and zero dates add power but require careful use to avoid subtle errors.