Date & Time Types0%

Date & Time Types

Beginner12 min read•Updated: 2026-09-12

Date & Time Types: DATE, TIME, DATETIME, and TIMESTAMP

Nearly every real-world application requires handling time: timestamps for when an order was placed, account creation dates, subscription renewal deadlines, and employee shift durations. Choosing between DATE, TIME, DATETIME, and TIMESTAMP requires understanding how MySQL handles time zones.

1. MySQL Temporal Data Types Overview

Type	Format	Storage	Range	Time Zone Aware?
`DATE`	`YYYY-MM-DD`	3 bytes	`1000-01-01` to `9999-12-31`	No
`TIME`	`HH:MM:SS[.fraction]`	3 bytes	`-838:59:59` to `838:59:59`	No
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	5 bytes	`1000-01-01 00:00:00` to `9999-12-31 23:59:59`	No (Constant)
`TIMESTAMP`	`YYYY-MM-DD HH:MM:SS`	4 bytes	`1970-01-01 00:00:01` UTC to `2038-01-19 03:14:07` UTC	Yes (UTC converted)
`YEAR`	`YYYY`	1 byte	`1901` to `2155`	No

2. The Great Debate: `DATETIME` vs `TIMESTAMP`

The most common point of confusion is choosing between DATETIME and TIMESTAMP.

 Client in Tokyo (+09:00) stores '2026-03-15 18:00:00'
 
 If DATETIME:
 Stored on disk as: '2026-03-15 18:00:00' (Exact literal string)
 Client in New York (-05:00) reads: '2026-03-15 18:00:00' (NO timezone adjustment!)
6 
 If TIMESTAMP:
 MySQL converts Tokyo time to UTC: '2026-03-15 09:00:00' UTC stored on disk.
 Client in New York (-05:00) reads: '2026-03-15 04:00:00' (AUTOMATIC timezone conversion!)

When to Use `TIMESTAMP`:

Event tracking, audit logs, created_at and updated_at record timestamps.
When your system serves users globally across different time zones.

When to Use `DATETIME`:

Dates that represent fixed calendar appointments (e.g., "Doctor Appointment on Dec 25 at 10:00 AM regardless of where you view it from").
Dates far in the future or past (e.g., historical dates before 1970, or mortgages expiring after the year 2038).

Important Warning

[!WARNING] The Year 2038 Problem: TIMESTAMP is stored as a 32-bit integer representing seconds since the Unix Epoch (Jan 1, 1970). On January 19, 2038, 32-bit timestamps will overflow! Modern systems frequently adopt DATETIME or 64-bit timestamps to avoid this limitation.

3. Automatic Timestamps: `created_at` & `updated_at`

MySQL allows columns to populate and update their timestamps automatically:

1CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  
  -- Automatically set to current timestamp when row is inserted:
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- Automatically set to current timestamp when row is updated:
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
11);

4. Querying & Manipulating Dates

1-- Insert using standard ISO-8601 string format:
2INSERT INTO orders (customer_id, total_amount) VALUES (101, 4999.00);
3 
4-- Extract parts of a date:
5SELECT 
6    order_id, 
7    created_at,
8    YEAR(created_at) AS order_year,
9    MONTHNAME(created_at) AS order_month,
10    DATE_FORMAT(created_at, '%d-%M-%Y %h:%i %p') AS formatted_date
11FROM orders;

5. Best Practices & Common Pitfalls

Always Store in ISO Format (YYYY-MM-DD): Never store dates as VARCHAR strings like "15/03/2026". Storing strings breaks date sorting ('02/10/2025' sorts before '15/01/2020'), breaks date arithmetic, and prevents indexing!
Store UTC in Application Tier: When using DATETIME, adopt the industry best practice of saving all records in Coordinated Universal Time (UTC), converting to local time only in the UI layer.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which MySQL temporal type automatically converts values to UTC for storage and converts back to the client session timezone on retrieval?

Question 2

What causes the "Year 2038 Problem" with MySQL TIMESTAMP columns?

Question 3

What is the standard SQL format for entering a date literal in MySQL?

Question 4

Which clause automatically updates a timestamp column whenever any column in that row is modified?

Question 5

Why is storing dates in a VARCHAR(20) column considered a major database design mistake?

Next Lesson

Specialized Types: ENUM, BOOLEAN, JSON

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
String & Text Types	Specialized Types: ENUM, BOOLEAN, JSON

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Specialized Types: ENUM, BOOLEAN, JSON

Date & Time Types

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Date & Time Types: DATE, TIME, DATETIME, and TIMESTAMP

1. MySQL Temporal Data Types Overview

