PostgreSQLでタイムスタンプを操作する方法は？

PostgreSQLでタイムスタンプを操作するトピックは、インターネット上のロシア語専門の出版物ではあまり開示されておらず、プログラマーの仕事における頻繁な問題の原因です。 人気のある外国のブログdepesz.comの著者であるHubert Lubaczewskiの資料の翻訳に注目します。 この記事がお役に立てば幸いです！



時々、IRCまたはメーリングリストの誰かが、特にタイムゾーンを考慮したタイムスタンプの深い誤解（または理解不足）を示す質問をします。 既にこれに出くわしたので、タイムスタンプとは何か、それらをどのように使用するか、そして遭遇する可能性のある最も一般的な障害は何かを教えてください。

使用できる2種類のデータがあります。

• タイムスタンプ
• タイムゾーン付きタイムスタンプ（またはtimestamptz）

タイムスタンプタイプには日付と時刻のみが含まれ、他の情報は含まれません。 一方で、これは悪いことではないように見えるかもしれません（結局、日常生活では日付と時刻のみを使用しますか？）、しかし一方で、それは全くの恐怖です。

タイムスタンプが「2014-04-04 20:00:00」であると想像してください。残念ながら、それほど多くはありません。 4月は、ロサンゼルス、シカゴ、ロンドン、ワルシャワ、またはモスクワの異なる時点であり、これはタイムゾーンの問題です。

もちろん、あなたは次のように思うかもしれません。「私は常に同じタイムゾーンにいます。異なるタイムゾーンのサポートに苦労する必要はありません。 私のタイムゾーンでは、特定の時点をマークするのに日付と時刻で十分です。これは、まさに「実際の生活」で行うことだからです。

しかし、本当にそうですか？

「2013-10-27 02:00:00」というラベルがあり、アプリケーションがポーランド時間に関連していることがわかっているとします。 この場合、午前2時の中央ヨーロッパ夏時間（CEST）、または通常の中央ヨーロッパ時間よりも1時間長くなる可能性があるため、幸運ではなくなります。 季節ごとの時間の変化が原因です。

タイムゾーンなしでタイムスタンプを使用することはほとんどの場合バグであり、修正する必要があると思います。 アプリケーションのエントリが異なるタイムゾーン（スケジューラアプリケーションなど）からのものである場合は、さらに多くの問題があります。

そのため、最も明らかな解決策は、タイムゾーン（タイムスタンプ）でタイムスタンプを使用することです。

まず、それはより多くのディスクスペースを占有しません：

$ select typname, typlen from pg_type where typname ~ '^timestamp'; typname | typlen -------------+-------- timestamp | 8 timestamptz | 8 (2 rows) 

これはどのように機能しますか？ ラベルはタイムゾーンを認識している必要がありますが、なぜこれがより多くのスペースを必要としないのですか？

事実は、彼女はタイムゾーンを知らないということです。 内部では、timestamptz列のすべての値はUTC（協定世界時）です。

UTCには優れた機能があります。オフセットはなく（それ自体が他のタイムゾーンのオフセットが考慮される開始点です）、夏時間と冬時間の間に違いはありません。 したがって、UTCタイムスタンプは常に1つの時点のみを指すことが保証されています。

しかし、常にUTCで示す場合、必要なタイムゾーンの時間をどのように見つけるのですか？

timestamptz値になるたびに、タイムゾーンが指定されていない場合、PostgreSQLは事前に設定された時間を使用します。 また、さまざまな方法で構成できます。

• postgresql.confのtimezoneパラメーター
• データベースを変更します...タイムゾーンを設定します= '...'
• ユーザーの変更... set timezone = '...'
• SETタイムゾーン= '...'

最初のパラメーターは、サーバーが配置されているタイムゾーンを示すために使用されます。 つまり、他の変更がない場合に使用されるデフォルトのタイムゾーン。

次の2つの方法は、選択したデータベースとユーザーのデフォルト値を変更します。

後者の方法は、データベース接続をさまざまな設定で機能させる場合に使用できます。

