TIMESTAMP型とDATE/DATETIME型の違い - タイムゾーンの扱いまで

SQLの日時型は同じ名前でもDB製品によって意味が異なります。特に TIMESTAMP のタイムゾーン変換は、MySQL、PostgreSQL、Oracleで同一ではありません。型名だけで判断せず、保存対象が「暦上の日付」「現地の壁時計時刻」「世界で一意な瞬間」のどれかを先に決めることが重要です。

DATE, DATETIME, and TIMESTAMP do not have identical semantics across database products. This guide compares MySQL, PostgreSQL, Oracle, and SQL Server, including timezone conversion, storage ranges, type-selection criteria, and migration risks.

1. DATE型は日付だけを表す

CREATE TABLE holidays (
  holiday_date DATE
);
INSERT INTO holidays VALUES ('2026-07-22');

一般に DATE は 2026-07-22 のような日付だけを持ち、時刻を持ちません。ただしOracleの DATE は例外です。

誕生日、祝日、請求対象日など、時刻やタイムゾーンを付けても意味が増えない値には日付専用型が向きます。対して日時型は「一意な瞬間」か「壁時計に表示された日時」かで選択が変わります。

2. MySQL: DATETIMEはそのまま、TIMESTAMPはUTC変換

CREATE TABLE events (
  scheduled_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP
);

MySQLの DATETIME はタイムゾーン情報を持たず、入力値を変換せず保存します。範囲は1000年から9999年です。MySQLの TIMESTAMP は内部的にUTCで保存され、読み書き時にセッションのタイムゾーンに応じて変換されます。

TIMESTAMP の範囲は概ね1970年から2038年で、32bit UNIX timestampの2038年問題と同種の制約があります。厳密な境界はバージョンや小数秒精度も含め利用中のMySQLで確認してください。ON UPDATE CURRENT_TIMESTAMP のような自動更新も利用できます。

SET time_zone = '+09:00';

INSERT INTO events (scheduled_at)
VALUES ('2026-07-22 09:00:00');

SELECT @@session.time_zone, scheduled_at, created_at
FROM events;

同じ TIMESTAMP の行でもセッション設定が違えば表示値が変わります。接続ごとの設定を暗黙にせず、アプリ全体で統一して往復テストを行います。DATETIME は1000〜9999年という広い範囲を使えるため、遠い将来の予約や歴史的データにも候補になります。

3. PostgreSQL: TIMESTAMPとTIMESTAMPTZ

CREATE TABLE events (
  local_time TIMESTAMP,
  occurred_at TIMESTAMPTZ
);
SET TIME ZONE 'Asia/Tokyo';

PostgreSQLの TIMESTAMP は TIMESTAMP WITHOUT TIME ZONE の別名で、タイムゾーン情報を持たない素の日時です。セッションのタイムゾーンによる変換は行われません。

TIMESTAMPTZ（TIMESTAMP WITH TIME ZONE）は特定の瞬間を内部的にUTCで保持し、表示時にセッションのタイムゾーンへ変換します。名前に反して入力時のオフセットやタイムゾーン名そのものは保存されません。必要なら別カラムに保存します。

INSERT INTO events (local_time, occurred_at)
VALUES (
  '2026-07-22 09:00:00',
  '2026-07-22 09:00:00+09'
);

SET TIME ZONE 'UTC';
SELECT local_time, occurred_at FROM events;

この例では local_time は09:00のままですが、occurred_at は同じ瞬間のUTC表示へ変わります。監査目的で元のオフセットが必要ならオフセットを、将来の夏時間規則も含めて現地時刻を再現するなら Asia/Tokyo のような地域IDを別カラムに保存します。

4. OracleとSQL Serverの日時型

Oracle: DATEにも時刻が入る

CREATE TABLE events (
  happened_at DATE,
  precise_at TIMESTAMP,
  global_at TIMESTAMP WITH TIME ZONE,
  local_global_at TIMESTAMP WITH LOCAL TIME ZONE
);

Oracleの DATE は名前に反して年月日だけでなく時分秒も含みます。TIMESTAMP は小数秒を追加した型です。さらに TIMESTAMP WITH TIME ZONE と TIMESTAMP WITH LOCAL TIME ZONE があります。

WITH LOCAL TIME ZONE はデータベースのタイムゾーンへ正規化して保存し、セッションのタイムゾーンで表示します。入力、比較、表示の細かな規則は利用バージョンの公式ドキュメントで確認してください。

SQL Server: datetimeよりdatetime2を優先する

CREATE TABLE events (
  legacy_at datetime,
  occurred_at datetime2(7),
  occurred_with_offset datetimeoffset(7)
);

SQL Serverの datetime2 は従来の datetime より日付範囲が広く、小数秒精度を選択でき、精度も高いため、新規開発ではMicrosoftのドキュメントでも datetime2 の利用が推奨されています。datetime は既存スキーマとの互換性が主な利用理由です。

datetime2 自体はタイムゾーンやオフセットを保持しません。一意な瞬間とオフセットを扱う場合は datetimeoffset も候補ですが、地域のタイムゾーンIDや将来の夏時間規則まで保持する型ではありません。精度、範囲、変換関数の対応はSQL Serverのバージョンと互換性レベルを確認してください。

5. DB製品ごとの対応表

