MariaDB Timestamp Y2K38

Whatโ€™s Y2K38 ???

Timestamps in MariaDB have a maximum value of 2147483647, equivalent to 2038-01-19 05:14:07. This is due to the underlying 32-bit limitation. Using the function on a date beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.

2038๋…„ ๋ฌธ์ œ๋Š” ์‹œ๊ฐ„์„ 32๋น„ํŠธ ์ •์ˆ˜ํ˜•์œผ๋กœ ํ‘œํ˜„ํ•˜๋Š” ์‹œ์Šคํ…œ์—์„œ ๋ฐœ์ƒํ•˜๋Š” ๋ฌธ์ œ์ด๋‹ค. MariaDB(MySQL)์€ ์‹œ๊ฐ„์„ ํ‘œํ˜„ํ•˜๋Š” ๊ฒฝ์šฐ ๋ฌธ์ž์—ด๋กœ ์ €์žฅ๋˜๋Š” DATETIME๊ณผ UTC๋กœ ์ €์žฅ๋˜๋Š” TIMESTAMP๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋Š”๋ฐ TIMESTAMP ํ˜•์‹์€ 4๋ฐ”์ดํŠธ๋กœ ์ €์žฅ๋œ๋‹ค. ๋”ฐ๋ผ์„œ, DATETIME์„ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ์„ ๊ถŒ๊ณ ํ•˜๋Š” ํŽธ์ด๋ฉฐ DATETIME์€ ๋ฌธ์ž์—ด๋กœ ์ €์žฅ๋˜๋ฏ€๋กœ DATETIME์„ ์ €์žฅํ•˜๋Š” ์‹œ์ ์— UTC๋กœ ์ €์žฅ๋  ์ˆ˜ ์žˆ๋„๋ก ํƒ€์ž„์กด์„ UTC๋กœ ์„ค์ •ํ•˜๋Š” ๊ฒŒ ์ข‹๋‹ค.

MariaDB ํƒ€์ž„์กด์„ UTC๊ฐ€ ์•„๋‹Œ Aisa/Seoul๋กœ ์„ค์ •ํ•˜๋Š” ๊ฒฝ์šฐ์—๋Š” SQL ์กฐํšŒ ์‹œ CONVERT_TZ ํ•จ์ˆ˜๋ฅผ ๋งค๋ฒˆ ์‚ฌ์šฉํ•ด์•ผํ•  ํ•„์š”๊ฐ€ ์žˆ๋‹ค.

Example

SHOW VARIABLES LIKE '%time_zone%';
-- timezone: Asia/Seoul

CREATE OR REPLACE TABLE test
(
    id         BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT 'ID',
    name       VARCHAR(50)           NULL COMMENT 'Name',
    created_at DATETIME              NOT NULL DEFAULT now()
);
INSERT INTO test (name) VALUES ('mambo');
SELECT convert_tz(created_at, '+09:00', '+00:00') AS created_at FROM test;
-- created_at,created_at_utc
-- 2023-09-24 16:36:30,2023-09-24 07:36:30