データベース分析の必須テクニック！PostgreSQLクエリから時間を効率的に取り出す方法
PostgreSQL クエリから時間を抽出する方法
date_part 関数は、TIMESTAMP 型または DATE 型の値から特定の日付/時刻コンポーネントを抽出するために使用されます。時間を抽出するには、'hour' という文字列を最初の引数として渡し、TIMESTAMP または DATE 型の値を 2 番目の引数として渡します。
SELECT date_part('hour', my_timestamp_column);
このクエリは、my_timestamp_column カラムにある各 TIMESTAMP 値の時間を抽出し、結果を新しい列に格納します。
SELECT to_char(my_timestamp_column, 'HH24');
SELECT EXTRACT(hour FROM my_timestamp_column);
例
以下の表は、上記の各方法を使用して my_timestamp_column カラムから時間を抽出するクエリを示しています。
| 方法 | クエリ |
|---|---|
date_part | SELECT date_part('hour', my_timestamp_column); |
to_char | SELECT to_char(my_timestamp_column, 'HH24'); |
EXTRACT | SELECT EXTRACT(hour FROM my_timestamp_column); |
ヒント
- 上記のクエリを組み合わせて、日付と時刻の両方を含む新しい列を作成することもできます。たとえば、次のようなクエリを使用して、
my_timestamp_columnカラムにある各 TIMESTAMP 値の日付と時刻を新しい列に格納できます。
SELECT to_char(my_timestamp_column, 'YYYY-MM-DD HH24:MI:SS');
- PostgreSQL には、日付と時刻を処理するための他にも多くの関数があります。これらの関数の詳細については、PostgreSQL ドキュメントを参照してください。
-- サンプルテーブルの作成
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
timestamp TIMESTAMP NOT NULL
);
-- サンプルデータの挿入
INSERT INTO my_table (timestamp) VALUES
('2024-06-28 23:01:23'),
('2024-06-28 22:59:59'),
('2024-06-28 22:59:00');
-- `date_part` 関数を使用して時間を抽出
SELECT id, date_part('hour', timestamp) AS hour FROM my_table;
-- `to_char` 関数を使用して時間を抽出
SELECT id, to_char(timestamp, 'HH24') AS hour FROM my_table;
-- `EXTRACT` 関数を使用して時間を抽出
SELECT id, EXTRACT(hour FROM timestamp) AS hour FROM my_table;
my_tableという名前の新しいテーブルを作成します。このテーブルには、idという名前のシリアル主キー列と、timestampという名前の TIMESTAMP 列があります。- 3 つのサンプルデータ行を
my_tableテーブルに挿入します。 date_part関数を使用してmy_tableテーブルから時間を抽出し、結果を新しいhour列に格納します。
このコードを実行すると、次の出力が出力されます。
id | hour
-------+--------
1 | 23
2 | 22
3 | 22
この出力は、my_table テーブルにある各 TIMESTAMP 値の時間が正しく抽出されていることを示しています。
SUBSTRING 関数は、文字列の一部を抽出するために使用されます。TIMESTAMP 型の値を文字列として扱い、SUBSTRING 関数を使用して時間の部分を抽出することができます。この方法は、TIMESTAMP 値の形式がわかっている場合にのみ使用できます。
SELECT SUBSTRING(my_timestamp_column, 12, 2);
このクエリは、my_timestamp_column カラムにある各 TIMESTAMP 値の時間の部分を抽出し、結果を新しい列に格納します。このクエリは、TIMESTAMP 値の形式が 'YYYY-MM-DD HH24:MI:SS' であることを前提としています。
SELECT overlay(my_timestamp_column PLACING 'X' FROM 12 FOR 2);
正規表現を使用して、TIMESTAMP 型の値から時間を抽出することもできます。この方法は、より複雑ですが、TIMESTAMP 値の形式がわからない場合に使用できます。
SELECT regexp_replace(my_timestamp_column, '[^0-9:]+', '', 'g');
注意事項
上記の方法を使用する場合は、TIMESTAMP 値の形式がわかっていることを確認してください。形式がわからない場合は、正規表現を使用する必要があります。
また、これらの方法は、パフォーマンス上のオーバーヘッドが大きくなる可能性があることに注意してください。パフォーマンスが重要な場合は、date_part 関数、to_char 関数、または EXTRACT 関数を使用することをお勧めします。
sql database postgresql