この表は設計の出発点です。小数秒精度、暗黙変換、インデックス、ドライバーによる値のマッピングは製品・バージョン・接続ライブラリで異なる可能性があります。採用前に実環境で往復テストを行ってください。

6. 「瞬間」と「壁時計時刻」で使い分ける

注文日時など「いつ起きたか」を複数タイムゾーンで扱うなら、PostgreSQLの TIMESTAMPTZ など特定の瞬間を正規化できる型が適切です。「毎日9時に開店」のような壁時計時刻にはタイムゾーンを持たない型を使い、必要なら地域のタイムゾーンIDを別に保持します。

新規スキーマでは、次の順に判断すると型名の違いに惑わされにくくなります。

1. 日付だけか: 誕生日や締日なら日付専用型を選ぶ。
2. 世界で一意な瞬間か: 作成日時、決済日時、ログならUTCへ正規化できる型を選ぶ。
3. 壁時計時刻か: 店舗の開店時刻、将来の現地予約ならタイムゾーンなし日時と地域IDを組み合わせる。
4. 範囲と精度は足りるか: 2038年以降、過去年、マイクロ秒などの要件を対象製品で確認する。

保存対象は日付だけ？
  ├─ はい → 日付専用型
  └─ いいえ
      ├─ 一意な瞬間 → UTC正規化・TZ対応型
      └─ 壁時計時刻 → TZなし日時 + 地域タイムゾーンID

「毎日9時に開店」は単なる瞬間ではありません。夏時間のある地域では、日付と地域IDから当日のオフセットを決める必要があります。逆に監査ログをタイムゾーンなしで保存すると、異なる地域のイベント順序を正しく比較できません。

7. 既存列の型を変更するときの注意点

日時型の変更はDDLだけで終わりません。タイムゾーンなしの値を瞬間型へ変える場合、既存値をどの地域の壁時計時刻として解釈するかを決める必要があります。解釈を誤ると全行が数時間ずれます。

1. 元データの意味、範囲、NULL、ゼロ日付、異常値を調査する。
2. 変換に使うタイムゾーンと小数秒の丸め規則を決める。
3. 本番相当のコピーで変換し、最小値・最大値・夏時間境界を検証する。
4. アプリ、ORM、APIのシリアライズ形式を同時に見直す。
5. インデックス再構築やテーブルロックの影響を製品ごとに確認する。
6. 移行前後の値を突合できる監査列やバックアップを用意する。

段階移行では新しい列へ二重書きし、変換済みデータを照合してから読み取り先を切り替える方法があります。具体的な ALTER TABLE のロックやオンライン変更の可否はDB製品とバージョンに依存するため、公式ドキュメントで確認してください。

8. 保存・API・表示を一貫させる

一意な瞬間はDBでUTC基準に正規化し、APIではオフセットを含むISO 8601形式にすると境界が明確です。表示時だけ利用者の地域へ変換します。壁時計予約では現地日時とIANAタイムゾーンIDを保持し、実行時点の規則で瞬間へ変換します。

// 一意な瞬間をAPIで渡す例
{ "createdAt": "2026-07-22T00:00:00Z" }

// 地域ルールが必要な将来予約の例
{
  "localDateTime": "2026-07-22T09:00:00",
  "timeZone": "Asia/Tokyo"
}

DB値が正しくても、ドライバーがローカル時刻へ暗黙変換したり、JSON化でオフセットを落としたりすると不具合になります。書き込み、読み出し、API、画面表示までを1本の経路としてテストしてください。日時差分はTIMESTAMPDIFF関数の使い方でDB別に比較しています。

よくある間違い

• TIMESTAMPは全DBで同じと思う: PostgreSQLのTZなしTIMESTAMPは変換されません。
• TIMESTAMPTZが入力オフセットも保存すると思う: PostgreSQLでは元のオフセット自体は残りません。
• OracleのDATEは日付だけと思う: 時分秒まで含みます。
• MySQLのTIMESTAMPで遠い将来を保存する: 2038年付近の上限を確認します。
• SQL Serverで新規列にもdatetimeを使う: 精度と範囲に優れるdatetime2をまず検討します。
• UTCだけで将来の現地予定を表す: 地域の夏時間規則が変わる可能性があるため地域IDも検討します。
• セッションタイムゾーンを暗黙のままにする: 接続ごとの差をなくすため設定を明示し、テストします。
• 型変更だけで移行が完了すると思う: 既存値の解釈、アプリの型、API形式も同時に移行します。

English summary

MySQL DATETIME stores a timezone-free value as entered, while MySQL TIMESTAMP converts between the session timezone and UTC and is generally limited to 1970–2038. PostgreSQL TIMESTAMP is timezone-free; TIMESTAMPTZ represents an instant without retaining the original offset. Oracle DATE includes time to the second, while Oracle TIMESTAMP adds fractional seconds.

For SQL Server, datetime2 normally offers better precision and range than legacy datetime; datetimeoffset adds an offset but does not replace a regional timezone identifier. Choose a date-only type for calendar dates, an instant-aware type for events across timezones, and a timezone-free local datetime plus an IANA zone ID for wall-clock schedules.

Changing an existing column requires a defined interpretation of old values, range and daylight-saving tests, application and ORM updates, and a rollback or reconciliation plan. Product behavior can vary by version, driver, and compatibility settings, so verify precision, conversion, and migration behavior in the documentation and in a production-like environment.

関連ツール / Related tools

関連ガイド / Related guides