| PostgreSQL 8.0.0 中文文件（轉譯自 PostgreSQL 中國 製作的簡體中文版本） | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 8. 資料類型 | Fast Forward | Next |
PostgreSQL 支援 SQL 中所有的日期和時間類型。 在 Table 8-9 中顯示。 這些資料類型上可以進行的操作在 Section 9.9 描述。
Table 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 毫秒 |
| 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 毫秒 |
注意: 在 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 前後幾年的日期實現的， 對於那些遠一些的日子，精度會下降。如果 timestamp 以八字元整數儲存（一個編譯時的選項），那麼微秒的精度就可以在數值的全部範圍內都可以獲得。 不過，八位整數的時間戳的日期範圍縮小到 4713 BC 到 294276 AD。
對於 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.5 獲取更多訊息。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 類型的輸入 中聲明了時區，那麼它會被無聲地忽略。
Table 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 | 用名字聲明的時區 |
Table 8-12. 時區輸入
| 例子 | 描述 |
|---|---|
| PST | 太平洋標準時間（Pacific Standard Time） |
| -8:00 | ISO-8601 與 PST 的偏移 |
| -800 | ISO-8601 與 PST 的偏移 |
| -8 | ISO-8601 與 PST 的偏移 |
| zulu | 軍方對 UTC 的縮寫（譯註：可能是美軍） |
| z | zulu 的縮寫 |
參考Appendix B 獲取可以識別的時區輸入。
時間戳類型的有效輸入由一個日期和時間的連線組成，後面跟著一個可選的時區，一個可選的 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 [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.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 是 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 之間， 並且預設是輸入文本的精度。
PostgreSQL 為方便起見支援幾個特殊輸入值， 如在 Table 8-13 裡面顯示的那樣。 值infinity 和 -infinity 是特別在系統內部資料表示的，並且將按照同樣的方式顯示； 但是其它的都只是符號縮寫，在讀取的時候將被轉換成普通的日期/時間值。 （特別是，now 和相關的字串在讀取的時候就被轉換成對應的數值。） 所有這些值在 SQL 命令裡當作普通常量對待時，都需要寫在單引號裡面。
Table 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。最後四個接受一個可選的精度聲明。 （見 Section 9.9.4 。） 不過，請注意這些 SQL 函數不是被當作資料輸入串識別的。
使用 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:secondes ]
日期/時間風格可以由用戶用 SET datestyle 命令 選取，或者在 postgresql.conf 配置文件裡的參數 DateStyle 設置，或者伺服器或客戶端的 PGDATESTYLE 環境變量裡設置。我們也可以用格式化函數 to_char（參閱 Section 9.8） 來更靈活地控制時間/日期地輸出。
時區和時區習慣不僅僅受地球幾何形狀的影響，還受到政治決定的影響。 到了19世紀，全球的時區變得稍微標準化了些，但是還是易於遭受隨意的修改， 部分是因為夏時制規則。 PostgreSQL 目前支援 1902 年到 2038 年之間的夏時制訊息（對應於傳統 Unix 系統時間的完整跨度）。 如果時間超過這個範圍，那麼假設時間是選取的時區的"標準時間"，不管它們落在哪個年份裡面。
PostgreSQL 在典型應用中盡可能與 SQL 的定義相兼容。但 SQL 標準在日期和時間類型和功能上有一些奇怪的混淆。兩個顯而易見的問題是：
date （日期）類型與時區沒有聯繫，而 time （時間）類型卻有或可以有。 然而，現實世界的時區只有在與時間和日期都關聯時才有意義， 因為時間偏移量（時差）可能因為實行類似夏時制這樣的制度而在一年裡有所變化。
預設的時區用一個數字常量資料表示與UTC的偏移（時差）。 因此，當跨 DST 界限做日期/時間算術時， 我們根本不可能把夏時制這樣的因素計算進去。
為了克服這些困難，我們建議在使用時區的時候，使用那些同時包含日期和時間的日期/時間類型。 我們建議不要使用類型 time with time zone （儘管 PostgreSQL 出於合理應用以及為了與其他RDBMS實現兼容的考慮支援這個類型）。 PostgreSQL 假設您用於任何類型的本地時區都只包含日期或時間。
在系統內部，所有日期和時間都是用全球統一時間UTC格式儲存， 時間在發給客戶前端前由資料庫伺服器轉換成本地時間，使用的是配置參數 timezone 聲明的時區。
我們可以在 postgresql.conf 文件裡設置配置參數 timezone， 或者用任何其它在 Section 16.4 描述的標準方法。 還有好幾種特殊方法可以設置它：
如果沒有在 postgresql.conf 裡聲明 timezone，也沒有在命令行開關上聲明， 伺服器試圖使用伺服器主機上的TZ環境變量作為伺服器的預設時區。 如果沒有定義 TZ，或者是 PostgreSQL 不認識的時區名， 那麼伺服器將試圖透過檢查 C 庫函數 localtime() 的行為來判斷操作系統的預設時區。 預設時區是按照最接近 PostgreSQL 的已知時區的原則來選擇的。
SQL 命令 SET TIME ZONE 為會話設置時區，這是 SET TIMEZONE TO 的一個可選的拼寫方式， 更加兼容標準。
如果在客戶端設置了PGTZ環境變量， 那麼libpq在連線時將使用這個環境變量給後端發送一個 SET TIME ZONE 命令。
請參考 Appendix B 獲取一個可用時區的列資料表。
PostgreSQL 使用儒略曆法用於所有日期/時間計算。 如果假設一年的長度是365.2425天時，這個方法可以 很精確地預計/計算從4713 BC（公元前4713年）到很久的未來的任意一天的日期。
19世紀以前的日期傳統（曆法）只是對一些趣味讀物有意義， 而在我們這裡好像沒有充分的理由把它們編碼入日期/時間控制器裡面去。