now（）の出力がどのように変化するかを考慮してください：

 $ select now(); now ------------------------------- 2014-04-04 20:32:59.390583+02 (1 row) $ set timezone = 'America/New_York'; SET $ select now(); now ------------------------------- 2014-04-04 14:33:06.442768-04 (1 row) 

したがって、timestamptz値を表示または変更するたびに、PostgreSQLはそれらをUTCから/に変換します。

これは、値を簡単に比較できることを意味します（これらはすべて同じタイムゾーンにあり、夏時間または冬時間のシフトがないため、常に比較が可能です）。

そして、値が「2014-04-04 20:00:00」であると想像してみましょう。 また、今回はロサンゼルスにいることを知っていますが、別のタイムゾーンで動作するデータベースに保存する必要があります。 現在のオフセットが-7時間であることを確認し、この値を次のように使用できます。

 $ select '2014-04-04 20:00:00-07'::timestamptz; timestamptz ------------------------ 2014-04-05 05:00:00+02 

どうした 午後8時に表示されないのはなぜですか？

理由は簡単です-あるタイムゾーンでリクエストにタイムスタンプを挿入しました。 内部では、ラベルはUTCに変換され、その後、通常のタイムゾーンに再変換されます（おそらく、UTCがなくてもわかりません）。

 $ show timezone; TimeZone ---------- Poland (1 row) 

ロサンゼルスのタイムゾーンを設定した場合、クエリ結果は次のようになります。

 $ set timezone = 'America/Los_Angeles'; SET $ select '2014-04-04 20:00:00-07'::timestamptz; timestamptz ------------------------ 2014-04-04 20:00:00-07 (1 row) 

表示される値は常にタイムゾーン設定を考慮することを理解することが重要です。

ロサンゼルスで20:00を取得する別の方法があります。

 $ set timezone = 'Poland'; SET $ select '2014-04-04 20:00:00'::timestamp at time zone 'America/Los_Angeles'; timezone ------------------------ 2014-04-05 05:00:00+02 (1 row) 

値の後に「::タイムスタンプ」を追加することは非常に重要です。それ以外の場合は、奇妙なものになります。

 $ set timezone = 'Poland'; SET $ select '2014-04-04 20:00:00' at time zone 'America/Los_Angeles'; timezone --------------------- 2014-04-04 11:00:00 (1 row) 

ここで何が起こったのですか？ 11:00はどこから来たのですか？

引用符で囲まれた値（2014-04-04 20:00:00）はtimestamptzとして認識されます。これは、私のタイムゾーンで午後8時を意味します。

 select '2014-04-04 20:00:00'::timestamptz; timestamptz ------------------------ 2014-04-04 20:00:00+02 (1 row) 

そして、値が私のタイムゾーンに転送された後にのみ、PGは「at time zone ...」を読み取ります。これは、選択したタイムゾーンの時刻を表示するために使用されます。

したがって、タイムゾーンのタイムスタンプは値timestamptzを提供します。これは、選択されたタイムゾーンの現地時間がコマンドで指定された時刻を示します。

タイムゾーンのtimestamptzはタイムスタンプ値を提供し、指定された時間に選択されたタイムゾーンの時間を表示します。

これは少しわかりにくいので、例を挙げましょう。

 select '2014-04-04 20:00:00'::timestamptz at time zone 'UTC'; timezone --------------------- 2014-04-04 18:00:00 (1 row) select '2014-04-04 20:00:00'::timestamp at time zone 'UTC'; timezone ------------------------ 2014-04-04 22:00:00+02 (1 row) 

興味深いことに、Pgがタイムゾーンに含まれていなくても、これを使用して、あるタイムゾーンから別のタイムゾーンに時間を変換できます。

ロサンゼルスの何時、モスクワの朝8時かを知りたいとします。 私の現地時間は次のとおりです。

 $ show timezone; TimeZone ---------- Poland (1 row) 

それによるメリットはほとんどありません。

まず、モスクワの午前8時を示す特定の時点（timestamptz形式）を特定する必要があります。

 $ select '2014-04-04 08:00:00'::timestamp at time zone 'Europe/Moscow'; timezone ------------------------ 2014-04-04 06:00:00+02 (1 row) 

