timestamp.1e5b.de PostgreSQLから期待される時間が取得されません：
これは正しいタイムスタンプを返します：
SELECT extract(epoch from now());
そうではありませんが：
SELECT extract(epoch from now() at time zone "utc");
私はタイムゾーンUTC + 02に住んでいます。 PostgreSQLから現在のUNIXタイムスタンプを取得する正しい方法は何ですか？
これは正しい時間とタイムゾーンを返します：
SELECT now(); now ------------------------------- 2011-05-18 10:34:10.820464+02
別の比較：
select now(), extract(epoch from now()), extract(epoch from now() at time zone "utc"); now | date_part | date_part -------------------------------+------------------+------------------ 2011-05-18 10:38:16.439332+02 | 1305707896.43933 | 1305700696.43933 (1 row) Unix timestamp from the web sites: 1305707967
回答
postgresでは、timestamp with time zoneはtimestamptzと省略でき、timestamp without time zoneはtimestampと省略できます。簡単にするために短いタイプ名を使用します。
now()のようなpostgres timestamptzからUnixタイムスタンプを取得するのはあなたが言うように、単純です：
select extract(epoch from now());
、now()を含む。
timestampフィールドがある場合にのみ複雑になります。 。
now()のようなtimestamptzデータをそのフィールドに入力すると、最初に特定のタイムゾーンに変換されます（at time zoneを使用して明示的に、またはセッションのタイムゾーンに変換することにより）、タイムゾーン情報は破棄されます。絶対時間を参照しなくなりました。これが理由です。通常、タイムスタンプをtimestampとして保存する必要はなく、通常はtimestamptzを使用します—映画は特定の午後6時にリリースされる可能性がありますすべてのタイムゾーンでの日付、それは一種のユースケースです。
1つのタイムゾーンでしか作業しない場合は、
。 timestamptzへの変換は、DSTに対処するのに十分賢く、変換の目的で、タイムスタンプは現在のタイムゾーンにあると想定されます。 GMT / BSTの例を次に示します。
select "2011-03-27 00:59:00.0+00"::timestamptz::timestamp::timestamptz , "2011-03-27 01:00:00.0+00"::timestamptz::timestamp::timestamptz; /* |timestamptz |timestamptz | |:---------------------|:---------------------| |2011-03-27 00:59:00+00|2011-03-27 02:00:00+01| */
DBFiddle
ただし、次の紛らわしい動作に注意してください。
set timezone to 0; values(1, "1970-01-01 00:00:00+00"::timestamp::timestamptz) , (2, "1970-01-01 00:00:00+02"::timestamp::timestamptz); /* |column1|column2 | |------:|:---------------------| | 1|1970-01-01 00:00:00+00| | 2|1970-01-01 00:00:00+00| */
DBFiddle
このは：
PostgreSQLは、リテラル文字列のタイプを判別する前にその内容を調べることはないため、両方の[…]をタイムゾーンのないタイムスタンプとして扱います。リテラルがタイムゾーン付きのタイムスタンプとして扱われるようにし、正しい明示的なタイプを指定します…タイムゾーンなしのタイムスタンプであると判断されたリテラルでは、PostgreSQLはタイムゾーンの表示を黙って無視します
コメント
回答
SELECT extract(epoch from now() at time zone "utc");
postgresタイムゾーン変換によってタイムゾーン情報が結果から破棄されるため、正しいタイムスタンプが返されません：
9.9.3。ATTIMEZONE
構文：タイムゾーンなしのタイムスタンプAT TIME ZONEゾーン
戻り値：タイムゾーン付きのタイムスタンプ
指定されたタイムゾーンにあるタイムゾーンなしのタイムスタンプを扱います
構文：タイムゾーンのあるタイムスタンプAT TIME ZONE zone
戻り値：タイムゾーンのないタイムスタンプ
タイムゾーンの指定なしで、タイムゾーンのある指定されたタイムスタンプを新しいタイムゾーンに変換します
その後、extractはタイムゾーンのないタイムスタンプを調べ、それを現地時間と見なします（実際にはすでにutcですが）。
正しい方法
select now(), extract(epoch from now()), -- correct extract(epoch from now() at time zone "utc"), -- incorrect extract(epoch from now() at time zone "utc" at time zone "utc"); -- correct now | date_part | date_part | date_part -------------------------------+------------------+------------------+------------------ 2014-10-14 10:19:23.726908+02 | 1413274763.72691 | 1413267563.72691 | 1413274763.72691 (1 row)
最後の行で、最初のat time zoneが変換を実行します。 2つ目は、結果に新しいタイムゾーンを割り当てます。