PostgreSQLでは、表8-9に示されているSQLの日付と時刻データ型のすべてがサポートされています。
表 8-9. 日付/時刻データ型
| 型名 | 格納サイズ | 説明 | 最遠の過去 | 最遠の未来 | 精度 |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8 バイト | 日付と時刻両方 | 4713 BC | 5874897 AD | 1 μ秒 / 14 桁 |
| timestamp [ (p) ] with time zone | 8 バイト | 日付と時刻両方 | 4713 BC | 5874897 AD | 1 μ秒 / 14 桁 |
| interval [ (p) ] | 12 バイト | 時間間隔 | -178000000 年 | 178000000 年 | 1 μ秒 |
| date | 4 バイト | 日付のみ | 4713 BC | 32767 AD | 1 日 |
| time [ (p) ] [ without time zone ] | 8 バイト | その日の時刻のみ | 00:00:00.00 | 23:59:59.99 | 1 μ秒 |
| time [ (p) ] with time zone | 12 バイト | その日の時刻のみ、時間帯付き | 00:00:00.00+12 | 23:59:59.99-12 | 1 μ秒 |
注意: PostgreSQL 7.3より以前では、単なるtimestampはtimestamp with time zoneと同じでした。 これは、SQL互換性のために変更されました。
time、timestamp、および interval は秒フィールドに保有されている小数点以下の桁数を指定するオプションの精度値であるpを受け付けます。 デフォルトでは、明示的な精度に対する限界はありません。 pの許容範囲は、timestamp型とinterval型の場合は0から6です。
注意: timestampの値が倍精度浮動小数点数として保存されている場合(現在のデフォルトです)、有効な精度は 6 よりも小さいかもしれません。 timestampの値は2000-01-01深夜を基準にした経過秒数として格納されます。 マイクロセカンドの精度は、2000-01-01から2、3年以内の日付では達成されていますが、その精度は日付が(2000-01-01から)離れるにつれて悪化します。 timestampの値が8バイト整数(コンパイル時にオプション指定)として格納される時にはマイクロセカンドの精度はすべての範囲の値に関して有効です。 しかし、8バイト整数のタイムスタンプでは、その日付範囲が上述の制限よりも制限され、4713 BCから294276 ADまでとなってしまいます。
time型では8バイト整数が使用された場合にはpは0から6までの範囲で使用可能です。 また、浮動小数点が使用される場合には0から10までが使用可能です。
time with time zoneは標準SQLで定義されていますが、その定義は、その有用性を疑問視することになりかねない特性を示しています。 ほとんどの場合、date、time、timestamp without time zone、timestamp with time zoneの組合せで、すべてのアプリケーションで要求される日付/時刻機能すべてを提供しているはずです。
abstimeとreltimeは精度の低いデータ型で、内部で使用されています。 新しいアプリケーションにはこれらの型の使用を避け、また適当な時に古いアプリケーションも更新してください。 これらの内部用の型の一部またはすべてが今後のリリースでは削除される可能性があります。
日付と時刻の入力は、ISO 8601、SQL互換、伝統的なPOSTGRES、その他を含むほとんどの適正と見なされる書式を受け付けます。 日付の入力における月と日の順序のようないずれとも解釈されるいくつかの書式については、それらフィールドを好きな順序に指定できるようになっています。 datestyleパラメータをMDYに設定すれば、月日年という順で解釈され、DMYに設定すれば日月年という順に、YMDに設定すれば年月日という順で解釈されます。
PostgreSQLは日付/時刻の運用において標準SQLの要求よりも柔軟です。 日付/時刻の入力における正確な構文解析規則と、月および週、そして時間帯を含む使用可能なテキストフィールドに関しては付録Bを参照してください。
テキスト文字列のように、日付や時刻リテラルは単一引用符で囲む必要があることを思い出して下さい。 詳細は項4.1.2.4を参照してください。 SQLでは下記の構文が必要です。
type [ (p) ] 'value'
ここで、オプションの精度の指定pは秒フィールドの小数点以下の桁数に対応する整数です。 精度はtime、timestamp、およびinterval型に対して設定できます。 値の許容範囲は既に説明しています。 定数指定において精度指定がない場合は、デフォルトのリテラル値の精度になります。
表8-10はdate型で入力可能なものの一部を示します。
表 8-10. 日付入力
| 例 | 説明 |
|---|---|
| January 8, 1999 | すべてのdatestyle入力モードにおいて曖昧さがありません。 |
| 1999-01-08 | ISO-8601。すべてのモードで1月8日になります。(推奨書式) |
| 1/8/1999 | MDYモードでは1月8日、DMYモードでは8月1日。 |
| 1/18/1999 | MDYモードでは1月18日、他のモードでは拒絶されます。 |
| 01/02/03 | MDYモードでは2003年1月2日、DMYモードでは2003年2月1日、YMDモードでは2001年2月3日。 |
| 1999-Jan-08 | すべてのモードで1月8日になります。 |
| Jan-08-1999 | すべてのモードで1月8日になります。 |
| 08-Jan-1999 | すべてのモードで1月8日になります。 |
| 99-Jan-08 | YMDモードで1月8日、他のモードではエラー。 |
| 08-Jan-99 | 1月8日。ただしYMDモードではエラー。 |
| Jan-08-99 | 1月8日。ただしYMDモードではエラー。 |
| 19990108 | ISO-8601。すべてのモードで1月8日になります。 |
| 990108 | ISO-8601。すべてのモードで1月8日になります。 |
| 1999.008 | 年とその日までの累計 |
| J2451187 | ユリウス日 |
| January 8, 99 BC | 西暦紀元前99年 |
ある一日の時刻を表す型はtime [(p) ] without time zoneとtime [ (p) ] with time zoneです。 単なるtimeはtime without time zoneと同じです。
これらの型への有効な入力は、時刻、その後にオプションで時間帯からなります。 (表8-11と表8-12を参照してください。) time without time zoneへの入力に時間帯が指定された場合、時間帯は警告なく無視されます。
タイムスタンプ型への有効な入力は、日付と時刻をくっつけたもの、その後にオプションでADもしくはBC、更にその後にオプションでタイムゾーン、からなります。 従って、
1999-01-08 04:05:06
と
1999-01-08 04:05:06 -8:00
は有効な値で、ISO 8601に準拠しています。 また、広く使用されている
January 8 04:05:06 1999 PST
という書式もサポートされます。
timestamp [without time zone] に対して明示的に指定された時間帯はすべて警告なく無視されます。 ということは、結果の日付/時刻の値は明示された入力値の日付/時刻フィールドから持ち込まれ、時間帯に調整されていません。
timestamp with time zoneについて内部に格納されている値は常にUTC(協定世界時、歴史的にグリニッジ標準時GMTとして知られています)です。 時間帯が明示的に指定された入力値は、その時間帯に適したオフセットを使用してUTCに変換されます。 入力文字列に時間帯が指定されていない場合は、システムのtimezoneパラメータに示されている値が時間帯とみなされ、timezone時間帯用のオフセットを使用してUTCに変換されます。
timestamp with time zone値が出力されると、この値はUTCから現行のtimezoneに変換され、その時間帯のローカル時間として表示されます。 他の時間帯での時間を表示するには、timezoneを変更するか、あるいはAT TIME ZONE構文を使用します(項9.8.3 を参照)。
timestamp without time zoneとtimestamp with time zone間の変換では、通常timestamp without time zone値はtimezoneローカル時間としてみなされる、または、指定されるものと想定されます。 AT TIME ZONEを使用する変換では、異なる時間帯参照を指定できます。
intervalの値は以下の構文で書くことができます。
[@] quantity unit [quantity unit...] [direction]
ここで、quantityは（符号付き）時間量、unit(単位)はsecond(秒)、minute(分)、hour(時)、day(日)、week(週)、month(月)、year(年)、decade(10年単位)、century(100年単位)、millennium(1000年単位)あるいはこれらの単位の簡略形または複数形です。 direction（方向）はagoもしくは空です。 アットマーク（@）はオプションで付けても付けなくても構いません。 異なる単位における時間量は適切に符号を考慮して暗黙的に足し込まれます。
日、時、分、および秒の時間量は明示的に単位を指名しないでも構いません。 例えば、'1 12:59:10'は'1日と12時間59分10秒' と解釈されます。
オプションの精度pは0から6までの範囲で、デフォルトは入力リテラルの精度です。
SQL互換の関数、CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMPを、対応するデータ型の日付または時間の値として使用できます。 後の4つでは、オプションとして精度指定が可能です。 （項9.8.4 も参照してください。）
PostgreSQLでは利便性のために、表8-13に示されているような特別な日付/時刻入力値もサポートしています。 infinityと-infinityの値は、特別にシステム内部で表現され、同じように表示されます。 他のものは、単に簡略化された表記で、読み込まれる際には通常の日付/時刻値に変換されます。 これらの値はすべて、通常の定数として扱われ、また、単一引用符で括らなければなりません。
表 8-13. 特殊な日付/時刻定数
| 入力文字列 | 有効な型 | 説明 |
|---|---|---|
| epoch | date, timestamp | 1970-01-01 00:00:00+00 (Unixシステム時間におけるゼロ) |
| infinity | timestamp | 他のすべてのタイムスタンプより遅い |
| -infinity | timestamp | 他のすべてのタイムスタンプより早い |
| now | date, time, timestamp | 現トランザクションの開始時刻 |
| today | date, timestamp | 今日の始まり |
| tomorrow | date, timestamp | 明日の始まり |
| yesterday | date, timestamp | 昨日の始まり |
| allballs | time | 00:00:00.00 UTC |
日付/時刻型の出力書式は、SET datestyleコマンドを使用して、ISO 8601、SQL（Ingres）、伝統的なPOSTGRES、Germanのいずれかに設定することができます。 デフォルトはISO書式です。(標準SQLではISO 8601書式の使用が定められています。 "SQL"出力書式名は偶然のものです。) 表8-14に各出力書式の例を示します。 dateとtimeの書式はいうまでもなく、示された例のそれぞれ日付と時刻の部分となります。
表 8-14. 日付/時刻出力形式
| 様式仕様 | 説明 | 例 |
|---|---|---|
| ISO | ISO 8601/標準SQL | 1997-12-17 07:37:16-08 |
| SQL | 伝統的な様式 | 12/17/1997 07:37:16.00 PST |
| POSTGRES | 特有の様式 | Wed Dec 17 07:37:16 1997 PST |
| German | 地域限定様式 | 17.12.1997 07:37:16.00 PST |
SQLとPOSTGRESでは、DMYフィールド順が指定された場合は月の前に日が現れます。 指定がなければ日の前に月が現れます。 (この設定が入力値の解釈にどう影響を与えるのかについては項8.5.1を参考にしてください。) 表8-15に例を示します。
表 8-15. 日付の順序の慣習
| datestyleの設定 | 入力の順序 | 出力例 |
|---|---|---|
| SQL, DMY | day(日)/month(月)/year(年) | 17/12/1997 15:37:16.00 CET |
| SQL, MDY | month(月)/day(日)/year(年) | 12/17/1997 07:37:16.00 PST |
| Postgres, DMY | day(日)/month(月)/year(年) | Wed 17 Dec 07:37:16 1997 PST |
intervalの出力はcenturyやweekといった単位が年と日に変換される点agoが適切な符号に変換される点を除き、入力書式と同じように表現されます。 ISOモードでの出力は以下のようになります。
[ quantity unit [ ... ] ] [ days ] [ hours:minutes:sekunden ]
ユーザはSET DATESTYLEコマンド、postgresql.conf構成ファイルのdatestyleパラメータ、そしてサーバかクライアントのPGDATESTYLE環境変数を使用して、日付/時刻の様式を選択することができます。 日付/時刻出力のより柔軟な書式設定方法として、書式設定関数to_char(項9.7を参照)を使用することもできます。
時間帯および時間帯の取り決めは地球の幾何学的要素のみでなく政治的判断に影響されます。 世界にまたがる時間帯は1900年代に標準化されたようですが勝手に変更する傾向が続いています。 PostgreSQLは時間帯による出力のサポートに使用しているオペレーティングシステムの機能を使いますが、これらのシステムは通常（伝統的なUnixシステム時間の範囲に一致する）1902年から2038年までの情報しか所有していません。 timestamp with time zoneとtime with time zoneはこの年範囲のみ時間帯情報を使用し、範囲外の日付/時刻はUTCであると仮定します。 しかし、時間帯のサポートはオペレーティングシステムの時間帯機能から生成されますので、夏時間や他の特殊な振舞いを扱うことができます。
PostgreSQLは汎用的に使用できるように標準SQLへの互換性に対し最大限の努力をしています。 しかし、標準SQLには、日付と時刻のデータ型と機能に関する混乱が見受けられます。 2つの明らかな問題点を以下に示します。
date型にはそれに関連する時間帯がありませんが、time型にはあります。 現実の世界において、時間帯のオフセットが夏時間への切り替えにより年間を通じて変化することから、時刻と同様に日付もそれに結び付けられていないと意味がありません。
デフォルトの時間帯はUTCからの整数定数オフセットとして指定されています。 DST(夏時間)への切替えを跨いで日付/時刻演算を行う場合、夏時間を適用することはできません。
このような問題を解決するためには、時間帯を使用する際に日付と時刻の両方を保持できる日付/時刻データ型を使用することを勧めます。 time with time zone型の使用はお勧めしません (もっともPostgreSQLでは、旧式のアプリケーションや、他のSQL実装との互換性のために、time with time zone型の使用をサポートしています)。 PostgreSQLは、日付または時刻のみを保持するデータ型のすべては使用中の時間帯であると前提しています。
すべての日付と時刻はUTCで内部的に保存されます。 時刻はクライアントに送られる前にデータベースサーバのローカル時間に変換されるため、サーバでの時間帯がデフォルトになります。
サーバで使用する時間帯を選択する方法はいくつかあります。
値が指定されていない場合には、サーバホストのTZ環境変数がサーバのデフォルトの時間帯として使用されます。
postgresql.confファイル内にtimezone構成パラメータを設定できます。
クライアントによってPGTZ環境変数が設定されている場合、接続確立時にサーバに対しSET TIME ZONEコマンドをlibpqアプリケーションが送信します。
SET TIME ZONE SQLコマンドによってセッションで用いる時間帯を設定します。
注意: 無効な時間帯が指定された場合（ほとんどのシステムで）時間帯はUTCになります。
使用可能な時間帯については、付録Bを参照してください。