これは、タイムゾーンの午前6に対応することを示しています。 しかし、ロサンゼルスの時間を知りたいです。 「LA」のタイムゾーンで「2014-04-04 06：00 + 02」と書くこともできますが、別の方法で行うことができます。

 $ select ('2014-04-04 08:00:00'::timestamp at time zone 'Europe/Moscow') at time zone 'America/Los_Angeles'; timezone --------------------- 2014-04-03 21:00:00 (1 row) 

式 'timestamp at time zone ..'はtimestamptzと同じであるため、「at time zone」を再び使用して、タイムゾーンを指定せずにタイムスタンプに変換し直します。別の場所に。

これですべてが明らかになることを願っています。 私自身はかなり長い間この問題を整理しようとしてきましたが、最終的にすべてを理解しました:)

これには興味深い副作用が1つあります。timestamptzで機能する関数にインデックスを追加するのはそれほど簡単ではありません。 たとえば、曜日を取得するために使用されるインデックスを作成することはできません。

 $ create table test (i timestamptz); CREATE TABLE $ create index q on test (to_char(i, 'Day')); ERROR: functions in index expression must be marked IMMUTABLE 

上記の例に示すように、理由は非常に単純です。同じ時点は、タイムゾーンに応じて異なる曜日を参照できます。 また、to_char（）は現在のタイムゾーンを使用するため、システムのタイムゾーン設定に応じて、同じソースデータに対して異なる値を生成できます。

 $ set timezone = 'Europe/Warsaw'; SET $ insert into test (i) values ('2014-04-04 06:00:00'); INSERT 0 1 $ select i, to_char(i, 'Day') from test; i | to_char ------------------------+----------- 2014-04-04 06:00:00+02 | Friday (1 row) $ set timezone = 'Europe/Moscow'; SET $ select i, to_char(i, 'Day') from test; i | to_char ------------------------+----------- 2014-04-04 08:00:00+04 | Friday (1 row) $ set timezone = 'America/Los_Angeles'; SET $ select i, to_char(i, 'Day') from test; i | to_char ------------------------+----------- 2014-04-03 21:00:00-07 | Thursday (1 row) 

まったく同じ時点だが、異なる日。 場所に応じて、異なる月または異なる年になります。

ここのタイムスタンプ（タイムゾーンなし）は強い側面を「示しています」-タイムゾーンを指定していないため、情報の抽出に安全に使用できます。

しかし、timestamptzをtimestampに変換する方法は知っています。 あなたは彼女にタイムゾーンを伝える必要があります。 したがって、これを行うことができます。

 create index q on test (to_char(i at time zone 'Poland', 'Day')); 

しかし、残念ながら、それは何も生じません。 実際のところ、to_charは多用途です。 to_charは次のように使用できます。

 $ select to_char(now(), 'TMMonth'); to_char --------- April (1 row) $ set lc_time = 'pl_PL.UTF-8'; SET $ select to_char(now(), 'TMMonth'); to_char ---------- Kwiecień (1 row) 

今回は、タイムゾーンのためではなく、ロケールのために他の結果が得られます。

インデックス作成の問題に対する正しい解決策は、絶対に一定の「環境」でto_charを呼び出して、すでにインデックスを作成する独自の関数を作成することです。 このように：

 create function day_from_ts(timestamptz) returns text as $$ select to_char( $1 at time zone 'Poland', 'Day' ); $$ language sql immutable; CREATE FUNCTION 

これで、インデックス作成に使用できます。

 create index q on test (day_from_ts( i )); CREATE INDEX 

これは、関数自体がタイムゾーンを「ポーランド」に設定し、ロケール値を無視するようにto_charを呼び出す（つまり、to_char形式にはTMプレフィックスがない）ため、安全です。

当然、このインデックスを使用するには、すべてのクエリで次の関数も使用する必要があります。

 select * from test where day_from_ts(i) = 'Friday'; 

タイムゾーンで作業する場合のもう1つの重要なことは、Unixから時間を取得すること、またはいわゆる時代です。 一般的に、それは簡単です：

 $ select extract(epoch from now()); date_part ------------------ 1396638868.57491 (1 row) 

