| Version 1 (modified by , 4 weeks ago) ( diff ) |
|---|
MariaDB TIMESTAMP
TIMESTAMP와 DATETIME 비교
1. time_zone
DATETIME은 항상 입력한 값 그대로 일정하지만 TIMESTAMP는 time_zone 변수에 따라 값을 자동 변환하여 처리한다.
MariaDB> CREATE OR REPLACE TABLE timezone_test ( dt DATETIME, ts TIMESTAMP ); MariaDB> SET time_zone = 'Asia/Seoul'; MariaDB> INSERT INTO timezone_test VALUES ((NOW()),(NOW())); MariaDB> SELECT * FROM timezone_test; +---------------------+---------------------+ | dt | ts | +---------------------+---------------------+ | 2022-11-01 12:40:23 | 2022-11-01 12:40:23 | +---------------------+---------------------+ MariaDB> SET time_zone = 'America/New_York'; MariaDB> SELECT * FROM timezone_test; +---------------------+---------------------+ | dt | ts | +---------------------+---------------------+ | 2022-11-01 12:40:23 | 2022-10-31 23:40:23 | +---------------------+---------------------+
따라서 글로벌 환경을 감안해서 개발할 때는 DATETIME보다 TIMESTAMP를 사용해야 한다. 그러나, 글로벌 환경이 아닌 경우에는 굳이 복잡하게 TIMESTAMP를 사용하는 것보다는 DATETIME을 사용하는 것이 간단하다.
주의 글로벌 환경이 아니더라도 일광절약 시간제를 사용한다면 TIMESTAMP를 고려한다.
2. 디폴트 값
TIMESTAMP는 time_zone 변수에 따라 값을 자동 변환하여 처리하는 특성 외에 다른 여러 특징이 있다.
TIMESTAMP는 레코드를 삽입하거나 업데이트할 때 명시적으로 값을 지정하지 않거나 Null을 지정하면 디폴트로 현재 시각을 자동으로 저장한다. 따라서, 데이터를 입력하거나 수정한 시간을 저장하기 편하다.
자동으로 저장하는 속성은 레코드의 첫 번째 TIMESTAMP 컬럼에만 적용된다. 두번째 TIMESTAMP 컬럼부터는 변경되지 않는다.
MariaDB> CREATE OR REPLACE TABLE timestamp_test (id INT, ts1 TIMESTAMP, ts2 TIMESTAMP); MariaDB> INSERT INTO timestamp_test (id) VALUES (1); MariaDB> SELECT * FROM timestamp_test; +------+---------------------+---------------------+ | id | ts1 | ts2 | +------+---------------------+---------------------+ | 1 | 2022-11-01 00:01:45 | 0000-00-00 00:00:00 | +------+---------------------+---------------------+ MariaDB> UPDATE timestamp_test SET id = 2 WHERE id = 1; MariaDB> SELECT * FROM timestamp_test; +------+---------------------+---------------------+ | id | ts1 | ts2 | +------+---------------------+---------------------+ | 2 | 2022-11-01 00:02:38 | 0000-00-00 00:00:00 | +------+---------------------+---------------------+
2.1. TIMESTAMP의 디폴트 값을 없애려면
현재 시각이 자동 저장되는 특성을 없애려면 DEFAULT 값을 Null이 아닌 다른 값으로 지정하면 된다.
MariaDB> CREATE OR REPLACE TABLE timestamp_test1 (id INT, ts TIMESTAMP DEFAULT 0); MariaDB> INSERT INTO timestamp_test1 (id) VALUES (1); MariaDB> SELECT * FROM timestamp_test1; +------+---------------------+ | id | ts | +------+---------------------+ | 1 | 0000-00-00 00:00:00 | +------+---------------------+ MariaDB> UPDATE timestamp_test1 SET id = 2 WHERE id = 1; MariaDB> SELECT * FROM timestamp_test1; +------+---------------------+ | id | ts1 | +------+---------------------+ | 2 | 0000-00-00 00:00:00 | +------+---------------------+
2.2. DATETIME에도 디폴트 값을 지정하려면
DATETIME 컬럼에도 TIMESTAMP처럼 레코드를 삽입하거나 업데이트할 때 디폴트로 현재 시각을 자동으로 저장하려면 DEFAULT CURRENT_TIMESTAMP 와 ON UPDATE CURRENT_TIMESTAMP를 지정해주면 된다.
MariaDB> CREATE OR REPLACE TABLE datetime_test ( id INT, dt1 DATETIME DEFAULT CURRENT_TIMESTAMP, dt2 DATETIME ON UPDATE CURRENT_TIMESTAMP, dt3 DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); MariaDB> DESC datetime_test; +-------+----------+------+-----+---------------------+-------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------------------+-------------------------------+ | id | int(11) | YES | | NULL | | | dt1 | datetime | YES | | current_timestamp() | | | dt2 | datetime | YES | | NULL | on update current_timestamp() | | dt3 | datetime | YES | | current_timestamp() | on update current_timestamp() | +-------+----------+------+-----+---------------------+-------------------------------+ MariaDB> INSERT INTO datetime_test (id) VALUES (1); MariaDB> SELECT * FROM datetime_test; +------+---------------------+------+---------------------+ | id | dt1 | dt2 | dt3 | +------+---------------------+------+---------------------+ | 1 | 2022-11-01 00:34:48 | NULL | 2022-11-01 00:34:48 | +------+---------------------+------+---------------------+ MariaDB> UPDATE datetime_test SET id = 2 WHERE id = 1; MariaDB> SELECT * FROM datetime_test; +------+---------------------+---------------------+---------------------+ | id | dt1 | dt2 | dt3 | +------+---------------------+---------------------+---------------------+ | 2 | 2022-11-01 00:34:48 | 2022-11-01 00:41:16 | 2022-11-01 00:41:16 | +------+---------------------+---------------------+---------------------+
3. 저장 범위
TIMESTAMP의 저장 가능한 시각은 UTC 기준 '1970-01-01 00:00:01' 부터 '2038-01-19 03:14:07' 까지다. 반면에 DATETIME은 '1000-01-01 00:00:00' 부터 '9999-12-31 23:59:59'까지 입력할 수 있다.
DATETIME이 저장 가능한 값의 범위가 넓은 만큼 8바이트를 차지하는데 반해 TIMESTAMP는 4바이트를 차지한다.
Time Zone 설정
mysql 데이터베이스의 Time Zone 테이블
mysql 데이터베이스에는 다음과 같은 Time Zone 테이블이 있다.
- time_zone
- time_zone_leap_second
- time_zone_name
- time_zone_transition
- time_zone_transition_type
위 Time Zone 테이블은 생성은 되지만 실제 데이터는 입력되어 있지 않다. 여기에 데이터가 입력되어 있어야 'Asia/Seoul' 같은 시간대를 설정할 수 있다.
데이터를 입력하지 않았을 때는 다음과 같은 오류가 발생한다.
MariaDB> set time_zone='Asia/Seoul'; ERROR 1298 (HY000): Unknown or incorrect time zone: 'Asia/Seoul'
데이터를 입력하려면 다음과 같이 mysql_tzinfo_to_sql 명령을 이용한다.
# /usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql
단 윈도우를 사용할 때는 mysql_tzinfo_to_sql 명령을 사용할 수 없기 때문에 다른 서버에서 데이터를 백업받아와서 복원해야 한다.
디폴트 시간대 설정
time_zone 시스템 변수로 시간대를 설정한다.
시간대는 다음과 같은 형식으로 지정할 수 있다.
- UTC로부터의 오프셋. 예를 들어 '+9:00'
- mysql 데이터베이스의 Time Zone 테이블에 데이터가 있을 때는
'Asia/Seoul'과 같은 형식으로 time_zone 변수를 지정할 수 있다.
글로벌 time_zone 변수를 지정하는 방법은 다음과 같다. mysql.time_zone_name 테이블을 참조하면 지정할 수 있는 time_zone 이름을 알 수 있다.
[mariadb] ... default_time_zone = 'Asia/Seoul'
mysql 데이터베이스의 Time Zone 테이블에 데이터가 없어도 다음과 같이 지정할 수는 있다.
[mariadb] ... default_time_zone = '+9:00'
systemd에서 쓰지 않는 리눅스 시스템에서는 mysqld_safe로 MariaDB를 구동하므로 [mysqld_safe] 항목에 지정해주면 된다.
[mysqld_safe] timezone = 'Asia/Seoul'
글로벌 time_zone 변수를 조회하려면 다음과 같이 한다.
MariaDB> SHOW GLOBAL VARIABLES LIKE 'time_zone'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | time_zone | SYSTEM | +---------------+--------+
글로벌 time_zone 변수가 SYSTEM인 경우 system_time_zone 시스템 변수에 정의된 시간대를 대신 사용한다는 뜻이므로 다음과 같이 system_time_zone 시스템 변수를 찾아봐야 한다.
MariaDB> SHOW GLOBAL VARIABLES LIKE 'system_time_zone'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | system_time_zone | KST | +------------------+-------+
위 예에서는 시간대 설정으로 'KST'를 사용한다는 것인데 MariaDB는 'KST'라는 시간대를 이해하지 못한다.
세션의 시간대 설정
따로 지정하지 않으면 세션의 시간대는 글로벌 time_zone 시스템 변수 값을 상속한다. 그러나 time_zone 시스템 변수를 바꿔 세션마다 시간대를 다르게 지정할 수 있다.
다음과 같이 바꾸면 된다.
MariaDB> SET time_zone = 'Asia/Seoul';
현재 세션의 시간대는 다음과 같이 확인할 수 있다.
MariaDB> SHOW VARIABLES LIKE 'time_zone'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | time_zone | Asia/Seoul | +---------------+------------+
JDBC 시간대 오류
JDBC로 MariaDB에 접속하면 다음과 같은 오류가 나온다. KST라는 시간대를 인식하지 못한다는 뜻이다.
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (The server time zone value KST is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.)
이 때는 위와 같이 설정하면 된다.
