PostgreSQLでは、表8-9に示されているSQLの日付と時刻データ型の全てがサポートされています。 これらのデータ型で利用できる演算子については項9.9で説明します。
表 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μ秒、14桁 |
| date | 4バイト | 日付のみ | 4713 BC | 5874897 AD | 1日 |
| time [ (p) ] [ without time zone ] | 8バイト | その日の時刻のみ | 00:00:00 | 24:00:00 | 1μ秒、14桁 |
| time [ (p) ] with time zone | 12バイト | その日の時刻のみ、時間帯付き | 00:00:00+1459 | 24:00:00-1459 | 1μ秒、14桁 |
注意: 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およびinterval値を浮動小数点数として格納するか8バイト整数として格納するかも決まります。 浮動小数点数の場合、大きなintervalでは間隔が増加する際に精度が落ちます。
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.5を参照してください。 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への入力に時間帯が指定された場合、時間帯は警告なく無視されます。 また、America/New_Yorkなど夏時間規則を含む時間帯名を使用していない限り、日付を指定することはできますが、これは無視されます。 この場合、標準か夏時間かどちらを適用するかを決定できるように、日付の指定が必要です。 適切な時間帯オフセットはtime with time zone型の値に記録されています。
表 8-11. 時刻入力
| 例 | 説明 |
|---|---|
| 04:05:06.789 | ISO 8601 |
| 04:05:06 | ISO 8601 |
| 04:05 | ISO 8601 |
| 040506 | ISO 8601 |
| 04:05 AM | 04:05と同じ。AMは値に影響しません。 |
| 04:05 PM | 16:05と同じ。時の入力は12以下でなければなりません。 |
| 04:05:06.789-8 | ISO 8601 |
| 04:05:06-08:00 | ISO 8601 |
| 04:05-08:00 | ISO 8601 |
| 040506-08 | ISO 8601 |
| 04:05:06 PST | 省略形による時間帯の指定。 |
| 2003-04-12 04:05:06 America/New_York | 名前による時間帯の指定。 |
表 8-12. 時間帯入力
| 例 | 説明 |
|---|---|
| PST | 省略形（米国太平洋標準時間） |
| America/New_York | 完全な時間帯名 |
| PST8PDT | |
| -8:00 | ISO 8601。PST用のオフセット |
| -800 | ISO 8601。PST用のオフセット |
| -8 | ISO 8601。PST用のオフセット |
| zulu | UTC用の軍事用略記 |
| z | zuluの略記 |
時間帯の指定方法に関する詳細は 項8.5.3を参照してください。
タイムスタンプ型への有効な入力は、日付と時刻をセットにしたもの、さらにその後にオプションで時間帯、その後にオプションでADもしくはBCからなります （他に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
という書式もサポートされます。
標準SQLでは、timestamp without time zoneのリテラルとtimestamp with time zoneのリテラルを"+"もしくは"-"の有無で区別しています。 そのため、標準に従うと、
TIMESTAMP '2004-10-19 10:23:54'
はtimestamp without time zoneに、
TIMESTAMP '2004-10-19 10:23:54+02'
はtimestamp with time zoneになります。 PostgreSQLでは、timestamp with time zoneのリテラルには明示的な型付け、例えば、
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
が必要です。 timestamp with time zoneの存在により明示的に示されていないリテラルでは、PostgreSQLはリテラル内に指定された時間帯情報を全て警告なく無視します。 つまり、結果の日付/時刻の値は明示された入力値の日付/時刻フィールドから持ち込まれますが、時間帯の調整はなされません。
timestamp with time zoneについて内部に格納されている値は常にUTCです（協定世界時、歴史的にグリニッジ標準時GMTとして知られています）。 時間帯が明示的に指定された入力値は、その時間帯に適したオフセットを使用してUTCに変換されます。 入力文字列に時間帯が指定されていない場合は、システムのtimezoneパラメータに示されている値が時間帯とみなされ、timezone時間帯用のオフセットを使用してUTCに変換されます。
timestamp with time zone値が出力されると、この値はUTCから現行のtimezoneに変換され、その時間帯のローカル時間として表示されます。 他の時間帯での時間を表示するには、timezoneを変更するか、あるいはAT TIME ZONE構文を使用します（項9.9.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（単位）はmicrosecond（マイクロ秒）、millisecond（ミリ秒）、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までの範囲で、デフォルトは入力リテラルの精度です。
内部的には、intervalの値は月数、日数および秒数として格納されています。ある月の日数は月によって変わりますし、夏時間が導入されていると1日が23時間であったり25時間であったりするからです。時間間隔は通常定数列、もしくはtimestampからの引き算で生成されますので、この様に保存すればほとんどの場合うまく行きます。justify_daysとjustify_hours関数で通常の範囲を越えた日にちと時刻を調整できます。
PostgreSQLでは利便性のために、表8-13に示されているような特別な日付/時刻入力値をサポートしています。 infinityと-infinityの値は、特別にシステム内部で表現され、同じように表示されます。 他のものは、単に簡略化された表記で、読み込まれる際には通常の日付/時刻値に変換されます （特にnowとその関連文字列は読み込まれるとすぐにその時点の値に変換されます）。 これらの値は全て、SQLコマンドで定数として使う場合は、単一引用符で括らなければなりません。
表 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 |
SQL互換の関数である、CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMPも、対応するデータ型の日付または時間の値として使用できます。 後の4つでは、オプションとして秒以下の精度指定が可能です （項9.9.4 を参照してください）。 しかし、これらはSQL関数であり、データ入力文字列としては認識されないことに注意してください。
日付/時刻型の出力書式は、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:seconds ]
ユーザはSET DATESTYLEコマンド、postgresql.conf構成ファイルのDateStyleパラメータ、そしてサーバかクライアントのPGDATESTYLE環境変数を使用して、日付/時刻の様式を選択することができます。
日付/時刻出力のより柔軟な書式設定方法として、書式設定関数to_char（項9.8を参照）を使用することもできます。
時間帯および時間帯の取り決めは地球の幾何学的要素のみでなく政治的判断に影響されます。 世界にまたがる時間帯は1900年代に標準化されたようですが、特に夏時間規則の点で、勝手に変更する傾向が続いています。 PostgreSQLは現在、（伝統的なUnixシステム時間の範囲に一致する）1902年から2038年までの期間について夏時間規則をサポートします。 範囲外の日付/時刻は選択された時間帯の"標準時間"で表されたものとみなします。 つまり、何年になったら失敗するということを心配する必要はありません。
PostgreSQLは汎用的に使用できるように標準SQLへの互換性に対し最大限の努力をしています。 しかし、標準SQLには、日付と時刻のデータ型と機能に関する混乱が見受けられます。 2つの明らかな問題点を以下に示します。
date型にはそれに関連する時間帯がありませんが、time型にはあります。 現実の世界において、時間帯のオフセットが夏時間への切り替えにより年間を通じて変化することから、時刻と同様に日付もそれに結び付けられていないと意味がありません。
デフォルトの時間帯はUTCからの整数定数オフセットとして指定されています。 したがってDST（夏時間）への切り替えをまたいで日付/時刻演算を行う場合、夏時間を適用することはできません。
このような問題を解決するためには、時間帯を使用する際に日付と時刻の両方を保持できる日付/時刻データ型を使用することを勧めます。 time with time zone型の使用はお勧めしません （もっともPostgreSQLでは、旧式のアプリケーションや、標準SQLとの互換性のために、time with time zone型の使用をサポートしています）。 PostgreSQLは、日付または時刻のみを保持するデータ型の全ては使用中の時間帯であると前提しています。
全ての時間帯付きの日付と時刻はUTCで内部的に保存されます。 これらはクライアントに表示される前にtimezone設定パラメータで指定された時間帯におけるローカル時間に変換されます。
PostgreSQLでは、3つの形式で時間帯を指定することができます。
America/New_Yorkなどの完全な時間帯名称。 認識できる時間帯名称はpg_timezone_namesビューに列挙されています（項43.49を参照してください）。 PostgreSQLはこの目的のためによく使用されているzic時間帯データを使用します。 したがって、多くの他のソフトウェアでも同じ名前が認識されます。
PSTなどの時間帯省略形。 こうした指定は、単に特定のUTCからのオフセットを定義します。 一方完全な時間帯名称では夏時間遷移規則群も組み込むことができます。 認識可能な省略形はpg_timezone_abbrevsビューに列挙されています（項43.48を参照してください）。 時間帯省略形を使用してtimezone設定パラメータを設定することはできませんが、日付時刻型の入力値やAT TIME ZONE演算子に省略形を使用することができます。
時間帯名やその省略形に加え、PostgreSQLは、STDoffsetやSTDoffsetDSTという形式のPOSIX様式の時間帯指定を受付けます。 ここで、STDは時間帯省略形、offsetはUTCから西に数えた時間単位のオフセットです。 また、DSTは省略可能で夏時間時間帯の省略形で、指定オフセットから1時間進むことを意味します。 たとえば、EST5EDTが認識済みの時間帯名でない場合でも、受付けられ、USA西海岸時間と同じものとして動作します。 夏時間時間帯名があると、zic時間帯データベースのposixrules項目で使用される夏時間変換規則と同じ規則に従って使用されるものと前提されます。 標準的なPostgreSQLインストレーションでは、posixrulesはUS/Easternと同じです。 このためPOSIX書式の時間帯指定はUSA夏時間規則に従います。 必要に応じてposixrulesファイルを置き換えることで、この動作を調整することができます。
省略形と省略なしの名称の間には概念的および実質的な差異があります。省略形はUTCから固定したオフセットを表わすのに対して、ほとんどの省略なしの名称はローカルの夏時間規定を意味するので、UTCオフセットには２つの可能性があります。
時間帯省略系の正当性を検査しないため、POSIX書式時間帯機能ではおかしな入力を警告なく受け付けてしまう可能性があることに注意すべきです。 例えば、システムの動作はより独特なUTCの省略系を使用し続けた状態で、SET TIMEZONE TO FOOBAR0でも動作します。
全ての場合において、時間帯名は大文字小文字の区別なく認識されます。（これはPostgreSQLのバージョン8.2以前からの変更で、全てではありませんが、ある文脈では大文字小文字が区別されました。）
省略なし、省略形のどちらもサーバ内に組み込まれるわけではありません。インストールディレクトリの、.../share/timezone/ および .../share/timezonesets/の下に保存される構成ファイルから取得されます（項B.3を参照ください）。
timezoneはpostgresql.confファイルや第17章で説明する他の標準的な方法で設定することができます。 以下に、いくつか特別な設定方法を示します。
postgresql.conf内やサーバのコマンドラインスイッチでtimezoneが指定されていない場合、デフォルトの時間帯としてTZ環境変数の値を使用しようと試みます。 TZが定義されていない場合やTZがPostgreSQLで認識可能な時間帯名称でなかった場合、サーバは、localtime()Cライブラリ関数の動作を検査することでオペレーティングシステムのデフォルトの時間帯を決定しようと試みます。 このデフォルトの時間帯は、PostgreSQLが認識可能な時間帯の中から最も近いものが選択されます。
SET TIME ZONE SQLコマンドはセッションの時間帯を設定します。 これはSQL仕様互換の文法により従っているSET TIMEZONE TOの別名です。
クライアントによってPGTZ環境変数が設定されている場合、接続確立時にサーバに対しSET TIME ZONEコマンドをlibpqアプリケーションが送信します。
PostgreSQLは全ての日付と時刻の計算にユリウス日を使っています。 これは、紀元前4,713年から未来までの全ての日付を、1年は365.2425日であると仮定し正確な予測や計算をするという優れた特性を持っています。
19世紀以前の日付規則はおもしろい読み物にはなりますが、日付/時刻ハンドラの正しいコーディングを保証することは整合性について完璧ではありません。