Chuyển đổi DATETIME sang INTEGER (Unix Timestamp) trong MySQL

Để chuyển kiểu DATETIME hoặc TIMESTAMP thành số nguyên Unix timestamp (giây từ 1970-01-01 00:00:00 UTC), MySQL cung cấp hàm UNIX_TIMESTAMP().

Cú pháp

SELECT UNIX_TIMESTAMP(ten_cot_datetime) AS unix_timestamp FROM ten_bang;

Ví dụ minh họa

1. Tạo bảng

CREATE TABLE DatetimeToInteger (
    Id INT NOT NULL AUTO_INCREMENT,
    ArrivalTime DATETIME,
    PRIMARY KEY(Id)
);

2. Chèn dữ liệu

INSERT INTO DatetimeToInteger(ArrivalTime) VALUES(NOW());
INSERT INTO DatetimeToInteger(ArrivalTime) VALUES(CURDATE());
INSERT INTO DatetimeToInteger(ArrivalTime) VALUES('2017-09-21 13:10:55');
INSERT INTO DatetimeToInteger(ArrivalTime) VALUES(DATE_ADD(NOW(), INTERVAL 3 YEAR));

3. Xem dữ liệu gốc

SELECT * FROM DatetimeToInteger;

Kết quả (ví dụ):

+----+---------------------+
| Id | ArrivalTime         |
+----+---------------------+
| 1  | 2019-01-22 15:12:45 |
| 2  | 2019-01-22 00:00:00 |
| 3  | 2017-09-21 13:10:55 |
| 4  | 2022-01-22 15:14:32 |
+----+---------------------+

4. Chuyển sang Unix Timestamp

SELECT UNIX_TIMESTAMP(ArrivalTime) AS DateTimeToIntegerDemo 
FROM DatetimeToInteger;

Kết quả:

+-----------------------+
| DateTimeToIntegerDemo |
+-----------------------+
| 1548150165            |
| 1548095400            |
| 1505979655            |
| 1642844672            |
+-----------------------+

Chuyển ngược: INTEGER → DATETIME

SELECT FROM_UNIXTIME(1548150165) AS DateTimeResult;
+---------------------+
| DateTimeResult      |
+---------------------+
| 2019-01-22 15:12:45 |
+---------------------+

Lưu ý về múi giờ (Timezone)

UNIX_TIMESTAMP() chuyển đổi theo **múi giờ của session** (system timezone)
TIMESTAMP lưu trữ dưới dạng UTC, chuyển đổi tự động theo session timezone
DATETIME lưu trữ "naive" (không có timezone), UNIX_TIMESTAMP giả định nó ở session timezone
Để chuẩn UTC: SET time_zone = '+00:00'; trước khi gọi UNIX_TIMESTAMP

Các hàm liên quan

Hàm	Chức năng
`UNIX_TIMESTAMP()`	Now → Unix timestamp (không tham số)
`UNIX_TIMESTAMP(datetime)`	Datetime → Unix timestamp
`FROM_UNIXTIME(unix_ts)`	Unix timestamp → Datetime (session tz)
`FROM_UNIXTIME(unix_ts, format)`	Unix timestamp → String format
`UTC_TIMESTAMP()`	Now UTC → Datetime

Ứng dụng thực tế

Lưu trữ timestamp trong BIGINT để index nhanh, so sánh nhanh
Tích hợp với hệ thống khác dùng Unix time (Java, Python, JS, Redis...)
Tính khoảng cách giây: UNIX_TIMESTAMP(t2) - UNIX_TIMESTAMP(t1)
Partitioning theo thời gian trên BIGINT

⚠️ Vấn đề Year 2038

Unix timestamp 32-bit signed hết hạn vào 19-01-2038 03:14:07 UTC.
MySQL UNIX_TIMESTAMP() trả về BIGINT (64-bit) → An toàn đến năm 292 tỷ.
Nhưng ứng dụng 32-bit (PHP cũ, C++ int) có thể bị tràn số → Cân nhắc lưu DATETIME hoặc BIGINT.