8.5. 日付/時刻データ型

PostgreSQLでは、表8-9に示されているSQLの日付と時刻データ型の全てがサポートされています。 これらのデータ型で利用できる演算子については項9.9で説明します。

表 8-9. 日付/時刻データ型

型名格納サイズ説明最遠の過去最遠の未来精度
timestamp [ (p) ] [ without time zone ]8 バイト日付と時刻両方4713 BC5874897 AD1μ秒、14桁
timestamp [ (p) ] with time zone8バイト日付と時刻両方、時間帯付き4713 BC5874897 AD1μ秒、14桁
interval [ (p) ]12バイト時間間隔-178000000年178000000年1μ秒、14桁
date4バイト日付のみ4713 BC5874897 AD1日
time [ (p) ] [ without time zone ]8バイトその日の時刻のみ00:00:0024:00:001μ秒、14桁
time [ (p) ] with time zone12バイトその日の時刻のみ、時間帯付き00:00:00+145924:00:00-14591μ秒、14桁

注意: PostgreSQL 7.3より以前では、単なるtimestamptimestamp with time zoneと同じでした。 これは、SQL互換性のために変更されました。

timetimestamp、および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で定義されていますが、その定義は、その有用性を疑問視することになりかねない特性を示しています。 ほとんどの場合、datetimetimestamp without time zonetimestamp with time zoneの組み合わせで、全てのアプリケーションで要求される日付/時刻機能全てを提供しているはずです。

abstimereltimeは精度の低いデータ型で、内部で使用されています。 新しいアプリケーションにはこれらの型の使用を避け、また適当な時に古いアプリケーションも更新してください。 これらの内部用の型の一部または全てが今後のリリースでは削除される可能性があります。

8.5.1. 日付/時刻の入力

日付と時刻の入力は、ISO 8601、SQL互換、伝統的なPOSTGRES、その他を含むほとんどの適正とみなされる書式を受け付けます。 日付の入力における月と日の順序のようないずれとも解釈されるいくつかの書式については、それらのフィールドを好きな順序に指定できるようになっています。 DateStyleパラメータをMDYに設定すれば、月日年という順で解釈され、DMYに設定すれば日月年という順で、YMDに設定すれば年月日という順で解釈されます。

PostgreSQLは日付/時刻の運用において標準SQLの要求よりも柔軟です。 日付/時刻の入力における正確な構文解析規則と、月および週、そして時間帯を含む使用可能なテキストフィールドに関しては付録Bを参照してください。

テキスト文字列のように、日付や時刻リテラルは単一引用符で囲む必要があることを思い出してください。 詳細は項4.1.2.5を参照してください。 SQLでは下記の構文が必要です。

type [ (p) ] 'value'

ここで、オプションの精度の指定pは秒フィールドの小数点以下の桁数に対応する整数です。 精度はtimetimestamp、およびinterval型に対して設定できます。 値の許容範囲は既に説明しています。 定数指定において精度指定がない場合は、デフォルトのリテラル値の精度になります。

8.5.1.1. 日付

表8-10date型で入力可能なものの一部を示します。

表 8-10. 日付入力

説明
January 8, 1999全てのdatestyle入力モードにおいて曖昧さがありません。
1999-01-08ISO 8601。全てのモードで1月8日になります(推奨書式)。
1/8/1999MDYモードでは1月8日、DMYモードでは8月1日。
1/18/1999MDYモードでは1月18日、他のモードでは拒絶されます。
01/02/03MDYモードでは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-08YMDモードで1月8日、他のモードではエラー。
08-Jan-991月8日。ただしYMDモードではエラー。
Jan-08-991月8日。ただしYMDモードではエラー。
19990108ISO 8601。全てのモードで1月8日になります。
990108ISO 8601。全てのモードで1月8日になります。
1999.008年とその日までの累計
J2451187ユリウス日
January 8, 99 BC西暦紀元前99年

8.5.1.2. 時刻

ある一日の時刻を表す型はtime [(p) ] without time zonetime [ (p) ] with time zoneです。 単なるtimetime without time zoneと同じです。

