Angular Ansible Apache HTTP Server C C++ CMake Composer CSS Cypress date-fns Django Django REST Framework Eigen3 ESLint FastAPI Git GNU Make HTML htmx HTTP JavaScript Julia LaTeX MariaDB Node.js NumPy Octave pandas Perl PHP PostgreSQL Pygame Python PyTorch Qt R SQLite Tailwind CSS

PostgreSQLで日付と時刻を扱う：timestamp型とtimestamptz型の違い


  • 小数点以下の秒数 (オプション)
  • 秒
  • 分
  • 時
  • 日
  • 月
  • 年

注意点

  • timestamp 型は、μ秒精度 (100万分の1秒) をサポートしていますが、2000年と2038年の間 の範囲でのみ有効です。
  • 内部的には、timestamp 型の値は 2000-01-01 00:00:00 UTC からの経過秒数として格納されます。
  • timestamp 型は、タイムゾーン情報 を保持しません。そのため、日時を解釈する際には、システムのタイムゾーン設定が考慮されます。

例

-- 現在の日時を 'timestamp' 型で取得
SELECT CURRENT_TIMESTAMP;

-- 特定の日時を 'timestamp' 型でリテラルとして指定
SELECT '2024-07-03 08:49:30';

timestamp 型と timestamptz 型の違い

PostgreSQL には、timestamp 型とは別に タイムゾーン情報付きの日時 を格納するために timestamptz 型が用意されています。

  • さらに、timestamptz 型は タイムゾーン情報 も保持します。これにより、日時を その場所のタイムゾーン で解釈することができます。
  • timestamptz 型は、timestamp 型と同様に 年、月、日、時、分、秒、小数点以下の秒数 を格納できます。
  • どちらの型を使用するかは、アプリケーションの要件によって決定されます。
  • timestamptz 型は、タイムゾーン情報付きで日時を格納するために使用されます。
  • timestamp 型は、タイムゾーン情報なしで日時を格納するために使用されます。

上記の説明に加えて、以下の点にも注意する必要があります。

  • 日付と時刻のデータ型に関する詳細は、PostgreSQL のドキュメントを参照してください。
  • 特定のタスクに最適なデータ型を選択することが重要です。
  • PostgreSQL は、date 型、time 型、interval 型など、他にも様々な日付と時刻のデータ型をサポートしています。

現在の日時を取得

SELECT CURRENT_TIMESTAMP;

出力

2024-07-03 08:50:32.000000+00:00

特定の日時をリテラルとして指定

SELECT '2024-07-03 08:49:30';

出力

2024-07-03 08:49:30

timestamp 型の値を文字列に変換

SELECT to_char('2024-07-03 08:49:30'::timestamp, 'YYYY-MM-DD HH:MM:SS');

出力

2024-07-03 08:49:30

文字列を timestamp 型に変換

SELECT to_timestamp('2024-07-03 08:49:30');

出力

2024-07-03 08:49:30

timestamp 型の値を比較

SELECT '2024-07-03 08:49:30'::timestamp < '2024-07-03 08:50:32'::timestamp;

出力

t

timestamp 型の値にオフセットを追加

SELECT '2024-07-03 08:49:30'::timestamp + interval '1 hour';

出力

2024-07-03 09:49:30

特定の曜日の最初の時刻を取得

SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP) + interval '1 day';

出力

2024-07-04 00:00:00

特定の月の最初の時刻を取得

SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP);

出力

2024-07-01 00:00:00

特定の年の最初の時刻を取得

SELECT DATE_TRUNC('year', CURRENT_TIMESTAMP);

2024-01-01 00:00:00

timestamptz 型

  • さらに、timestamptz 型は タイムゾーン情報 も保持します。これにより、日時を その場所のタイムゾーン で解釈することができます。
  • timestamptz 型は、timestamp 型と同様に 年、月、日、時、分、秒、小数点以下の秒数 を格納できます。

例

-- 現在の日時を 'timestamptz' 型で取得
SELECT CURRENT_TIMESTAMP;

-- 特定の日時を 'timestamptz' 型でリテラルとして指定
SELECT '2024-07-03 08:49:30 America/Los_Angeles';

timestamp 型と timestamptz 型の使い分け

  • タイムゾーン情報が必要な場合は、timestamptz 型を使用します。
  • タイムゾーン情報が不要な場合は、timestamp 型を使用します。

date 型と time 型の組み合わせ

  • time 型は、時、分、秒、小数点以下の秒数 を格納するために使用されます。
  • date 型は、年、月、日 を格納するために使用されます。

例

-- 現在の日時を 'date' 型と 'time' 型の組み合わせで取得
SELECT CURRENT_DATE, CURRENT_TIME;

文字列型

  • ただし、文字列型は、日時を操作する際に柔軟性が低くなります。
  • 文字列型は、YYYY-MM-DD HH:MM:SS 形式などの文字列で日時を表現できます。

例

-- 文字列リテラルを 'timestamp' 型に変換
SELECT to_timestamp('2024-07-03 08:49:30');

各データ型の利点と欠点

データ型利点欠点
timestampタイムゾーン情報が不要な場合にシンプルタイムゾーン情報が保持されない
timestamptzタイムゾーン情報付きで日時を格納できるtimestamp 型よりも複雑
date 型と time 型の組み合わせ柔軟性が高い2つの型を組み合わせる必要がある
文字列型人間にとって分かりやすい日時を操作する際に柔軟性が低い

timestamp 型の代替方法は、状況によって異なります。

  • より柔軟な表現が必要な場合は、date 型と time 型の組み合わせや、文字列型を使用することもできます。
  • タイムゾーン情報が必要な場合は、timestamptz 型を使用する必要があります。
  • タイムゾーン情報が不要な場合は、timestamp 型が最もシンプルです。