PostgreSQLのデータ型: timestamp, bigint, integer
PostgreSQLでUNIXタイムスタンプを扱うためのデータ型
timestamp 型
最も一般的なデータ型で、タイムゾーン情報を含む時刻を格納します。UNIXタイムスタンプは、この型にミリ秒単位で保存されます。
CREATE TABLE events (
event_id serial PRIMARY KEY,
event_time timestamp NOT NULL
);
この例では、eventsテーブルにevent_timeという名前のtimestamp型列を作成しています。この列には、イベントが発生した時刻がミリ秒単位で保存されます。
bigint 型
タイムゾーン情報を含まない時刻を格納する場合に使用します。UNIXタイムスタンプは、この型に秒単位で保存されます。
CREATE TABLE sensor_data (
sensor_id serial PRIMARY KEY,
timestamp bigint NOT NULL
);
この例では、sensor_dataテーブルにtimestampという名前のbigint型列を作成しています。この列には、センサーデータが記録された時刻が秒単位で保存されます。
integer 型
古いバージョンのPostgreSQLで使用されていたデータ型です。UNIXタイムスタンプは、この型に秒単位で保存されます。
CREATE TABLE old_data (
data_id serial PRIMARY KEY,
timestamp integer NOT NULL
);
この例では、old_dataテーブルにtimestampという名前のinteger型列を作成しています。この列には、古いバージョンのPostgreSQLで記録されたデータの時刻が秒単位で保存されます。
最適なデータ型の選択
どのデータ型を使用するかは、以下の要素を考慮して決定する必要があります。
- タイムゾーン情報が必要かどうか:
timestamp型はタイムゾーン情報を含むため、タイムゾーンを跨いでイベントを処理する必要がある場合に適しています。 - 精度:
timestamp型はミリ秒単位の精度で、bigint型は秒単位の精度です。より高い精度が必要な場合は、timestamp型を使用する必要があります。 - 互換性: 古いバージョンのPostgreSQLとの互換性を維持する必要がある場合は、
integer型を使用する必要があります。
- UNIXタイムスタンプを文字列に変換するには、
to_char()関数を使用します。
SELECT to_char(event_time, 'YYYY-MM-DD HH24:MI:SS.SSS') AS formatted_time
FROM events;
この例では、eventsテーブルのevent_time列をYYYY-MM-DD HH24:MI:SS.SSS形式の文字列に変換しています。
- 文字列をUNIXタイムスタンプに変換するには、
to_timestamp()関数を使用します。
SELECT to_timestamp('2024-05-31 23:47:59.123') AS unix_timestamp;
UNIXタイムスタンプの挿入
INSERT INTO events (event_time)
VALUES (to_timestamp('2024-05-31 23:47:59.123'));
このコードは、eventsテーブルに2024-05-31 23:47:59.123というUNIXタイムスタンプを挿入します。
SELECT event_time
FROM events;
このコードは、eventsテーブルからすべてのUNIXタイムスタンプを取得します。
UPDATE events
SET event_time = to_timestamp('2024-05-31 23:48:00')
WHERE event_id = 1;
このコードは、eventsテーブルのevent_idが1であるレコードのevent_time列を2024-05-31 23:48:00というUNIXタイムスタンプに更新します。
DELETE FROM events
WHERE event_time < to_timestamp('2024-05-31 23:00:00');
このコードは、eventsテーブルから2024-05-31 23:00:00よりも前のUNIXタイムスタンプを持つすべてのレコードを削除します。
SELECT *
FROM events
WHERE event_time BETWEEN to_timestamp('2024-05-31 23:00:00') AND to_timestamp('2024-05-31 23:59:59');
EXTRACT関数を使用して、UNIXタイムスタンプから年、月、日、時、分、秒などの情報を取り出すことができます。
SELECT
EXTRACT(year FROM event_time) AS year,
EXTRACT(month FROM event_time) AS month,
EXTRACT(day FROM event_time) AS day,
EXTRACT(hour FROM event_time) AS hour,
EXTRACT(minute FROM event_time) AS minute,
EXTRACT(second FROM event_time) AS second
FROM events;
このコードは、eventsテーブルのevent_time列から年、月、日、時、分、秒の情報を取り出し、それぞれ別の列に表示します。
TIMESTAMP WITH TIME ZONE型
タイムゾーン情報を含むUNIXタイムスタンプを格納する場合に使用します。この型は、timestamp型よりも新しいバージョンのPostgreSQLでのみ使用できます。
CREATE TABLE events_with_timezone (
event_id serial PRIMARY KEY,
event_time timestamp with time zone NOT NULL
);
この例では、events_with_timezoneテーブルにevent_timeという名前のtimestamp with time zone型列を作成しています。この列には、イベントが発生した時刻とタイムゾーン情報が保存されます。
INTERVAL型
期間を表すために使用します。UNIXタイムスタンプ同士の差を計算する際に役立ちます。
SELECT event_time + interval '1 hour' AS next_hour
FROM events;
このコードは、eventsテーブルの各レコードのevent_time列に1時間加算した値をnext_hourという列に表示します。
pg_timezone_names()関数
利用可能なタイムゾーンの一覧を取得するために使用します。
SELECT *
FROM pg_timezone_names();
このコードは、PostgreSQLで使用可能なすべてのタイムゾーンの名前を一覧表示します。
これらの方法は、PostgreSQLでUNIXタイムスタンプをより柔軟に操作するために役立ちます。
注意事項
- 上記の方法は、PostgreSQL 9.1以降で使用できます。古いバージョンのPostgreSQLを使用している場合は、使用できる機能が制限される場合があります。
postgresql postgresql-9.1
