PostgresはSQLの日にちと 時間型をすべてサポートしています。
Table 3-6. 日付データ型
| 型 | 説明 | データベース上のバイト数 | 最古値 | 最新値 | 精度 |
|---|---|---|---|---|---|
| timestamp | 年月日時分秒 | 8 バイト | 4713 BC | AD 1465001 | 1マイクロセカンド/14桁 |
| timestamp [ with time zone ] | タイムゾーン付き年月日時分秒 | 8 バイト | 1903 AD | 2037 AD | 1マイクロセカンド/14桁 |
| interval | 日付/時刻の差 | 12 バイト | -178000000 年 | 178000000 年 | 1 マイクロセカンド |
| date | 日付のみ | 4 バイト | 4713 BC | 32767 AD | 1 日 |
| time [ without time zone ] | タイムゾーンなし時刻 | 4 バイト | 00:00:00.00 | 23:59:59.99 | 1 マイクロセカンド |
| time with time zone | タイムゾーン付き時刻 | 4 バイト | 00:00:00.00+12 | 23:59:59.99-12 | 1 マイクロセカンド |
Note: Postgresの前のバージョンとの互換性を確実に保つために、 datetime型(timestampと同じ)とtimespan型 (intervalと同じ)も引き続き提供します。しかし、これらは 現在timestamp型とinterval型に移行しているので、 制限されています。abstime型とreltime型は 精度が低い型で、内部で使用されています。新しいアプリケーションには これらの型の使用は避け、また適当な時に古いものは削除して下さい。 これらの型は今後のリリースで削除される可能性があります。
日付と時間はISO-8601や互換性のあるSQL、 伝来のPostgresなど多くのフォーマットで入力可能です。 日付の月と日にちの順序は決められたものがないので、どのように解釈されるべきなのかを 設定しなければなりません。SET DateStyle TO 'US'や SET DateStyle TO 'NonEuropean'コマンドは様々な "月のあとに日にち形式"を 設定し、またSET DateStyle TO 'European'コマンドは 様々な"日にちのあとに月形式"に設定します。ISO形式がデフォルトと なっていますが、コンパイル時、または実行中に変えることが可能です。
日時の正確な入力方法と使用可能なタイムゾーンについてはAppendix Aを ご覧下さい。
文字列のように、日付や時間はシングルクォーテーションで囲む必要があります。 詳細はSection 1.1.2.5をご覧下さい。 SQLには下記の構文が必要ですが、Postgresは もっと柔軟です。
type 'value'
date 型では下記の入力が可能です。
Table 3-7. 日付入力
| 例 | 説明 |
|---|---|
| January 8, 1999 | 変更不可 |
| 1999-01-08 | ISO-8601のフォーマット。推奨。 |
| 1/8/1999 | US形式。ヨーロッパモードでは8月1日と認識されます。 |
| 8/1/1999 | ヨーロッパ形式。USモードでは8月1日と認識されます。 |
| 1/18/1999 | 米国形式。このケースではどのモードでも1月18日と認識されます。 |
| 19990108 | ISO-8601の年、月、日付 |
| 990108 | ISO-8601の年、月、日付 |
| 1999.008 | 年とその日までの累計 |
| 99008 | 年とその日までの累計 |
| January 8, 99 BC | 西暦紀元の99年前 |
Table 3-8. 月の短縮形式
| 月 | 短縮形式 |
|---|---|
| 4月 | Apr |
| 8月 | Aug |
| 12月 | Dec |
| 2月 | Feb |
| 1月 | Jan |
| 7月 | Jul |
| 6月 | Jun |
| 3月 | Mar |
| 11月 | Nov |
| 10月 | Oct |
| 9月 | Sep, Sept |
Note: 5月には略する必要性がないので、短縮形式がありません。
Table 3-9. 曜日の短縮形式
| 曜日 | 短縮形式 |
|---|---|
| 日曜日 | Sun |
| 月曜日 | Mon |
| 火曜日 | Tue, Tues |
| 水曜日 | Wed, Weds |
| 木曜日 | Thu, Thur, Thurs |
| 金曜日 | Fri |
| 土曜日 | Sat |
SQL99では、この型はtimeとtime without time zoneの 両方を使用できます。
下記がtime型の入力として可能です。
Table 3-10. 時刻入力
| 例 | 説明 |
|---|---|
| 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以下でなければなりません。 |
| z | 00:00:00と同義です。 |
| zulu | 00:00:00と同義です。 |
| allballs | 00:00:00と同義です。 |
この型はSQL92で定義されていますが、その定義は根本的な部分の欠陥があり、 この型をほとんど無益なものとしています。ほとんどの場合、date型、 time型、timestamp型との組み合わせをもって、 あらゆるアプリケーションで必要とされる日付/時刻の機能を提供できるはずです。
time with time zone型ではtime型で使用可能な入力方法は すべて使用でき、適切なタイムゾーンを付けます。
Table 3-11. タイムゾーンつき時刻入力
| 例 | 説明 |
|---|---|
| 04:05:06.789-8 | ISO-8601 |
| 04:05:06-08:00 | ISO-8601 |
| 04:05-08:00 | ISO-8601 |
| 040506-08 | ISO-8601 |
タイムゾーンに関するほかの例はTable 3-12を ご覧下さい。
timestamp型の有効な入力形式は日付と時刻の連結で行われ、 ADやBC、 タイムゾーンがオプションで付けられます。(下記参照)したがって、
1999-01-08 04:05:06 -8:00
は有効なtimestamp型の値で、ISO互換性があります。
また、広く使用されている
January 8 04:05:06 1999 PST
のフォーマットもサポートされています。
interval型は下記の構文で特定することが可能です。
Quantity Unit [Quantity Unit...] [Direction]
@ Quantity Unit [Direction]
Quantity(時間量) は -1,
0, 1, 2, ...;
Unit(単位) は second(秒),
minute(分), hour(時), day(日),
week(週), month(月), year(年),
decade(10年間), century(1世紀),
millennium(千年期)で、これら単位の短縮形や複数形です。
Direction(方向)はago、または空となります。
SQL互換性のあるCURRENT_DATE、 CURRENT_TIME、CURRENT_TIMESTAMP関数は 対応しているデータ型に日付/時間入力として使用することができます。
Postgresは便宜をはかるために特殊な定数が設けてあります。
Table 3-13. 特殊な 日付/時刻 定数
| 定数 | 説明 |
|---|---|
| current | トランザクションが行われた時点(下記参照) |
| epoch | 1970-01-01 00:00:00+00 Unix経時基準時刻 |
| infinity | 有効な時間よりも大きい日付 |
| -infinity | 有効な時間よりも小さい日付 |
| invalid | 無効な入力 |
| now | トランザクションが行われた時点(下記参照) |
| today | 本日 |
| tomorrow | 明日 |
| yesterday | 昨日 |
出力形式フォーマットはSET DateStyleコマンドを使用して ISO-8601、SQL (Ingres)、伝来のPostgres、 Germanの いずれかに設定することができます。デフォルトはISOフォーマット となっています。
Table 3-14. 日付/時間 出力形式
| 出力形式名 | 説明 | 例 |
|---|---|---|
| 'ISO' | ISO-8601形式 | 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 |
date型とtime型の出力形式は言うまでもなく、 上記の例と一致している日付と時間の部分です。
SQL形式にはヨーロッパ式と非ヨーロッパ式(US)があり、 月の後に日にちなのか、それともその逆なのかの判断をします。(この設定がどのように値の 評価に影響するかの詳細は上記の日付/時間入力の説明をご覧下さい。)
Table 3-15. 日付順序の慣習
| 形式名 | 説明 | 例 |
|---|---|---|
| European | 日/月/年 | 17/12/1997 15:37:16.00 MET |
| US | 月/日/年 | 12/17/1997 07:37:16.00 PST |
interval型の出力形式は入力フォーマットと似ていますが、 週や世紀は年と日にちに変換されます。 ISOモードに於て、出力は下記のようになります。
[ Quantity Units [ ... ] ] [ Days ] Hours:Minutes [ ago ]
日付/時間データ型の出力形式撰択としていくつかの方法があります。
postmasterを起動させる際に、バックエンドで使用される環境変数PGDATESTYLEに設定する。
環境変数PGDATESTYLE をフロントエンド libpq 起動時に使用する。
SQLのSET DATESTYLEコマンドを使用する。
Postgresは汎用的に使用できる ようにと、SQL92 への準拠に対して最大限の 努力をしています。しかし、SQL92標準には、 一時凌ぎで混乱している日付/時刻型とその仕様が あります。下記が2つの明らかな問題点です。
date型には関連するタイムゾーンはありませんが、time型には あります。現実の世界において、タイムゾーンはオフセットが夏時間の切り替えにより 一年を通じてが変化することから、時刻と同様に日付もそれに 結び付けられていないと意味があ りません。
デフォルトのタイムゾーンはGMT/UTCからのオフセット整数定数として指定されています。 DST線をこえる日付/時間の計算を行う際にサマータイムを 使用することはできません。
このような問題を解決するためには、タイムゾーンを使用する際、日付と時間の両方を 保持している日付/時間型を使用されることをお勧めします。 他のRDBMSの実装との互換性を持たせるためと、過去のアプリケーションのために PostgresでSQL92のTIME WITH TIME ZONEを サポートしていますが、利用はお勧めしません。 Postgresは日付、または時間だけを保持している あらゆる型の現地時間を推測します。また、タイムゾーンのサポートはOSの タイムゾーンの機能を使用しているので、サマータイムとその他の機能を 利用できます。
Postgresは(UNIX - ライクな システムの典型的な日付の限界である) 1902年から2038年までの 間は、OS環境下のタイムゾーンサポート を受けますが、 この範囲以外のすべての日付は、協定世界時間 (Universal Coordinated Time - (UTC)) が 指定された物と仮定し 使用されます。
内部的にはすべての日付と時刻は、グリニッジ標準時 (GMT) としても 知られてる UTC で保存されています。 時刻はクライアントフロントエンドに 送られる前にデータベース サーバーの上でローカル時刻に変換されるため、 サーバーでの タイムゾーンがデフォルトになります。
タイムゾーンの動作に影響を与える方法はいくつか有ります。
環境変数 TZ を postmaster 起動時に与え、タイムゾーンの デフォルトとする。
コネクション確立時に、環境変数 PGTZ をクライアント側から libpq を使用して バックエンドにタイムゾーンの情報を与える。
SQLのSET TIME ZONEコマンドで セッション中のタイムゾーンを設定する。
SQL92の
timestamp AT TIME ZONE 'zone'で、zone はテキストでタイムゾーンや (例 'PST')インターバル(例 INTERVAL '-08:00')で 指定することができます。
Note: 無効なタイムゾーンが指定された場合、ほとんどのシステムでは タイムゾーンは GMT になります。
Note: コンパイラのオプションに USE_AUSTRALIAN_RULES をセットした 場合、EST というのは、 UTC に対し +10:00 時間の時差を持った オーストラリア東部標準時 (Australia Eastern Std Time) のことです。
Postgresはすべての日付と時刻 の計算に、ユリウス暦を使っています。 これは、紀元前4713年 から未来までの全ての日付を、1年は365.2425日であると 仮定し正確な予測や計算をするという優れた特性を持っています。
19世紀以前の日付規則は面白い読み物にはなりますが、日付/時刻ハンドラを 正しくコーディングするためには十分では ありません。