これらの型への有効な入力は、時刻、その後にオプションで時間帯からなります (表8-11表8-12を参照してください)。 time without time zoneへの入力に時間帯が指定された場合、時間帯は警告なく無視されます。 また、America/New_Yorkなど夏時間規則を含む時間帯名を使用していない限り、日付を指定することはできますが、これは無視されます。 この場合、標準か夏時間かどちらを適用するかを決定できるように、日付の指定が必要です。 適切な時間帯オフセットはtime with time zone型の値に記録されています。

表 8-11. 時刻入力

説明
04:05:06.789ISO 8601
04:05:06ISO 8601
04:05ISO 8601
040506ISO 8601
04:05 AM04:05と同じ。AMは値に影響しません。
04:05 PM16:05と同じ。時の入力は12以下でなければなりません。
04:05:06.789-8ISO 8601
04:05:06-08:00ISO 8601
04:05-08:00ISO 8601
040506-08ISO 8601
04:05:06 PST省略形による時間帯の指定。
2003-04-12 04:05:06 America/New_York名前による時間帯の指定。

表 8-12. 時間帯入力

説明
PST省略形(米国太平洋標準時間)
America/New_York完全な時間帯名
PST8PDT 
-8:00ISO 8601。PST用のオフセット
-800ISO 8601。PST用のオフセット
-8ISO 8601。PST用のオフセット
zuluUTC用の軍事用略記
zzuluの略記

時間帯の指定方法に関する詳細は 項8.5.3を参照してください。

8.5.1.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 zonetimestamp with time zone間の変換では、通常timestamp without time zone値はtimezoneローカル時間としてみなされる、または、指定されるものと想定されます。 AT TIME ZONEを使用する変換では、異なる時間帯参照を指定できます。

8.5.1.4. 時間間隔

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_daysjustify_hours関数で通常の範囲を越えた日にちと時刻を調整できます。

8.5.1.5. 特殊な値

PostgreSQLでは利便性のために、表8-13に示されているような特別な日付/時刻入力値をサポートしています。 infinity-infinityの値は、特別にシステム内部で表現され、同じように表示されます。 他のものは、単に簡略化された表記で、読み込まれる際には通常の日付/時刻値に変換されます (特にnowとその関連文字列は読み込まれるとすぐにその時点の値に変換されます)。 これらの値は全て、SQLコマンドで定数として使う場合は、単一引用符で括らなければなりません。

表 8-13. 特殊な日付/時刻定数

入力文字列有効な型説明
epochdate, timestamp1970-01-01 00:00:00+00(Unixシステム時間におけるゼロ)
infinitytimestamp他の全てのタイムスタンプより遅い
-infinitytimestamp他の全てのタイムスタンプより早い
nowdate, time, timestamp現トランザクションの開始時刻
todaydate, timestamp今日の始まり
tomorrowdate, timestamp明日の始まり
yesterdaydate, timestamp昨日の始まり
allballstime00:00:00.00 UTC

SQL互換の関数である、CURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPLOCALTIMELOCALTIMESTAMPも、対応するデータ型の日付または時間の値として使用できます。 後の4つでは、オプションとして秒以下の精度指定が可能です (項9.9.4 を参照してください)。 しかし、これらはSQL関数であり、データ入力文字列としては認識されないことに注意してください。

8.5.2. 日付/時刻出力

日付/時刻型の出力書式は、SET datestyleコマンドを使用して、ISO 8601、SQL(Ingres)、伝統的なPOSTGRES、Germanのいずれかに設定することができます。 デフォルトはISO書式です(標準SQLではISO 8601書式の使用が定められています。 "SQL"出力書式名は偶然のものです)。 表8-14に各出力書式の例を示します。 datetimeの書式は、言うまでもなく示された例のそれぞれ日付と時刻の部分となります。

表 8-14. 日付/時刻出力形式

