MySQL TIMESTAMP

MySQL TIMESTAMP Data Type

The TIMESTAMP data type in MySQL is used to store date and time values. It is commonly used for tracking changes in records, such as when a row was created or updated.

1. TIMESTAMP vs. DATETIME

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

✔ Use TIMESTAMP when you need automatic updates and timezone conversion.
✔ Use DATETIME when working with historical data beyond 2038.

2. Creating a Table with TIMESTAMP

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

✔ order_date defaults to the current time when inserting.
✔ updated_at updates automatically whenever the row is modified.

3. Inserting Data into a TIMESTAMP Column

If you don’t provide a value, MySQL inserts the current timestamp:

INSERT INTO orders VALUES (NULL, DEFAULT, DEFAULT);

✔ The DEFAULT keyword uses the current timestamp.

To insert a specific timestamp:

INSERT INTO orders (order_date, updated_at) 
VALUES ('2024-01-01 10:30:00', '2024-01-01 10:30:00');

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

4. Retrieving TIMESTAMP Values

SELECT id, order_date, updated_at FROM orders;

✔ Displays date and time of order creation and last update.

5. Updating a TIMESTAMP Column

UPDATE orders 
SET updated_at = CURRENT_TIMESTAMP 
WHERE id = 1;

✔ Updates the updated_at column to the current time.

6. Formatting TIMESTAMP Output

Use the DATE_FORMAT() function to customize the output:

SELECT DATE_FORMAT(order_date, '%d-%m-%Y %h:%i %p') AS formatted_date 
FROM orders;

✔ Returns the timestamp in DD-MM-YYYY HH:MM AM/PM format.

7. Converting TIMESTAMP to Different Time Zones

By default, MySQL stores TIMESTAMP in UTC. You can convert it using:

SELECT CONVERT_TZ(order_date, '+00:00', '+05:30') AS local_time 
FROM orders;

✔ Converts UTC to Indian Standard Time (IST, +5:30).

8. Finding Records in a Date Range

Find orders placed within a specific time frame:

SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';

✔ Retrieves all orders from January 2024.

9. Reset Auto-Update Behavior

If you don’t want updated_at to update automatically:

ALTER TABLE orders 
MODIFY updated_at TIMESTAMP NULL DEFAULT NULL;

✔ Now, updated_at must be manually updated.

10. Summary

TIMESTAMP is smaller (4 bytes) and supports auto-updates.
Stores data in UTC but can be converted to local time zones.
Use CURRENT_TIMESTAMP for automatic date handling.
Use DATETIME if working with historical data beyond 2038.

Would you like help with time zone conversions or indexing TIMESTAMP columns? 🚀

Souy Soeng