| PostgreSQL 7.4 文檔 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 8. 數據類型 | Fast Forward | Next |
PostgreSQL 支持 SQL 中所有的日期和時間類型。 在 Table 8-9 中顯示。
Table 8-9. 日期/時間類型
| 名字 | 存儲空間 | 描述 | 最低值 | 最高值 | 分辨率 |
|---|---|---|---|---|---|
| timestamp [ (p) ] [without time zone] | 8 字節 | 包括日期和時間 | 4713 BC | AD 5874897 | 1 毫秒 / 14 位 |
| timestamp [ (p) ] with time zone | 8 字節 | 日期和時間，帶時區 | 4713 BC | AD 5874897 | 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 毫秒 |
time ，timestamp 和interval 接受一個可選的 精度值 p，這個精度值聲明在秒域後面 小數點之後保留的位數．缺省的時候在精度上是沒有明確的綁定的， p 有用的範圍對 timestamp和 interval 是從 0 到大約 6。
注意: 如果 timestamp 數值是以雙精度浮點數（目前的缺省）的方式存儲的， 那麼精度的有效限制會小于 6。 timestamp 值是以自 2000-01-01 以來的 秒數存儲的，而微秒的精度是為那些在 2000-01-01 前後幾年的日期實現的， 對于那些遠一些的日子，精度會下降。如果 timestamp 以八字節整數存儲 （一個編譯時的選項），那麼微秒的精度就可以在數值的全部範圍內都可以獲得。 不過，八位整數的時間戳的日期範圍縮小到 4713 BC 到 294276 AD。
注意: 在 PostgreSQL 7.3 以前，只寫 timestamp 等效于 timestamp with time zone。 這樣是為了和 SQL 兼容。
對于 time 類型，如果使用了八字節的整數存儲，那麼 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 標準要求的更靈活． 參閱 Appendix B 獲取關于日期/時間輸入的準確的分析規則和可識別文本字段， 包括月份，星期幾，和時區．
請記住任何日期或者時間的文本輸入需要由單引號包圍， 就象一個文本字符串一樣。 參考 Section 4.1.2.4 獲取更多信息．SQL 要求下面的語法
type [ (p) ] 'value'
在這裡可選的精度聲明中的 p 是一個整數， 對應在秒域中小數部分的位數， 我們可以對 time， timestamp，和 interval 類型聲明精度． 允許的精度在上面已經說明。如果在常量聲明中沒有聲明精度， 缺省是文本值的精度。
Table 8-10 顯示了 date 類型可能的輸入方式。
Table 8-10. 日期輸入
| 例子 | 描述 |
|---|---|
| January 8, 1999 | 在任何datestyle輸入模式下都無歧義 |
| 1999-01-08 | ISO-8601 格式，任何方式下都是199年1月8號，（建議格式） |
| 1/8/1999 | 歧義，在MDY下是一月八號；在 DMY 模式下讀做八月一日 |
| 1/18/1999 | 在MDY模式下讀做一月十八日，其它模式下被拒絕 |
| 01/02/03 | MDY 模式下的2003年一月2日； DMY 模式下的 2003 年 2月 1日； YMD 模式下的2001年二月三日； |
| 1999-Jan-08 | 任何模式下都是一月8日 |
| Jan-08-1999 | 任何模式下都是一月8日 |
| 08-Jan-1999 | 任何模式下都是一月8日 |
| 99-Jan-08 | 在 YMD 模式下是一月8日，否則錯誤 |
| 08-Jan-99 | 一月八日，除了在 YMD 模式下是錯誤的之外 |
| Jan-08-99 | 一月八日，除了在 YMD 模式下是錯誤的之外 |
| 19990108 | ISO-8601; 任何模式下都是1999年1月8日 |
| 990108 | ISO-8601; 任何模式下都是1999年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。
這些類型的有效輸入由當日時間後面跟著可選的時區組成。 （參閱 Table 8-11。） 如果在 time without time zone 類型的輸入 中聲明了時區，那麼它會被無聲地忽略。
時間戳 類型的有效輸入由一個 日期和時間的聯接組成，後面跟著一個可選的 AD 或者 BC，然後再跟著可選的時區．(見Table 8-12．) 因此
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 構造（參閱 Section 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, century, millennium, 或者這些單位的縮寫或復數； direction 可以是 ago 或者為空。符號 @ 是一個可選的雜音．不同的單位以及相應 正確的符號都是隱含地增加的．
日期，小時，分鐘，以及秒鐘的數量可以在無明確單位標記的情況下聲明． 比如，'1 12:59:10' 和 '1 day 12 hours 59 min 10 sec' 讀數一樣．
可選的精度 p 應該介于 0 和 6 之間， 並且缺省是輸入文本的精度．
下面的 SQL-兼容的函數可以用于 對應的數據類型的日期或時間輸入： CURRENT_DATE， CURRENT_TIME，CURRENT_TIMESTAMP， LOCALTIME，LOCALTIMESTAMP 後面四個接受一個可選的精度聲明． （又見 Section 9.8.4。）
PostgreSQL 為方便起見同樣還支持幾個特殊輸入值， 如在 Table 8-13 裡面顯示的那樣。 值infinity 和 -infinity 是特別在系統內部表示的，並且將按照同樣的方式顯示； 但是其它的都只是符號縮寫，在讀取的時候將唄轉換成普通的日期/時間值。 所有這些值都被當作普通常量對待，所以需要寫在單引號裡面。
使用 SET DateStyle，時間/日期類型的輸出格式可以設成四種風格之一： ISO 8601，SQL （Ingres），傳統的 POSTGRES，和 German 。缺省是 ISO 格式。 （SQL 標準要求使用 ISO 8601 格式。"SQL" 輸出格式的名字是歷史偶然。）Table 8-14 顯示了每種輸出風格的例子。date 和 time 類型的 輸出當然只是給出的例子裡面的日期和時間部分。
Table 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 |
如果聲明了 DMY 字段，那麼哉 SQL 和 POSTGRES 風格裡，日期在月份之前出現，否則月份出現在日期之前。 （參閱 Section 8.5.1 分，看看這個設置是如何影響對輸入值的解釋。） Table 8-15 顯示了一個例子。
Table 8-15. 日期順序習慣
| 風格描述 | 描述 | 例子 |
|---|---|---|
| SQL, DMY | 日/月/年 | 17/12/1997 15:37:16.00 CET |
| SQL, MDY | 月/日/年 | 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（參閱 Section 9.7） 來更靈活地控制時間/日期地輸出。
時區和時區習慣不僅僅受地球幾何形狀的影響，還受到政治決定的影響。 到了19世紀，全球的時區變得稍微標準化了些，但是還是易于遭受隨意的修改。 PostgreSQL 使用你的 操作系統下層的特性提供時區支持， 這些系統通常只包含 1902 年到 2038 年之間的信息（對應于傳統 Unix 系統時間的完整跨度）。 timestamp with time zone 和 time with time zone 將只在這個年代範圍內使用時區信息， 如果時間超過這個範圍，那麼假設時間是 UTC。 但是因為時區支持是來自下層操作系統的時區功能，所以，它可以處理夏時制和其它特殊的行為。
PostgreSQL 在典型應用中盡可能與 SQL 的定義相兼容．但 SQL 標準在日期和時間類型 和功能上有一些奇怪的混淆．兩個顯而易見的問題是：
date （日期）類型與時區沒有聯系，而 time （時間）類型卻有或可以有． 然而，現實世界的時區只有在與時間和日期都關聯時才有意義， 因為時間偏移量（時差）可能因為實行類似夏 時制這樣的制度而在一年裡有所變化．
缺省的時區用一個數字常量表示與UTC的偏移（時差）． 如果這樣， 當跨 DST 界限做日期/時間算術時， 我們根本不可能把夏時制這樣的因素計算進去．
為了克服這些困難，我們建議在使用時區的時候， 使用那些同時包含日期和時間的日期/時間類型． 我們建議不要使用類型 time with time zone （盡管 PostgreSQL 出于合理應用以及為了與其他RDBMS實現兼容的考慮支持這個類型）． PostgreSQL 假設你用于任何類型的本地時區 都只包含日期或時間．
在系統內部,所有日期和時間都是用全球統一時間UTC格式存儲， 時間在發給客戶前端前由數據庫服務器轉換成本地時間， 因而缺省的時區是服務器的時區．
有好幾種選取服務器使用的時區的方法：
如果沒有聲明其他的， 直接在服務器主機上的TZ環境變量作為服務器的缺省時區．
我們可以在 postgresql.conf 文件裡設置 timezone 配置參數。
如果在客戶端設置了PGTZ環境變量，那麼libpq在聯接時將使用 這個環境變量給後端發送一個 SET TIME ZONE 命令．
SQL 命令 SET TIME ZONE 可以給會話設置時區．
注意: 如果使用了非法的時區，時區變為 UTC（在大多數系統上）。
請參考 Appendix B 獲取一個可用 時區的列表。
PostgreSQL 使用儒略歷法用于所有日期/時間計算。 如果假設一年的長度是365.2425天時，這個方法可以 很精確地預計/計算從4713 BC（公元前4713年）到很久的未來的任意一天的日期。
19世紀以前的日期傳統（歷法）對一些趣味讀物有意義， 但是在我們這裡好象沒有充分的理由把它們編碼入日 期/時間控制器裡面去。