様式仕様説明
ISOISO 8601/標準SQL1997-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, DMYday(日)/month(月)/year(年)17/12/1997 15:37:16.00 CET
SQL, MDYmonth(月)/day(日)/year(年)12/17/1997 07:37:16.00 PST
Postgres, DMYday(日)/month(月)/year(年)Wed 17 Dec 07:37:16 1997 PST

intervalの出力はcenturyweekといった単位が年と日に変換される点とagoが適切な符号に変換される点を除き、入力書式と同じように表現されます。 ISOモードでの出力は以下のようになります。

[quantity unit [ ... ] ] [ days ] [ hours:minutes:seconds ]

ユーザはSET DATESTYLEコマンド、postgresql.conf構成ファイルのDateStyleパラメータ、そしてサーバかクライアントのPGDATESTYLE環境変数を使用して、日付/時刻の様式を選択することができます。 日付/時刻出力のより柔軟な書式設定方法として、書式設定関数to_char項9.8を参照)を使用することもできます。

8.5.3. 時間帯

時間帯および時間帯の取り決めは地球の幾何学的要素のみでなく政治的判断に影響されます。 世界にまたがる時間帯は1900年代に標準化されたようですが、特に夏時間規則の点で、勝手に変更する傾向が続いています。 PostgreSQLは現在、(伝統的なUnixシステム時間の範囲に一致する)1902年から2038年までの期間について夏時間規則をサポートします。 範囲外の日付/時刻は選択された時間帯の"標準時間"で表されたものとみなします。 つまり、何年になったら失敗するということを心配する必要はありません。

PostgreSQLは汎用的に使用できるように標準SQLへの互換性に対し最大限の努力をしています。 しかし、標準SQLには、日付と時刻のデータ型と機能に関する混乱が見受けられます。 2つの明らかな問題点を以下に示します。

このような問題を解決するためには、時間帯を使用する際に日付と時刻の両方を保持できる日付/時刻データ型を使用することを勧めます。 time with time zone型の使用はお勧めしません (もっともPostgreSQLでは、旧式のアプリケーションや、標準SQLとの互換性のために、time with time zone型の使用をサポートしています)。 PostgreSQLは、日付または時刻のみを保持するデータ型の全ては使用中の時間帯であると前提しています。

全ての時間帯付きの日付と時刻はUTCで内部的に保存されます。 これらはクライアントに表示される前にtimezone設定パラメータで指定された時間帯におけるローカル時間に変換されます。

PostgreSQLでは、3つの形式で時間帯を指定することができます。

省略形と省略なしの名称の間には概念的および実質的な差異があります。省略形はUTCから固定したオフセットを表わすのに対して、ほとんどの省略なしの名称はローカルの夏時間規定を意味するので、UTCオフセットには2つの可能性があります。

時間帯省略系の正当性を検査しないため、POSIX書式時間帯機能ではおかしな入力を警告なく受け付けてしまう可能性があることに注意すべきです。 例えば、システムの動作はより独特なUTCの省略系を使用し続けた状態で、SET TIMEZONE TO FOOBAR0でも動作します。

全ての場合において、時間帯名は大文字小文字の区別なく認識されます。(これはPostgreSQLのバージョン8.2以前からの変更で、全てではありませんが、ある文脈では大文字小文字が区別されました。)

省略なし、省略形のどちらもサーバ内に組み込まれるわけではありません。インストールディレクトリの、.../share/timezone/ および .../share/timezonesets/の下に保存される構成ファイルから取得されます(項B.3を参照ください)。

timezonepostgresql.confファイルや第17章で説明する他の標準的な方法で設定することができます。 以下に、いくつか特別な設定方法を示します。

8.5.4. 内部

PostgreSQLは全ての日付と時刻の計算にユリウス日を使っています。 これは、紀元前4,713年から未来までの全ての日付を、1年は365.2425日であると仮定し正確な予測や計算をするという優れた特性を持っています。

19世紀以前の日付規則はおもしろい読み物にはなりますが、日付/時刻ハンドラの正しいコーディングを保証することは整合性について完璧ではありません。