PostgreSQL 日付と時間データ型
PostgreSQLをはじめとするリレーショナルデータベース（RDBMS）では、日付や時間の情報を扱うための豊富なデータ型が提供されています。これらは時系列データを効率的に管理するために極めて重要です。正しいデータ型を選択することは、データの整合性の確保、ストレージ効率の向上、そして正確な時間計算を行うための鍵となります。
本章では、DATE、TIME、TIMESTAMP という3つのコアなデータ型について、その使い方、細かな違い、そして実際のアプリケーションにおける活用シーンを深掘りします。これらのデータ型を理解することは、単にデータを保存するだけでなく、データのフィルタリング、ソーティング、および高度な計算を実行するための基盤となります。データ分析レポート、スケジューリングアプリ、システム監査ログ（オーディットログ）など、時間情報に依存する多くのシステム構築において不可欠なスキルです。
1. DATE データ型 (日付)
DATE データ型はカレンダーの日付を保存するために使用され、1日の中の時間コンポーネント（時、分、秒）は含まれません。グレゴリオ暦における特定の1日を表現します。データベースの低レイヤーでは、DATE 値は通常、特定の基準日（エポック、例えば2000年1月1日）からの経過日数を示す整数として表現されます。
1.1 構文と用法
カラムを DATE 型として宣言するには、以下の構文を使用します。
column_name DATE;日付値を挿入する際は、標準的な YYYY-MM-DD（年-月-日）フォーマットを使用します。
INSERT INTO events (event_date, event_name) VALUES ('2024-03-15', '技術セミナー');1.2 基礎的な例
誕生日の記録を管理するテーブルを作成してみましょう。
CREATE TABLE birthdays (
person_id SERIAL PRIMARY KEY,
name VARCHAR(100),
birth_date DATE
);
INSERT INTO birthdays (name, birth_date) VALUES ('Alice', '1990-05-20');
INSERT INTO birthdays (name, birth_date) VALUES ('Bob', '1985-12-10');
SELECT * FROM birthdays;出力結果：
person_id | name | birth_date
-----------+-------+------------
1 | Alice | 1990-05-20
2 | Bob | 1985-12-10
(2 rows)1.3 応用例：日付関数の活用
PostgreSQLは、日付を処理するための多くの組み込み関数を提供しています。例えば、EXTRACT() 関数を使用して、日付から特定の年、月、日を抽出できます。
SELECT name, EXTRACT(YEAR FROM birth_date) AS birth_year FROM birthdays;出力結果：
name | birth_year
-------+------------
Alice | 1990
Bob | 1985
(2 rows)また、AGE() 関数を使用して年齢を計算することもできます（指定した日付と現在のシステム日付との間の時間間隔を計算します）。
SELECT name, AGE(birth_date) AS age FROM birthdays;出力結果（現在の日付が 2024-10-27 と仮定）：
name | age
-------+----------
Alice | 34 years
Bob | 38 years
(2 rows)1.4 実務における注意点
- タイムゾーン (Time Zones): DATE データ型はタイムゾーン情報を保持しません。単にカレンダー上の「特定の日」を指します。
- 日付フォーマット: 標準フォーマットは
YYYY-MM-DDです。PostgreSQLは柔軟なため他の形式も自動変換できることが多いですが、曖昧さを避けるために標準形式を徹底するのがベストプラクティスです。 - 有効範囲: DATE 型の有効範囲は通常、紀元前4713年1月1日から西暦5874897年12月31日までです。
2. TIME データ型 (時間)
TIME データ型は1日の中の特定の時刻を保存するために使用され、日付コンポーネント（年、月、日）は含まれません。時、分、秒を含み、オプションで小数点以下の秒（ミリ秒/マイクロ秒）を保持できます。
2.1 構文と用法
カラムを TIME 型として宣言するには、以下の構文を使用します。
column_name TIME;時間値を挿入する際は、HH:MI:SS（時:分:秒）フォーマットを使用します。HH:MI:SS.MS のように小数点以下の秒を含めることも可能です。
INSERT INTO schedules (task, start_time) VALUES ('部門ミーティング', '10:00:00');2.2 基礎的な例
CREATE TABLE schedules (
task_id SERIAL PRIMARY KEY,
task VARCHAR(100),
start_time TIME
);
INSERT INTO schedules (task, start_time) VALUES ('モーニングスタンドアップ', '09:00:00');
INSERT INTO schedules (task, start_time) VALUES ('ランチ休憩', '12:30:00');
SELECT * FROM schedules;出力結果：
task_id | task | start_time
---------+---------------+------------
1 | モーニングスタンドアップ | 09:00:00
2 | ランチ休憩 | 12:30:00
(2 rows)2.3 応用例：タイムゾーン付き TIME
PostgreSQLは、時間とタイムゾーンオフセットを合わせて保存する TIME WITH TIME ZONE 型もサポートしています。
CREATE TABLE time_zones (
event VARCHAR(100),
event_time TIME WITH TIME ZONE
);
INSERT INTO time_zones (event, event_time) VALUES ('ニューヨーク会議', '14:00:00-05'); -- ニューヨーク時間 午後2時 (EST)
INSERT INTO time_zones (event, event_time) VALUES ('ロンドン会議', '19:00:00+01'); -- ロンドン時間 午後7時 (BST)
SELECT * FROM time_zones;出力結果：
event | event_time
----------------------+-------------------
ニューヨーク会議 | 14:00:00-05
ロンドン会議 | 19:00:00+01
(2 rows)2.4 実務における注意点
- タイムゾーン: デフォルトの TIME 型はタイムゾーン情報を持ちません。特定のタイムゾーンを記録する必要がある場合は、明示的に TIME WITH TIME ZONE を使用してください。
- 小数点以下の秒 (Fractional Seconds):
TIME(p)を使用して小数点以下の精度を指定できます。pは桁数です。例えば、TIME(3)はミリ秒単位まで保存可能です。 - 有効範囲: TIME 型の範囲は
00:00:00から24:00:00です。なお、24:00:00は1日の終わり（深夜零時）を表現するために使用されます。
3. TIMESTAMP 数据类型 (タイムスタンプ)
TIMESTAMP データ型は、日付と時間の情報を同時に保存します。これは事実上、DATE と TIME の機能を組み合わせたものです。主に2つのバリエーションがあります：TIMESTAMP WITHOUT TIME ZONE（タイムゾーンなし）と TIMESTAMP WITH TIME ZONE（タイムゾーンあり）です。
3.1 構文と用法
カラムを TIMESTAMP 型として宣言するには、以下の構文を使用します。
column_name TIMESTAMP; -- デフォルトはタイムゾーンなし (TIMESTAMP WITHOUT TIME ZONE)
-- または
column_name TIMESTAMP WITH TIME ZONE;値を挿入する際は、YYYY-MM-DD HH:MI:SS フォーマットを使用します。小数点以下の秒も含めることができます。タイムゾーン付きの場合は、末尾にオフセットを追加します。
INSERT INTO audit_log (event_time, description) VALUES ('2024-03-15 10:30:00', 'ユーザーログイン');
INSERT INTO audit_log (event_time, description) VALUES ('2024-03-15 10:30:00+02', 'ユーザーログイン'); -- タイムゾーン付きの記述3.2 基礎的な例：タイムゾーンなしの TIMESTAMP
CREATE TABLE audit_log (
log_id SERIAL PRIMARY KEY,
event_time TIMESTAMP,
description TEXT
);
INSERT INTO audit_log (event_time, description) VALUES ('2024-03-15 10:30:00', 'ユーザーログイン');
INSERT INTO audit_log (event_time, description) VALUES ('2024-03-15 10:45:00', 'データ更新');
SELECT * FROM audit_log;出力結果：
log_id | event_time | description
--------+---------------------+-------------
1 | 2024-03-15 10:30:00 | ユーザーログイン
2 | 2024-03-15 10:45:00 | データ更新
(2 rows)3.3 応用例：タイムゾーン付きの TIMESTAMP
CREATE TABLE meetings (
meeting_id SERIAL PRIMARY KEY,
start_time TIMESTAMP WITH TIME ZONE,
description TEXT
);
INSERT INTO meetings (start_time, description) VALUES ('2024-03-15 14:00:00-05', 'ニューヨークチーム同期会'); -- ニューヨーク時間 (EST)
INSERT INTO meetings (start_time, description) VALUES ('2024-03-15 19:00:00+01', 'ロンドンチーム同期会'); -- ロンドン時間 (BST)
SELECT * FROM meetings;出力結果（データベースサーバーのシステムタイムゾーンが UTC に設定されている場合）：
meeting_id | start_time | description
------------+----------------------------+---------------------
1 | 2024-03-15 19:00:00+00 | ニューヨークチーム同期会
2 | 2024-03-15 18:00:00+00 | ロンドンチーム同期会
(2 rows)重要なヒント: PostgreSQLは、TIMESTAMP WITH TIME ZONE（TIMESTAMPTZ）の値を内部ですべて UTC (協定世界時) に変換して統一保存します（上記の +00 は UTC を表します）。クエリ実行時には、現在のデータベース接続のタイムゾーン設定に基づいて変換された値が表示されます。
3.4 実務における注意点
タイムゾーン処理の核心的な違い:
TIMESTAMP と TIMESTAMP WITH TIME ZONE の最大の違いは、タイムゾーン情報の扱い方にあります。
- TIMESTAMP WITHOUT TIME ZONE: 入力された日付と時間をそのまま保存し、タイムゾーン変換は行いません。すべての業務データが同一のデフォルトタイムゾーンにあると想定されます。
- TIMESTAMP WITH TIME ZONE: タイムゾーン情報を含む時間を受け取り、内部で UTC に統一して保存します。データ取得時には、UTC 時間をクライアント側のタイムゾーン設定に自動変換します。
適切な型の選び方:
- 正確な「ローカルの時間点」をそのまま記録したい場合、かつアプリケーションが単一のタイムゾーンのみで動作する場合（あるいはタイムゾーン跨ぎの問題を全く考慮しない場合）は、タイムゾーンなし の型を使用します。
- アプリケーションがグローバル展開されており、異なるタイムゾーンのユーザーを扱う場合、かつイベントが発生した「絶対的な瞬間」を正確に保持する必要がある場合は、タイムゾーンあり の型を強く推奨します。
小数点精度:
TIMESTAMP(p) を使用して精度を指定できます。例えば、TIMESTAMP(6) はマイクロ秒精度のタイムスタンプを保存します。