timestamp alternative / db / database / 디비 / 데이터베이스 / 마리아db / 마리아 디비
mysql/mariadb 에서 timestamp 의 대안
timezone 의 마지막일은 2038-01-19T03:14:07Z 이다. 그래서
db 에서 timestamp 를 쓰는 것에 제약이 생겼다.
다음 글에서 처럼 Mysql 은 8.0.19 부터 datetime 에서 timezone literal
을 넣을 수 있다. 그리고 unix time 을 다루는 함수들이 64bit 을 다룰 수
있도록 변경됐다. FROM_UNIXTIME(),
UNIX_TIMESTAMP(), and CONVERT_TZ()
- MySQL :: MySQL 8.0 Reference Manual :: 1.3 What Is New in MySQL 8.0
- MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.28 (2022-01-18, General Availability)
datetime with time zone leteral
MySql v8.0.19 부터는 datetime type 일때
'2020-01-01 10:10:10+05:30' 처럼 +00:00 을 넣어서 timezone
을 추가할 수 있게 됐다.
from : ref.1
Beginning with MySQL 8.0.19, you can specify a time zone offset when inserting TIMESTAMP and DATETIME values into a table.
CREATE TABLE dt (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
col DATETIME NOT NULL
) AUTO_INCREMENT = 1;
// SELECT @@system_time_zone;
// @@system_time_zone EST
SET @@time_zone = 'SYSTEM';
INSERT INTO dt (col) VALUES
('2020-01-01 10:10:10'),
('2020-01-01 10:10:10+05:30'), // 현재 설정된 time zone 과 time zone literal 이 같이 적용된다.
('2020-01-01 10:10:10-08:00');
SET @@time_zone = '+00:00';
INSERT INTO dt (col) VALUES
('2020-01-01 10:10:10'),
('2020-01-01 10:10:10+05:30'),
('2020-01-01 10:10:10-08:00');
SELECT col, UNIX_TIMESTAMP(col) FROM dt ORDER BY id;
+---------------------+---------------------+
| col | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 | 1577891410 |
| 2019-12-31 23:40:10 | 1577853610 |
| 2020-01-01 13:10:10 | 1577902210 |
| 2020-01-01 10:10:10 | 1577891410 |
| 2020-01-01 04:40:10 | 1577871610 |
| 2020-01-01 18:10:10 | 1577920210 |
+---------------------+---------------------+mariaDB 는 지원하지 않는다
mariaDB 에서는 아직 datetime 에 +00:00 을 사용하는 것을 지원하지 않는다.
SELECT NOW() AS utc_time, CONVERT_TZ(NOW(), @@session.time_zone) AS local_time;
mariadb 에서 대안
time_zone 을 위한 column 을 따로 두라고 하지만, 이값에 대한 개인적인 의견:
- datetime 을 사용하고,
- 사용할 때 column 이름에 UTC 를 적어준다.
- 그리고 timezone 에 영향을 받지 않는 함수를 이용해서 UTC값을 insert 한다.
mariadb 에서 timezone에 영향받지 않는 함수
from: Time Zone Effects on Functions - MariaDB Knowledge Base
- timezone 에 영향을 받는 함수
- NOW()
- SYSDATE()
- CURDATE()
- CURTIME()
- UNIX_TIMESTAMP()
- timezone에 영향을 받지 않는 함수
- UTC_DATE()
- UTC_TIME()
- UTC_TIMESTAMP()
CONVERT_TZ :
-- '2023-05-11 11:00:00' 시간을 `+00:00` timezone 에서 `+09:00` timezone으로 변경
`SELECT CONVERT_TZ('2023-05-11 11:00:00', '+00:00', '+09:00');`named timezone
이름으로 된 timezone 을 사용하려면(예를들어, Asia/Seoul), time_zone table들이 load돼야 한다. 다음 글을 참고하자.
