8.5. 日付/時刻データ型

PostgreSQLでは、表8-9に示されているSQLの日付と時刻データ型のすべてがサポートされています。

表 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 μ秒
date4 バイト日付のみ4713 BC32767 AD1 日
time [ (p) ] [ without time zone ]8 バイトその日の時刻のみ00:00:00.0023:59:59.991 μ秒
time [ (p) ] with time zone12 バイトその日の時刻のみ、時間帯付き00:00:00.00+1223:59:59.99-121 μ秒

注意: 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型では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.4を参照してください。 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への入力に時間帯が指定された場合、時間帯は警告なく無視されます。

表 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名前による時間帯の指定。

表 8-12. 時間帯入力

説明
PST米国太平洋標準時間
-8:00ISO-8601。PST用のオフセット
-800ISO-8601。PST用のオフセット
-8ISO-8601。PST用のオフセット
zuluUTC用の軍事用略記
zzuluの略記

8.5.1.3. タイムスタンプ

タイムスタンプ型への有効な入力は、日付と時刻をくっつけたもの、その後にオプションで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 zonetimestamp with time zone間の変換では、通常timestamp without time zone値はtimezoneローカル時間としてみなされる、または、指定されるものと想定されます。 AT TIME ZONEを使用する変換では、異なる時間帯参照を指定できます。

8.5.1.4. 時間間隔

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までの範囲で、デフォルトは入力リテラルの精度です。

8.5.1.5. 特殊な値

SQL互換の関数、CURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPLOCALTIMELOCALTIMESTAMPを、対応するデータ型の日付または時間の値として使用できます。 後の4つでは、オプションとして精度指定が可能です。 (項9.8.4 も参照してください。)

PostgreSQLでは利便性のために、表8-13に示されているような特別な日付/時刻入力値もサポートしています。 infinity-infinityの値は、特別にシステム内部で表現され、同じように表示されます。 他のものは、単に簡略化された表記で、読み込まれる際には通常の日付/時刻値に変換されます。 これらの値はすべて、通常の定数として扱われ、また、単一引用符で括らなければなりません。

表 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

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:sekunden ]

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

8.5.3. 時間帯

時間帯および時間帯の取り決めは地球の幾何学的要素のみでなく政治的判断に影響されます。 世界にまたがる時間帯は1900年代に標準化されたようですが勝手に変更する傾向が続いています。 PostgreSQLは時間帯による出力のサポートに使用しているオペレーティングシステムの機能を使いますが、これらのシステムは通常(伝統的なUnixシステム時間の範囲に一致する)1902年から2038年までの情報しか所有していません。 timestamp with time zonetime with time zoneはこの年範囲のみ時間帯情報を使用し、範囲外の日付/時刻はUTCであると仮定します。 しかし、時間帯のサポートはオペレーティングシステムの時間帯機能から生成されますので、夏時間や他の特殊な振舞いを扱うことができます。

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

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

すべての日付と時刻はUTCで内部的に保存されます。 時刻はクライアントに送られる前にデータベースサーバのローカル時間に変換されるため、サーバでの時間帯がデフォルトになります。

サーバで使用する時間帯を選択する方法はいくつかあります。

注意: 無効な時間帯が指定された場合(ほとんどのシステムで)時間帯はUTCになります。

使用可能な時間帯については、付録Bを参照してください。

8.5.4. 内部

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

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