MySQL Working with Dates
MySQL Working with Dates
MySQL provides a variety of date and time data types and functions to store, manipulate, and retrieve date and time values efficiently. Dates are often used for recording timestamps, calculating durations, and filtering data.
🔹 Date and Time Data Types
| Data Type | Format | Description |
|---|---|---|
DATE |
'YYYY-MM-DD' |
Stores date only |
DATETIME |
'YYYY-MM-DD HH:MM:SS' |
Stores date and time |
TIMESTAMP |
'YYYY-MM-DD HH:MM:SS' |
Stores UTC date and time, auto-updates on change |
TIME |
'HH:MM:SS' |
Stores time only |
YEAR |
'YYYY' |
Stores year only |
🔹 Example Table with Dates
-
event_date→ stores only the date -
start_time→ stores only the time -
created_at→ automatically stores the timestamp when row is inserted
🔹 Common Date Functions
| Function | Description & Example |
|---|---|
NOW() |
Returns current date and time: 2025-12-02 10:30:00 |
CURDATE() |
Returns current date: 2025-12-02 |
CURTIME() |
Returns current time: 10:30:00 |
DATE() |
Extracts date from DATETIME: DATE(NOW()) → 2025-12-02 |
YEAR(date) |
Returns year from date: YEAR('2025-12-02') → 2025 |
MONTH(date) |
Returns month: MONTH('2025-12-02') → 12 |
DAY(date) |
Returns day: DAY('2025-12-02') → 2 |
DATE_ADD(date, INTERVAL n unit) |
Adds interval: DATE_ADD('2025-12-02', INTERVAL 5 DAY) → 2025-12-07 |
DATE_SUB(date, INTERVAL n unit) |
Subtracts interval: DATE_SUB('2025-12-02', INTERVAL 2 MONTH) → 2025-10-02 |
DATEDIFF(date1, date2) |
Returns difference in days: DATEDIFF('2025-12-02','2025-11-25') → 7 |
STR_TO_DATE(str, format) |
Converts string to date: STR_TO_DATE('02-12-2025','%d-%m-%Y') |
DATE_FORMAT(date, format) |
Formats date: DATE_FORMAT(NOW(), '%d-%m-%Y') → 02-12-2025 |
🔹 Example Queries
-
Insert with Date
-
Select events in December
-
Add 7 days to event_date
-
Difference between two dates
-
Format date
🔹 Key Points
-
Use the appropriate date/time data type based on your requirement.
-
MySQL functions like
NOW(),CURDATE(), andDATE_ADD()help manipulate dates easily. -
DATE_FORMAT()allows custom display of dates. -
DATEDIFF()is useful for calculating durations between two dates. -
Always use YYYY-MM-DD format for dates to avoid confusion.
