A Complete Guide To MySQL DATETIME Data Type

A Complete Guide to MySQL DATETIME Data Type

The DATETIME data type in MySQL is used to store date and time values without timezone conversion. It is ideal for historical records, logging events, and cases where timezone changes should not affect stored values.

1. DATETIME vs. TIMESTAMP

Feature	DATETIME	TIMESTAMP
Storage	8 bytes	4 bytes
Range	'1000-01-01' to '9999-12-31'	'1970-01-01' to '2038-01-19'
Timezone Aware	No (Stored as-is)	Yes (UTC conversion)
Auto-Update	No auto-update support	Supports auto-updating

✔ Use DATETIME for storing permanent records without worrying about timezone changes.
✔ Use TIMESTAMP if you need automatic updates and timezone conversion.

2. Creating a Table with DATETIME

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATETIME NOT NULL
);

✔ The event_date column must be manually set when inserting records.

3. Inserting Data into a DATETIME Column

INSERT INTO events (event_name, event_date) 
VALUES ('New Year Party', '2025-01-01 00:00:00');

✔ DATETIME must be in 'YYYY-MM-DD HH:MM:SS' format.

To insert the current date and time:

INSERT INTO events (event_name, event_date) 
VALUES ('Meeting', NOW());

✔ NOW() returns the current timestamp.

4. Retrieving and Formatting DATETIME Values

Fetch events:

SELECT * FROM events;

Format the output:

SELECT event_name, DATE_FORMAT(event_date, '%d-%m-%Y %h:%i %p') AS formatted_date 
FROM events;

✔ Converts '2025-01-01 00:00:00' → '01-01-2025 12:00 AM'.

5. Updating DATETIME Values

Change an event date:

UPDATE events 
SET event_date = '2025-12-31 23:59:59' 
WHERE event_name = 'New Year Party';

✔ Updates the event to a new datetime value.

6. Finding Records in a Date Range

Find all events in January 2025:

SELECT * FROM events 
WHERE event_date BETWEEN '2025-01-01 00:00:00' AND '2025-01-31 23:59:59';

✔ Retrieves events within the specified month.

7. Comparing DATETIME Values

Find all events scheduled after today:

SELECT * FROM events 
WHERE event_date > NOW();

✔ Retrieves upcoming events.

8. Extracting Parts of DATETIME

Get the year:

SELECT event_name, YEAR(event_date) AS event_year FROM events;

Get the month name:

SELECT event_name, MONTHNAME(event_date) AS event_month FROM events;

Get the day of the week:

SELECT event_name, DAYNAME(event_date) AS event_day FROM events;

✔ Useful for reports and analytics.

9. Converting DATETIME to UNIX Timestamp

Convert to a Unix timestamp:

SELECT UNIX_TIMESTAMP(event_date) FROM events;

✔ Returns seconds since '1970-01-01 00:00:00' UTC.

To convert back:

SELECT FROM_UNIXTIME(1704067200);

✔ Converts Unix timestamp to DATETIME.

10. Summary

DATETIME is used for storing precise date and time without timezone conversion.
It occupies 8 bytes, whereas TIMESTAMP uses only 4 bytes.
Use NOW() to insert the current date-time.
Use DATE_FORMAT() to customize output format.
Use functions like YEAR(), MONTHNAME(), DAYNAME() to extract parts of DATETIME.

Would you like help with indexing DATETIME columns for performance optimization? 🚀

Souy Soeng

A Complete Guide To MySQL DATETIME Data Type