Type	Format	Storage	Range	Time Zone Aware?
`DATE`	`YYYY-MM-DD`	3 bytes	`1000-01-01` to `9999-12-31`	No
`TIME`	`HH:MM:SS[.fraction]`	3 bytes	`-838:59:59` to `838:59:59`	No
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	5 bytes	`1000-01-01 00:00:00` to `9999-12-31 23:59:59`	No (Constant)
`TIMESTAMP`	`YYYY-MM-DD HH:MM:SS`	4 bytes	`1970-01-01 00:00:01` UTC to `2038-01-19 03:14:07` UTC	Yes (UTC converted)
`YEAR`	`YYYY`	1 byte	`1901` to `2155`	No

2. The Great Debate: `DATETIME` vs `TIMESTAMP`

The most common point of confusion is choosing between DATETIME and TIMESTAMP.

 Client in Tokyo (+09:00) stores '2026-03-15 18:00:00'
 
 If DATETIME:
 Stored on disk as: '2026-03-15 18:00:00' (Exact literal string)
 Client in New York (-05:00) reads: '2026-03-15 18:00:00' (NO timezone adjustment!)
6 
 If TIMESTAMP:
 MySQL converts Tokyo time to UTC: '2026-03-15 09:00:00' UTC stored on disk.
 Client in New York (-05:00) reads: '2026-03-15 04:00:00' (AUTOMATIC timezone conversion!)

When to Use `TIMESTAMP`:

Event tracking, audit logs, created_at and updated_at record timestamps.
When your system serves users globally across different time zones.

When to Use `DATETIME`:

Dates that represent fixed calendar appointments (e.g., "Doctor Appointment on Dec 25 at 10:00 AM regardless of where you view it from").
Dates far in the future or past (e.g., historical dates before 1970, or mortgages expiring after the year 2038).

Important Warning

3. Automatic Timestamps: `created_at` & `updated_at`

MySQL allows columns to populate and update their timestamps automatically:

1CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  
  -- Automatically set to current timestamp when row is inserted:
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- Automatically set to current timestamp when row is updated:
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
11);

4. Querying & Manipulating Dates

1-- Insert using standard ISO-8601 string format:
2INSERT INTO orders (customer_id, total_amount) VALUES (101, 4999.00);
3 
4-- Extract parts of a date:
5SELECT 
6    order_id, 
7    created_at,
8    YEAR(created_at) AS order_year,
9    MONTHNAME(created_at) AS order_month,
10    DATE_FORMAT(created_at, '%d-%M-%Y %h:%i %p') AS formatted_date
11FROM orders;

5. Best Practices & Common Pitfalls

Always Store in ISO Format (YYYY-MM-DD): Never store dates as VARCHAR strings like "15/03/2026". Storing strings breaks date sorting ('02/10/2025' sorts before '15/01/2020'), breaks date arithmetic, and prevents indexing!
Store UTC in Application Tier: When using DATETIME, adopt the industry best practice of saving all records in Coordinated Universal Time (UTC), converting to local time only in the UI layer.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which MySQL temporal type automatically converts values to UTC for storage and converts back to the client session timezone on retrieval?

Question 2

What causes the "Year 2038 Problem" with MySQL TIMESTAMP columns?

Question 3

What is the standard SQL format for entering a date literal in MySQL?

Question 4

Which clause automatically updates a timestamp column whenever any column in that row is modified?

Question 5

Why is storing dates in a VARCHAR(20) column considered a major database design mistake?

Next Lesson

Specialized Types: ENUM, BOOLEAN, JSON

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
String & Text Types	Specialized Types: ENUM, BOOLEAN, JSON

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Date & Time Types: DATE, TIME, DATETIME, and TIMESTAMP

1. MySQL Temporal Data Types Overview

2. The Great Debate: DATETIME vs TIMESTAMP

When to Use TIMESTAMP:

When to Use DATETIME:

3. Automatic Timestamps: created_at & updated_at

4. Querying & Manipulating Dates

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Specialized Types: ENUM, BOOLEAN, JSON

Related Lessons

Practice Quiz

Loading Page Content...

Date & Time Types: DATE, TIME, DATETIME, and TIMESTAMP

1. MySQL Temporal Data Types Overview

2. The Great Debate: DATETIME vs TIMESTAMP

When to Use TIMESTAMP:

When to Use DATETIME:

3. Automatic Timestamps: created_at & updated_at

4. Querying & Manipulating Dates

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Specialized Types: ENUM, BOOLEAN, JSON

Related Lessons

Practice Quiz

2. The Great Debate: `DATETIME` vs `TIMESTAMP`

When to Use `TIMESTAMP`:

When to Use `DATETIME`:

3. Automatic Timestamps: `created_at` & `updated_at`

2. The Great Debate: `DATETIME` vs `TIMESTAMP`

When to Use `TIMESTAMP`:

When to Use `DATETIME`:

3. Automatic Timestamps: `created_at` & `updated_at`