Changes between Initial Version and Version 1 of MariaDB TIMESTAMP

Timestamp:

Nov 11, 2025, 10:36:13 AM (4 weeks ago)

Author:

yongwoo

Comment:

—

MariaDB TIMESTAMP

@@ +1 @@
+[[PageOutline]]
+= 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.)
+}}}
+
+
+이 때는 위와 같이 설정하면 된다.
+
+----
+[WikiStart 처음으로]