[컴] mysql/mariadb 에서 timestamp 의 대안

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()

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돼야 한다. 다음 글을 참고하자.

Reference

  1. MySQL :: MySQL 8.0 Reference Manual :: 9.1.3 Date and Time Literals
  2. PHP & mySQL: Year 2038 Bug: What is it? How to solve it? - Stack Overflow
  3. Year 2038 problem - Wikipedia

댓글 없음:

댓글 쓰기