MariaDB講座 / DATE・DATETIME・TIMESTAMPで日付と時刻を保存する

MariaDB講座

DATEは日付、DATETIMEは年月日時分秒の値、TIMESTAMPは接続のタイムゾーンに応じて変換される時点を扱います。同じ入力でもTIMESTAMPの表示は接続によって変わるため、何を記録したいかを先に決めます。

日付・時計表示・時点の違い

誕生日や予約日はDATE、現地のカレンダー項目をそのまま扱う値はDATETIME、受信や記録の時点はTIMESTAMPが候補です。UTC（協定世界時）は時点を比較する基準で、+09:00の09時とUTCの00時は同じ時点です。

DATETIME自体に地域のタイムゾーンや夏時間規則は保存されません。UTCへ統一するか地域名と現地値を組み合わせるかはアプリケーションの方針です。TIMESTAMPも入力元の地域名を保持しません。期間計算は日付関数、設定範囲はサーバー設定で確認します。

バージョンと接続の時刻設定を調べる

実習用の接続内で実行します。

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

次に時刻設定を読みます。

SELECT @@SESSION.time_zone AS session_zone,
       @@GLOBAL.time_zone AS global_zone,
       @@system_time_zone AS system_zone;

SESSIONは現在の接続、GLOBALは新規接続の既定値です。SYSTEMならOSの設定を使うため、パソコンの設置場所だけでは判断できません。この実習は+09:00と+00:00の固定オフセットなので、名前付きゾーンのテーブルは不要です。

DATEとDATETIMEの年範囲は1000～9999です。通常のTIMESTAMPは1970-01-01 00:00:01 UTCからで、11.5未満は2038-01-19 03:14:07 UTC、11.5以降は2106-02-07 06:28:15 UTCまでです。特殊なゼロ値とは分け、実際のサーバー版を公式の範囲と照合します。

一度保存してタイムゾーンだけを変える

次の通常例は元のSQLモードと時刻設定を保存します。TIMESTAMP NULL DEFAULT NULLを明示し、版や設定で異なる暗黙の自動時刻指定を避けています。

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET @saved_time_zone = @@SESSION.time_zone;
SET SESSION time_zone = '+09:00';
CREATE TABLE time_lab (
  item_id INT PRIMARY KEY,
  event_date DATE NOT NULL,
  wall_time DATETIME NOT NULL,
  instant_time TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO time_lab VALUES
  (1, '2026-10-13', '2026-10-13 09:00:00', '2026-10-13 09:00:00');
SELECT item_id, event_date, wall_time, instant_time FROM time_lab;
SET SESSION time_zone = '+00:00';
SELECT item_id, event_date, wall_time, instant_time FROM time_lab;
SET SESSION time_zone = '+09:00';
SELECT item_id, wall_time, instant_time FROM time_lab;

最初の+09:00ではwall_timeとinstant_timeの両方が2026-10-13 09:00:00です。+00:00に変えるとDATETIMEは09時のまま、TIMESTAMPは00時になります。+09:00へ戻すとTIMESTAMPも09時に戻ります。二つの事件になったのではなく、一つの時点を別のオフセットで表示しています。event_dateは変わりません。

自動設定と小数秒を明示する

作成時刻を自動入力するならDEFAULT CURRENT_TIMESTAMP、更新時の自動変更ならON UPDATE CURRENT_TIMESTAMPを別々に指定します。最終更新時刻が外部で出来事の起きた時刻と同じとは限りません。

DATETIME(6)やTIMESTAMP(6)の6は小数秒の精度で、年の範囲や全体の文字数ではありません。列と既定式の精度を意図してそろえます。日付はYYYY-MM-DD、日時はYYYY-MM-DD HH:MM:SSのように曖昧さを避け、10/11/26のような地域依存の書式を安易に渡しません。

不正な日付と9時間のずれを調べる

同じstrictモードの接続で、別に実行します。

INSERT INTO time_lab VALUES (2, '2026-02-30', '2026-10-13 09:00:00', NULL);

存在しない2月30日はIncorrect date valueとなり、表の件数は1のままです。モードを緩めて無理に保存すると値の意味が変わり得ます。9時間ずれて見えたら列の型と@@SESSION.time_zoneを確認し、二重変換でないか調べてから判断します。いきなり全行へ9時間加減しないでください。

固定オフセットでは夏時間を含む地域の履歴を表せません。名前付きゾーンには適切なゾーンデータと運用方針が必要で、この比較は固定オフセットに限定しています。

同じ接続で設定を復元する

保存変数を持つ接続で実行します。

SET SESSION time_zone = @saved_time_zone;
SET SESSION sql_mode = @saved_sql_mode;
SELECT @@SESSION.time_zone, @@SESSION.sql_mode;

再接続後は変数が残らないため、その接続の値を確認します。実習を再作成するなら対象と保存すべき行を確認してから実行します。

DROP TABLE shopcourse.time_lab;

time_lab以外は対象にしません。DROP TABLEは定義と行を消し、通常のROLLBACKでは戻せません。