このトピックでは、日付 /時刻タイプの定義と構文について説明します。
日付 /時刻タイプ
名前 | ストレージサイズ | 説明 | 低い値 | 高い価値 | 解像度 |
timestamp [ (p) ] [タイムゾーンなし] | 8 バイト | 日付と時刻の両方 (タイムゾーンなし) | 4713 BC | 294276 AD | 1 マイクロ秒 |
timestamp [ (p) ] with time zone | 8 バイト | 日付と時刻の両方、タイムゾーン付き | 4713 BC | 294276 AD | 1 マイクロ秒 |
日付 | 4 バイト | 日付 (時刻なし) | 4713 BC | 5874897 AD | 1 日 |
time [ (p) ] [タイムゾーンなし] | 8 バイト | 時刻 (日付なし) | 00:00:00 | 24:00:00 | 1 マイクロ秒 |
time [ (p) ] withタイムゾーン | 12 バイト | 時刻 (日付なし) 、タイムゾーン付き | 00:00:00 + 1559 | 24:00:00-1559 | 1 マイクロ秒 |
2日目のインターバルデー [(p)] | 1バイト | 正確に日から秒までの時間間隔 | -178000000年 | 178000000年 | 1 マイクロ秒 |
インターバル 年から月 | 6バイト | 年から月までの時間間隔 | -178000000 年 | 178000000 年 | 1 マイクロ秒 |
SQL標準では、timestamp
だけを書き込むことがtimestamp without time zone
と同等である必要があります。timestamptz
は、timestamp with time zone
の略語として受け入れられます。これはPostgreSQL拡張です。
time
、timestamp
、およびinterval
は、秒フィールドに保持される分数桁数を指定するオプションの精度値p
を受け入れます。 デフォルトでは、精度に明示的な制限はありません。 p
の許容範囲は0から6です。
interval
タイプには、次のいずれかのフレーズを記述して、格納されたフィールドのセットを制限する追加オプションがあります。
年
月
デイ
時間
分
第2
月への年
DAY TO HOUR
分までの日
2日目
分までの時間
2番目の時間
分から2番目
fields
とp
の両方が指定されている場合、精度は秒にのみ適用されるため、フィールド
にはSECOND
を含める必要があります。
タイムゾーン
のタイプはSQL標準によって定義されていますが、この定義は有用性に疑問をもたらすプロパティを示しています。 ほとんどの場合、date
、time
、timestamp without time zone
、およびtimestamp with time zone
の組み合わせは、任意のアプリケーションで必要とされる完全な範囲の日付 /時刻機能を提供する必要があります。
日付 /時刻入力
日付と時刻の入力は、ISO 8601、SQL互換、traditionalPOSTGRESなど、ほぼすべての妥当な形式で受け入れられます。 一部の形式では、日付入力における日、月、年の順序付けがあいまいであり、これらのフィールドの予想される順序付けを指定するサポートがあります。 DateStyleパラメーターをMDY
に設定して、月-日-年の解釈を選択します。DMY
は日-月-年の解釈を選択します。YMD
は年-月-日の解釈を選択します。
PostgreSQLは、SQL標準が必要とするよりも日付 /時刻入力の処理において柔軟性があります。
日付または時刻のリテラル入力は、テキスト文字列のように一重引用符で囲む必要があることに注意してください。 SQLには次の構文が必要です
type [ (p) ] 'value'
ここで、p
は秒フィールドの分数桁数を示すオプションの精度仕様です。 精度は、time
、timestamp
、およびinterval
タイプに指定でき、0から6の範囲で指定できます。 定数指定で精度が指定されていない場合、デフォルト値はリテラル値の精度になります (ただし、6桁以下) 。
日付
日付入力
例 | 説明 |
1999-01-08 | ISO 8601; 1月8日任意のモード (推奨フォーマット) |
January 8, 1999 |
|
1999年1月8日 | 1月8日 |
1999年1月18日 | 1月18日 |
01/02/03 | 1月2日、 |
1999-Jan-08 | どのモードでも1月8日 |
Jan-08-1999 | どのモードでも1月8日 |
08-1月-1999 | どのモードでも1月8日 |
99-1月-08 | 1月8日 |
08-Jan-99 | 1月8日、 |
Jan-08-99 | 1月8日、 |
19990108 | ISO 8601; 1月8日、任意のモードで1999 |
990108 | ISO 8601; 1月8日、任意のモードで1999 |
1999.008 | 年と年の日 |
J2451187 | ジュリアンの日付 |
紀元前99年1月8日 | 紀元前99年 |
タイムズ
時刻タイプは、time [ () ] without time zone
とtime [ () ] with time zone
です。time
だけはtime without time zone
に相当します。
これらのタイプの有効な入力は、時刻とそれに続くオプションのタイムゾーンで構成されます。 入力にタイムゾーンが指定されている場合、time without time zone
は暗黙的に無視されます。 日付を指定することもできますが、America/New_York
などの夏時間ルールを含むタイムゾーン名を使用する場合を除き、無視されます。 この場合、標準または夏時間が適用されるかどうかを判断するために、日付を指定する必要があります。 適切なタイムゾーンオフセットは、time with time zone
の値に記録され、保存されたまま出力されます。
時間入力
例 | 説明 |
| ISO 8601 |
| ISO 8601 |
| ISO 8601 |
| ISO 8601 |
| 04:05と同じ; AMは値に影響しません |
| 16:05と同じです。入力時間は <= 12でなければなりません |
| ISO 8601 (UTCオフセットとしてのタイムゾーン付き) |
| ISO 8601 (UTCオフセットとしてのタイムゾーン付き) |
| ISO 8601 (UTCオフセットとしてのタイムゾーン付き) |
| ISO 8601 (UTCオフセットとしてのタイムゾーン付き) |
| ISO 8601、UTCオフセットとしての分数時間帯 |
| 秒に指定されたUTCオフセット (ISO 8601では許可されていません) |
| 省略形で指定したタイムゾーン |
| フルネームで指定したタイムゾーン |
タイムゾーン入力
例 | 説明 |
| 略語 (太平洋標準時のために) |
| フルタイムゾーン名 |
| POSIXスタイルのタイムゾーン仕様 |
| PSTのUTCオフセット |
| PSTのUTCオフセット (ISO 8601拡張フォーマット) |
| PSTのUTCオフセット (ISO 8601基本フォーマット) |
| PSTのUTCオフセット (ISO 8601基本フォーマット) |
| UTCのミリタリー略語 |
|
|
タイムスタンプ
タイムスタンプタイプの有効な入力は、日付と時刻の連結、オプションのタイムゾーン、オプションのAD
またはBC
の連結で構成されます。 (あるいは、AD
/BC
は時間帯の前に現れることができるが、これは好ましい順序ではない。) したがって:
1999-01-08 04:05:06
と:
1999-01-08 04:05:06 -8:00
はISO8601標準に従う有効な値です。 さらに、共通のフォーマット:
1月8日04:05:06 1999 PST
のみです。
SQLstandardは、timestamp without time zone
とtimestamp with time zone literals
を、"+" または "-" シンボルの存在と、時刻の後のタイムゾーンオフセットによって区別します。 したがって、標準によると、
タイムスタンプ '2004-10-19 10:23:54 '
はタイムスタンプなしのタイムゾーン
です。
タイムスタンプ '2004-10-19 10:23:54 02 '
はタイムスタンプとタイムゾーン
です。PostgreSQLneverは、そのタイプを決定する前にリテラル文字列の内容を調べます。したがって、上記の両方をタイムスタンプなしタイムゾーン
として扱います。 リテラルがtimestamp with time zone
として扱われるようにするには、正しい明示的な型を指定します。
タイムゾーン付きタイムスタンプ '2004-10-19 10:23:54 02 '
timestamp without time zone
であると判断されたリテラルでは、PostgreSQLはタイムゾーンの表示を黙って無視します。 つまり、結果の値は、入力値の日付 /時刻フィールドから取得され、タイムゾーンに合わせて調整されません。
timestamp with time zone
の場合、内部に格納される値は常にUTC (Universal Coordinated Time、伝統的にグリニッジ標準時 (GMT) として知られています) です。 明示的なタイムゾーンが指定された入力値は、そのタイムゾーンの適切なオフセットを使用してUTCに変換されます。 入力文字列にタイムゾーンが指定されていない場合は、システムのTimeZoneパラメーターで指定されたタイムゾーンと見なされ、timezone
ゾーンのオフセットを使用してUTCに変換されます。
timestamp with time zone
値が出力されると、常にUTCから現在のtimezone
ゾーンに変換され、そのゾーンの現地時間として表示されます。 別のタイムゾーンの時刻を表示するには、timezone
を変更するか、AT time zone
構造を使用します。
timestamp without time zone
とtimestamp with time zone
の間の変換は、通常、timestamp without time zone
値がtimezone
ローカル時間として取得または指定されることを前提としています。 AT time zone
を使用して、別のタイムゾーンを変換に指定できます。
特別な値
PostgreSQLは、便宜上、いくつかの特別な日付 /時刻入力値をサポートします。 infinity
と -infinity
の値はシステム内で特別に表示され、変更されずに表示されます。ただし、その他の値は、読み取り時に通常の日付 /時刻値に変換される単なる表記の省略形です。 (特に、現在
および関連する文字列は、読み取られるとすぐに特定の時間値に変換されます。) SQLコマンドで定数として使用する場合、これらの値はすべて単一引用符で囲む必要があります。
特別な日付 /時刻入力
入力文字列 | 有効な型 | 説明 |
|
| 1970-01-01 00:00:00 + 00 (Unixシステム時間ゼロ) |
|
| 他のすべてのタイムスタンプよりも遅い |
|
| 他のすべてのタイムスタンプよりも前 |
|
| 現在のトランザクションの開始時刻 |
|
| 今日の真夜中 ( |
|
| 明日 |
|
| 昨日の真夜中 ( |
|
| 00:00:00.00 UTC |
次のSQL互換関数を使用して、対応するデータ型の現在の時刻値を取得することもできます。CURRENT_DATE
、CURRENT_TIME
、CURRENT_TIME
、LOCALTIME
、LOCALTIMESTAMP
。 これらはSQL関数であり、データ入力文字列では認識されません。
現在
、今日
、明日
、および昨日
の入力文字列は、対話型SQLコマンドで使用するのに適していますが、コマンドを保存して後で実行するとき (準備済みのステートメント、ビュー、関数定義など) には驚くべき動作があります。 文字列は、それが古くなった後もずっと使用され続ける特定の時間値に変換することができる。 そのようなコンテキストでは、代わりにSQL関数の1つを使用します。 たとえば、CURRENT_DATE + 1
は 'tomorrow'::date
よりも安全です。
日付 /時刻出力
日付 /時刻タイプの出力形式は、ISO 8601、SQL (Ingres) 、従来のPOSTGRES (Unix日付形式) 、またはドイツ語の4つのスタイルのいずれかに設定できます。 デフォルトはtheISOformatです。 (SQL標準は、ISO 8601フォーマットの使用を必要とする。 「SQL」出力形式の名前は、過去の事故です。各出力スタイルの例を次の表に示します。 日付
および時間
タイプの出力は、一般的に、与えられた例による日付または時間部分のみである。 ただし、POSTGRESスタイルはISO形式で日付のみの値を出力します。
日付 /時刻出力スタイル
スタイル仕様 | 説明 | 例 |
| ISO 8601、SQL標準 |
|
| 伝統的なスタイル |
|
| オリジナルスタイル |
|
| 地域スタイル |
|
ISO 8601は、日付と時刻を区切るために大文字のT
を使用することを指定します。 PostgreSQLは入力時にその形式を受け入れますが、出力時には上記のようにT
ではなくスペースを使用します。 これは、読みやすさと、RFC 3339やその他のデータベースシステムとの一貫性のためです。
日付注文規則
| 入力注文 | 出力例 |
|
|
|
|
|
|
|
|
|
_char
へのフォーマット機能は、日付 /時刻出力をより柔軟にフォーマットする方法としても利用できます。
タイムゾーン
タイムゾーンとタイムゾーンの慣習は、地球の幾何学だけでなく、政治的決定の影響を受けます。 1900年代には、世界中のタイムゾーンがある程度標準化されましたが、特に夏時間ルールに関して、恣意的な変更が発生する傾向があります。 PostgreSQLは、広く使用されているIANA (Olson) タイムゾーンデータベースを使用して、タイムゾーンの履歴ルールに関する情報を取得します。 将来の時間については、所与の時間帯についての最新の既知のルールは、はるか未来まで無期限に観察され続けるであろうという仮定がある。
PostgreSQLは、一般的な使用法のSQL標準定義と互換性があるように努めています。 ただし、SQL標準には、日付と時刻のタイプと機能の奇妙な組み合わせがあります。 2つの明らかな問題は次のとおりです。
date
タイプにタイムゾーンを関連付けることはできませんが、time
タイプに関連付けることはできます。 現実世界のタイムゾーンは、日付と時間に関連付けられていない限りほとんど意味がありません。これは、夏時間の境界でオフセットが年を通じて変化する可能性があるためです。デフォルトのタイムゾーンは、UTCからの一定の数値オフセットとして指定されます。 したがって、DST境界を越えて日付 /時刻演算を行う場合、夏時間に適応することは不可能である。
これらの問題に対処するため、タイムゾーンを使用する場合は、日付と時刻の両方を含む日付 /時刻タイプを使用することをお勧めします。 タイプtime withタイムゾーン
の使用は推奨しません (ただし、レガシーアプリケーションおよびSQL標準への準拠のためにPostgreSQLでサポートされています) 。 PostgreSQLは、日付または時刻のみを含む任意のタイプのローカルタイムゾーンを想定しています。
すべてのタイムゾーン対応の日付と時刻は、UTCに内部的に格納されます。 これらは、クライアントに表示される前に、TimeZone設定パラメーターで指定されたゾーンのローカル時間に変換されます。
PostgreSQLallowesタイムゾーンを3つの異なる形式で指定できます。
フルタイムゾーン名 (
America/New_York
など) 。 認識されたタイムゾーン名は、pg_timezone_names
ビューに一覧表示されます。 PostgreSQLはこの目的で広く使用されているIANAタイムゾーンデータを使用するため、同じタイムゾーン名が他のソフトウェアによっても認識されます。タイムゾーンの省略形 (
PST
など) 。 このような仕様は、単にUTCからの特定のオフセットを定義します。これは、夏時間の移行ルールのセットを暗示する可能性のあるフルタイムゾーン名とは対照的です。 認識された略語は、pg_timezone_abbrevs
ビューに一覧表示されます。 設定パラメーターTimeZoneまたはlog_timezoneをタイムゾーンの省略形に設定することはできませんが、日付 /時刻入力値およびATTIME zone
演算子で省略形を使用できます。タイムゾーン名と略語に加えて、PostgreSQLはPOSIXスタイルのタイムゾーン仕様を受け入れます。 このオプションは通常、名前付きタイムゾーンを使用する場合には適していませんが、適切なIANAタイムゾーンエントリがない場合は必要になる場合があります。
要するに、これは略語とフルネームの違いです。略語はUTCからの特定のオフセットを表しますが、フルネームの多くはローカルの夏時間ルールを意味するため、2つの可能なUTCオフセットがあります。 例として、2014-06-04 12:00 America/New_York
は、ニューヨークの正午の現地時間を表します。この特定の日付では、東部夏時間 (UTC-4) でした。 したがって、2014-06-04 12:00 EDT
は同じ時刻を指定します。 ただし、2014-06-04 12:00 EST
は、夏時間が名目上その日に有効であったかどうかに関係なく、東部標準時正午 (UTC-5) を指定しています。
問題を複雑にするために、一部の法域では、同じタイムゾーンの略語を使用して、異なる時間に異なるUTCオフセットを意味しています。たとえば、モスクワでは、MSK
は、ある年にはUTC3、他の年にはUTC4を意味しています。 PostgreSQLは、指定された日付に意味する (または最近意味した) ものに従ってそのような略語を解釈します。ただし、上記のEST
の例と同様に、これは必ずしもその日付の現地市民時間と同じではありません。
すべての場合において、タイムゾーン名と略語はケースセンシティブに認識されます。 (これは、8.2前のPostgreSQLバージョンからの変更であり、一部のコンテキストでは大文字と小文字が区別されますが、他のコンテキストでは区別されませんでした。)
タイムゾーン名も略語もサーバーにハードワイヤードされていません。. ../share/timezone /
と. ../share/timezonesets /
インストールディレクトリの
TimeZone設定パラメーターは、ファイルpostgresql.conf
またはその他の標準的な方法で設定できます。 それを設定するいくつかの特別な方法もあります:
SET TIME ZONE
は、セッションのタイムゾーンを設定します。 これは、SET TIMEZONE TO
の代替スペルであり、SQL仕様と互換性のある構文を使用しています。PGTZ
環境変数は、接続時にサーバーにSET TIME ZONE
コマンドを送信するためにbylibpqclientsで使用されます。
インターバル入力
interval
値は、次の冗長構文を使用して記述できます。
[@] 数量単位 [数量単位...] [方向]
単位
は、マイクロ秒
、ミリ秒
、秒
、分
、時間
、日
、週
、月
、年
、10年
、世紀
、ミレニアム
、またはこれらの単位の略語または複数です。direction
は前
または空にすることができます。 at記号 (
@
) はオプションのノイズです。 異なる単位の量は、適切な符号計算で暗黙的に加算される。前に
すべてのフィールドを無効にします。 この構文は、IntervalStyleがpostgres_verbose
に設定されている場合、間隔出力にも使用されます。
日、時間、分、秒の量は、明示的な単位マーキングなしで指定できます。 例えば、「1 12:59:10」
は、「1日12時間59分10秒」
と同じように読まれる。 また、年と月の組み合わせをダッシュで指定することもできます。たとえば、「200-10」
は「200年10月」
と同じように読み取られます。 (これらの短い形式は、実際にはSQLstandardで許可されている唯一の形式であり、IntervalStyle
がsql_standard
に設定されている場合に出力に使用されます。)
間隔値は、標準のセクション4.4.3.2の「指定子付きフォーマット」またはセクション4.4.3.3の「代替フォーマット」のいずれかを使用して、ISO 8601時間間隔として記述することもできます。 指定子付きの形式は次のようになります。
P数量単位 [数量単位...] [T [数量単位...]]
文字列はP
で始まる必要があり、時刻単位を導入するT
を含むことができます。 単位は省略でき、任意の順序で指定できますが、1日未満の単位はT
の後に表示する必要があります。 特に、M
の意味は、それがT
の前か後かに依存する。
ISO 8601インターバル単位の略語
略語 | 意味 |
Y | 年 |
M | 月 (日付部分) |
W | 週 |
D | 日 |
H | 時間 |
M | 分 (時間部分) |
S | 秒 |
代替フォーマットでは:
P [年-月-日] [ T時間: 分: 秒]
文字列はP
で始まる必要があり、T
は間隔の日付と時間の部分を区切ります。 値は、ISO 8601の日付と同様の数字として与えられる。
fields
仕様で間隔定数を記述する場合、またはfields
仕様で定義された間隔列に文字列を割り当てる場合、マークされていない量の解釈はフィールド
に依存します。 例えば、INTERVAL '1' YEAR
は1年と読まれ、INTERVAL '1'
は1秒を意味する。 また、フィールド指定によって許可された最下位フィールドの「右」のフィールド
値は、静かに破棄される。 たとえば、INTERVAL '1day 2:03:04' HOUR TO MINUTE
を記述すると、秒フィールドは削除されますが、日フィールドは削除されません。
SQLstandardによると、間隔値のすべてのフィールドは同じ符号を持つ必要があるため、先行する負の符号がすべてのフィールドに適用されます。たとえば、間隔リテラル '-1 2:03:04'
の負の符号は、日と時間 /分 /秒の両方の部分に適用されます。この例では、時間 /分 /秒の部分は正と見なされます。 IntervalStyle
がsql_standard
に設定されている場合、先頭記号はすべてのフィールドに適用されると見なされます (ただし、追加の記号が表示されない場合のみ) 。 さもなければ、traditionalPostgreSQLinterpretationが使用される。 あいまいさを避けるために、フィールドが負の場合は、各フィールドに明示的な記号を付けることをお勧めします。
フィールド値は、小数部を有することができる: 例えば、「1.5週間」
または「01:02:03.45」
。 ただし、間隔は内部で3つの整数単位 (月、日、マイクロ秒) しか格納しないため、小数単位をより小さな単位にスピルする必要があります。 月を超える単位の部分は、月の整数になるように切り捨てられ、例えば、「1.5年」
は、「1年6月」
になる。 週および日の分数部分は、1ヶ月あたり30日および1日あたり24時間を仮定して、整数の日数およびマイクロ秒であるように計算され、例えば、「1.75ヶ月」
は、1月22日12:00:00
になる。 秒のみが出力に分数として表示されます。
インターバル入力
例 | 説明 |
| SQL標準フォーマット: 1年2か月 |
| SQL標準フォーマット: 3日4時間5分6秒 |
| 従来のPostgresフォーマット: 1年2か月3日4時間5分6秒 |
| ISO 8601「指定子付きフォーマット」: 上記と同じ意味 |
| ISO 8601「代替フォーマット」: 上記と同じ意味 |
内部間隔
の値は、月、日、マイクロ秒として格納されます。 これは、月の日数が変化するために行われ、夏時間の調整が含まれる場合、1日は23時間または25時間になる可能性があります。 月および日フィールドは整数であり、マイクロ秒フィールドは分数秒を格納することができる。 通常、間隔は定数文字列またはタイムスタンプ
の減算から作成されるため、この格納方法はほとんどの場合うまく機能しますが、予期しない結果を引き起こす可能性があります。
SELECT EXTRACT (80分からの時間:: 間隔);
date_part
-----------
1
SELECT EXTRACT (「80時間」からの日数:: 間隔);
date_part
-----------
0
関数justify_days
とjustify_hours
は、通常の範囲をオーバーフローする日と時間を調整するために使用できます。
インターバル出力
interval型の出力形式は、コマンドset intervalstyle
を使用して、4つのスタイルsql_standard
、postgres
、postgres_verbose
、またはiso_8601
のいずれかに設定できます。 デフォルトはpostgres
形式です。
sql_standard
スタイルは、インターバル値が標準の制限 (年月のみまたは日時のみ、正と負のコンポーネントの混合なし) を満たしている場合、SQL標準のインターバルリテラル文字列の仕様に準拠した出力を生成します。 それ以外の場合、出力は、標準的な年-月のリテラル文字列の後に日時のリテラル文字列が続くように見え、混合記号間隔の曖昧さを解消するために明示的な記号が追加されます。
インターバル出力スタイルの例
スタイル仕様 | 年-月間隔 | 日中間隔 | 混合間隔 |
| 1-2 | 3 4:05:06 | -1-2 + 3 -4:05:06 |
| 1年2モン | 3日04:05:06 | -1年-2モンズ + 3日-04:05:06 |
| @ 1年2モン | @ 3日4時間5分6秒 | @ 1年2 mons -3 days 4 hours 5 mins 6 secs前 |
| P1Y2M | P3DT4H5M6S | P-1Y-2M3D T-4H-5M-6S |