PostgreSQLで文字列をタイムスタンプに変換する方法

2024-05-20

PostgreSQLで文字列を指定したタイムゾーンのタイムスタンプに変換する方法

基本的な構文

SELECT TO_TIMESTAMP(your_string, format_string);

例

-- 2024年5月19日 15:56:00 JST をタイムスタンプに変換
SELECT TO_TIMESTAMP('2024-05-19 15:56:00', 'YYYY-MM-DD HH24:MI:SS');

フォーマット文字列は、文字列の日付と時刻の各コンポーネントがどのように解釈されるかを指定します。使用できる書式指定子は次のとおりです。

書式指定子	説明	例
`Y`	年 (4 桁)	`2024`
`YY`	年 (2 桁)	`24`
`M`	月 (1 桁または 2 桁)	`5`
`MM`	月 (2 桁)	`05`
`D`	日 (1 桁または 2 桁)	`19`
`DD`	日 (2 桁)	`19`
`HH`	時 (24 時間制、1 桁または 2 桁)	`15`
`MI`	分 (1 桁または 2 桁)	`56`
`SS`	秒 (1 桁または 2 桁)	`00`
`MS`	ミリ秒 (3 桁まで)	`123`
`US`	マイクロ秒 (6 桁まで)	`123456`
`TZ`	タイムゾーン (省略可)	`'Asia/Tokyo'`

タイムゾーンを指定するには、TZ 書式指定子を使用します。タイムゾーンは、IANA タイムゾーンデータベース https://www.iana.org/time-zones で定義されている文字列で指定する必要があります。

-- 2024年5月19日 15:56:00 PDT をタイムスタンプに変換
SELECT TO_TIMESTAMP('2024-05-19 15:56:00', 'YYYY-MM-DD HH24:MI:SS', 'America/Los_Angeles');

注意事項

入力文字列が正しい形式でない場合、エラーが発生します。
タイムゾーンが認識されない場合、エラーが発生します。
出力タイムスタンプは、PostgreSQL サーバーのタイムゾーン設定によって影響を受ける可能性があります。

上記以外にも、PostgreSQL には文字列をタイムスタンプに変換するための様々な方法があります。詳細については、PostgreSQL のドキュメントを参照してください。

PostgreSQLで文字列を指定したタイムゾーンのタイムスタンプに変換するサンプルコード

例1：異なるタイムゾーンの文字列をタイムスタンプに変換

この例では、2つの異なるタイムゾーンの文字列を、それぞれ対応するタイムゾーンのタイムスタンプに変換します。

-- 2024年5月19日 15:56:00 JST を日本標準時 (Asia/Tokyo) のタイムスタンプに変換
SELECT TO_TIMESTAMP('2024-05-19 15:56:00', 'YYYY-MM-DD HH24:MI:SS', 'Asia/Tokyo') AS japan_timestamp;

-- 2024年5月19日 8:56:00 PDT を太平洋夏時間 (America/Los_Angeles) のタイムスタンプに変換
SELECT TO_TIMESTAMP('2024-05-19 08:56:00', 'YYYY-MM-DD HH24:MI:SS', 'America/Los_Angeles') AS pacific_timestamp;

例2：フォーマット文字列を指定して文字列をタイムスタンプに変換

この例では、フォーマット文字列を使用して、文字列の日付と時刻の各コンポーネントをどのように解釈するかを指定します。

-- 2024年5月19日 15:56:00 を省略形の日付形式で日本標準時 (Asia/Tokyo) のタイムスタンプに変換
SELECT TO_TIMESTAMP('2024-05-19 15:56', 'YYYY-MM-DD HH:MI', 'Asia/Tokyo') AS japan_timestamp;

この例では、ミリ秒とマイクロ秒を含む文字列をタイムスタンプに変換します。

-- 2024年5月19日 15:56:00.123456 を日本標準時 (Asia/Tokyo) のタイムスタンプに変換
SELECT TO_TIMESTAMP('2024-05-19 15:56:00.123456', 'YYYY-MM-DD HH24:MI:SS.MS', 'Asia/Tokyo') AS japan_timestamp;

これらの例は、PostgreSQL で文字列を指定したタイムゾーンのタイムスタンプに変換する方法を理解するための出発点となるものです。具体的なニーズに合わせてコードを調整してください。

AT TIME ZONE 句は、TO_TIMESTAMP関数と同様に、文字列をタイムスタンプに変換するために使用できます。ただし、AT TIME ZONE 句は、文字列の日付と時刻の形式を自動的に検出する利点があります。

-- 2024年5月19日 15:56:00 JST を日本標準時 (Asia/Tokyo) のタイムスタンプに変換
SELECT '2024-05-19 15:56:00'::timestamp AT TIME ZONE 'Asia/Tokyo' AS japan_timestamp;

利点:

文字列の日付と時刻の形式を自動的に検出します。
TO_TIMESTAMP関数よりも簡潔な構文です。

TO_TIMESTAMP関数ほど多くのフォーマット文字列をサポートしていません。
タイムゾーンの指定が必須です。

CAST関数は、さまざまなデータ型間で値を変換するために使用できます。文字列をタイムスタンプに変換する場合、CAST関数と共にAT TIME ZONE 句を使用する必要があります。

-- 2024年5月19日 15:56:00 JST を日本標準時 (Asia/Tokyo) のタイムスタンプに変換
SELECT CAST('2024-05-19 15:56:00' AS timestamp) AT TIME ZONE 'Asia/Tokyo' AS japan_timestamp;

さまざまなデータ型間で値を変換するために使用できる汎用的な関数です。

構文が少し冗長です。

EXTRACT関数とMAKE_TIMESTAMP関数を組み合わせて、文字列をタイムスタンプに変換することもできます。この方法は、より複雑ですが、よりきめ細かな制御を提供します。

-- 2024年5月19日 15:56:00 JST を日本標準時 (Asia/Tokyo) のタイムスタンプに変換
SELECT MAKE_TIMESTAMP(
  EXTRACT(year FROM '2024-05-19'),
  EXTRACT(month FROM '2024-05-19'),
  EXTRACT(day FROM '2024-05-19'),
  EXTRACT(hour FROM '2024-05-19'),
  EXTRACT(minute FROM '2024-05-19'),
  EXTRACT(second FROM '2024-05-19')
) AT TIME ZONE 'Asia/Tokyo' AS japan_timestamp;