Learning Focus

Use this lesson to understand Date, Time, DateTime with practical syntax and examples.

MySQL Date & Time Data Types: Complete Guide

---

DATE

Description

Stores calendar dates (year, month, day) without time.

Details

Feature	Value
Format	YYYY-MM-DD
Range	1000-01-01 to 9999-12-31
Storage	3 bytes
Example	2023-10-15 (October 15, 2023)

Usage

CREATE TABLE events (
    event_name VARCHAR(50),
    event_date DATE  -- Stores dates like '2023-12-25'
);

---

TIME

Description

Stores time of day (hours, minutes, seconds) or time intervals.

Details

Feature	Value
Format	HH:MM:SS (or HHH:MM:SS for intervals)
Range	-838:59:59 to 838:59:59 (up to ~34 days)
Storage	3 bytes
Example	14:30:00 (2:30 PM), -05:00:00 (5-hour interval)

Usage

CREATE TABLE schedules (
    task VARCHAR(50),
    start_time TIME,  -- '09:00:00'
    duration TIME    -- '02:30:00' (2.5 hours)
);

---

DATETIME

Description

Stores combined date and time (no timezone support).

Details

Feature	Value
Format	YYYY-MM-DD HH:MM:SS
Range	1000-01-01 00:00:00 to 9999-12-31 23:59:59
Storage	5 bytes (MySQL 5.6.4+)
Example	2023-10-15 14:30:45

Usage

CREATE TABLE orders (
    order_id INT,
    order_time DATETIME  -- '2023-10-15 08:30:00'
);

---

TIMESTAMP

Description

Stores date and time with timezone conversion (UTC).

Details

Feature	Value
Format	YYYY-MM-DD HH:MM:SS
Range	1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC
Storage	4 bytes
Timezone	Converts to/from UTC for storage
Example	2023-10-15 14:30:45 (stored as UTC)

Usage

CREATE TABLE logs (
    log_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Auto-sets to server time
);

---

YEAR

Description

Stores year values in 2- or 4-digit format.

Details

Feature	Value
Format	YYYY or YY
Range	1901 to 2155 (4-digit)
Storage	1 byte
Example	2023, 1995

Usage

CREATE TABLE cars (
    model VARCHAR(50),
    release_year YEAR  -- Stores '2023', '1999'
);

---

Key Comparisons

Type	Range	Storage	Timezone	Auto-Set
DATE	1000-01-01 to 9999-12-31	3 bytes	[X]	[X]
TIME	838:59:59	3 bytes	[X]	[X]
DATETIME	1000-01-01 00:00:00 to 9999-12-31 23:59:59	5 bytes	[X]	[X]
TIMESTAMP	1970-01-01 00:00:01 to 2038-01-19 03:14:07	4 bytes	Yes	Yes
YEAR	1901-2155	1 byte	[X]	[X]

---

Common Functions

Current Date/Time

SELECT NOW();          -- Current DATETIME (e.g., '2023-10-15 14:30:45')
SELECT CURDATE();      -- Current DATE (e.g., '2023-10-15')
SELECT CURTIME();      -- Current TIME (e.g., '14:30:45')

Extracting Parts

SELECT YEAR(order_date) FROM orders;          -- 2023
SELECT MONTH(order_date) FROM orders;         -- 10
SELECT DAY(order_date) FROM orders;           -- 15
SELECT HOUR(order_time) FROM orders;          -- 14

Date Arithmetic

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);      -- Add 7 days
SELECT DATEDIFF('2023-12-31', '2023-10-15'); -- 77 days

---

Best Practices

1. Use TIMESTAMP for:

   • Auto-updating fields (DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
   • Timezone-aware applications (e.g., global platforms).

2. Use DATETIME for:

   • Future dates beyond 2038 (TIMESTAMP's Y2038 limit).
   • Historical dates before 1970.

3. Store times in UTC if working with multiple timezones.

4. Avoid mixing DATE/DATETIME in comparisons:

   -- [X] Bad:
   WHERE DATE(order_time) = '2023-10-15';
   -- [OK] Good:
   WHERE order_time BETWEEN '2023-10-15 00:00:00' AND '2023-10-15 23:59:59';
   

---

Example Use Cases

User Registration

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    registered_at DATETIME DEFAULT NOW()  -- Account creation time
);

Global Event Scheduling

CREATE TABLE conferences (
    id INT PRIMARY KEY,
    start_time TIMESTAMP  -- Stored in UTC, converts for local time
);

Age Calculation

SELECT name, birthdate, TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age FROM users;

---

Summary

• DATE: Calendar dates only.
• TIME: Time of day or intervals.
• DATETIME: Date + time (no timezone).
• TIMESTAMP: Date + time (UTC, auto-conversion).
• YEAR: Year values (1-byte optimization).

---

Next Topics

1. Date/Time Functions (STR_TO_DATE, DATE_FORMAT)
2. Time Zone Handling
3. Temporal Indexing

Concept Map

flowchart LR
    A[Schema Context] --> B[Date, Time, DateTime]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_date_time_datetime (id INT PRIMARY KEY, value DATE_TIME_DATETIME);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Next: YEAR - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.