興味深いことに、タイムゾーンに依存しません。

 $ begin; BEGIN $ show timezone; TimeZone ---------- Poland (1 row) $ select now(), extract(epoch from now()); now | date_part -------------------------------+------------------ 2014-04-04 21:15:27.834775+02 | 1396638927.83477 (1 row) $ set timezone = 'America/Los_Angeles'; SET $ select now(), extract(epoch from now()); now | date_part -------------------------------+------------------ 2014-04-04 12:15:27.834775-07 | 1396638927.83477 (1 row) $ commit; COMMIT 

誰もが知っているわけではないが、その理由は、Unix時間は常にUTCタイムゾーンで受け入れられるからです。 これは、タイムスタンプからエポックを取得するときに、PGはUTCであると想定することを意味します。 次の潜在的な問題が発生する原因：

 $ select now(), extract(epoch from now()); now | date_part -------------------------------+----------------- 2014-04-04 21:19:01.456205+02 | 1396639141.4562 (1 row) $ select extract(epoch from '2014-04-04 21:19:01.456205'::timestamp); date_part ----------------- 1396646341.4562 (1 row) 

最初のケースでは、Pgは「ポイントインタイム」を受け取ります。これは内部的にUTCに変換されます（表示されると、私のタイムゾーン+2に変換されます）。

2番目の場合、タイムスタンプは私のタイムゾーンにありますが、UTC（変換なし！）であると想定されます。また、 '2014-04-04ではなく、' 2014-04-04 21：19：01.456205 UTC 'の値から時代が取得されます。 21：19：01.456205 + 02 '。

ムドレーノ。

要するに、タイムスタンプを避け、timestamptzを使用するようにしてください。

最後に言いたいのは、バグや潜在的な問題ではなく、多くの人が気付いていない機能です。

おわかりのように、PostgreSQLはマイクロ秒単位の正確なタイムスタンプ（およびtimestamptz）を使用します。 私は個人的にはそれが好きではありませんが、多くの人は精度が最大1秒に過ぎないと主張しています。

タイムスタンプとtimestamptz（および時間に関連する他のタイプのデータ）の両方に、追加の精度（「精度」）を設定できます。

簡単な例を挙げましょう。

 $ select now(), now()::timestamptz(0), now()::timestamptz(1); now | now | now -------------------------------+------------------------+-------------------------- 2014-04-04 21:23:42.322315+02 | 2014-04-04 21:23:42+02 | 2014-04-04 21:23:42.3+02 (1 row) 

もちろん、テーブルでもこれを使用できます。

 $ create table test (i timestamptz(0)); CREATE TABLE $ insert into test(i) values (now()); INSERT 0 1 $ select * from test; i ------------------------ 2014-04-04 21:24:16+02 (1 row) 

いいね！ 「now（）」などを変更する必要はありません。データ型に精度を追加するだけで、すべてが調整されます。

私はそれが好きではないと言った。 その理由は簡単です-十分にロードされたシステムでは、1秒は精度が低すぎます。 さらに、マイクロ秒単位の正確なデータストレージには何もかかりませんが、便利です。 一方、データがマイクロ秒までの場合、値が瞬時に表示されるようにするにはどうすればよいですか？

簡単です：（SELECTクエリで）関数to_char（）またはdate_truncを使用するか、timestamptz（0）型にキャストします：

 $ select now(), to_char(now(), 'YYYY-MM-DD HH24:MI:SS TZ'), date_trunc('second', now()), now()::timestamptz(0); now | to_char | date_trunc | now -------------------------------+--------------------------+------------------------+------------------------ 2014-04-04 21:28:20.827763+02 | 2014-04-04 21:28:20 CEST | 2014-04-04 21:28:20+02 | 2014-04-04 21:28:21+02 (1 row) 

2016年7月のPG Day'16 Russiaカンファレンスで、タイムスタンプの操作方法について詳しくお話しします！ 質問を準備してください、私たちはそれらに答えようとします。

Mail.ru Groupの同僚が書いた有用な記事もおそらく見つかるでしょう。 timestamptzだけではタスクを解決するには不十分な例を明確に示しています。