8.5. 日期/時間類型

PostgreSQL 支援 SQL 中所有的日期和時間類型。 在 Table 8-9 中顯示。 這些資料類型上可以進行的操作在 Section 9.9 描述。

Table 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 zone只用於一日內時間,帶時區12 字元00:00:00.00+1223:59:59.99-121 毫秒

注意: PostgreSQL 7.3 以前,只寫 timestamp 等效於 timestamp with time zone。 這樣是為了和 SQL 兼容。

timetimestampinterval 接受一個可選的精度值 p,這個精度值聲明在秒域後面小數點之後保留的位數。 預設的時候在精度上是沒有明確的綁定的, p 有用的範圍對 timestampinterval 是從 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 標準定義的, 但是整個定義有些方面會導致有問題的用法。在大多數情況下, datetimetimestamp without time zonetimestamp with time zone 的組合就應該能提供提供任何應用需要的日期/時間的完整功能。

類型 abstimereltime 是低分辨率類型,它們被用於系統內部。 我們不鼓勵您在新的應用裡面使用這些類型,同時我們支援合適的時候把舊應用中對應的類型轉換成目前上面指明的。 因為這些舊類型的部分或全部可能會在未來的版本裡消失。

8.5.1. 日期/時間輸入

日期和時間的輸入幾乎可以是任何合理的格式,包括 ISO 8601SQL-兼容的, 傳統 POSTGRES 的和其他的形式。 對於一些格式,日期輸入裡的月份和日子輸入可能會讓人模糊, 因此系統支援聲明自己預期的這些字串的順序。 把 DateStyle 參數設置為 MDY, 就是「月-日-年」的解析,設置為 DMY 就是 「日-月-年」,而 YMD 是 「年-月-日」。

PostgreSQL 在處理日期/時間輸入上比 SQL 標準要求的更靈活。 參閱 Appendix B 獲取關於日期/時間輸入的準確的分析規則和可識別文本字串,包括月份,星期幾,和時區。

請記住任何日期或者時間的文本輸入需要由單引號包圍, 就像一個文本字元串一樣。 參考 Section 4.1.2.5 獲取更多訊息。SQL 要求下面的語法

type [ (p) ] 'value'

在這裡可選的精度聲明中的 p 是一個整數, 對應在秒域中小數部分的位數, 我們可以對 timetimestamp,和 interval 類型聲明精度。 允許的精度在上面已經說明。如果在常量聲明中沒有聲明精度,預設是文本值的精度。

8.5.1.1. 日期

Table 8-10 顯示了 date 類型可能的輸入方式。

Table 8-10. 日期輸入

例子描述
January 8, 1999在任何datestyle輸入模式下都無歧義
1999-01-08ISO-8601 格式,任何方式下都是199年1月8號,(建議格式)
1/8/1999歧義,在MDY下是一月八號;在 DMY 模式下讀做八月一日
1/18/1999MDY模式下讀做一月十八日,其它模式下被拒絕
01/02/03MDY 模式下的2003年一月2日; DMY 模式下的 2003 年 2月 1日; YMD 模式下的2001年二月三日;
1999-Jan-08任何模式下都是一月8日
Jan-08-1999任何模式下都是一月8日
08-Jan-1999任何模式下都是一月8日
99-Jan-08YMD 模式下是一月8日,否則錯誤
08-Jan-99一月八日,除了在 YMD 模式下是錯誤的之外
Jan-08-99一月八日,除了在 YMD 模式下是錯誤的之外
19990108ISO-8601; 任何模式下都是1999年1月8日
990108ISO-8601; 任何模式下都是1999年1月8日
1999.008年和年裡的第幾天
J2451187儒略日
January 8, 99 BC公元前99年

8.5.1.2. 時間

當日時間類型是 time [ (p) ] without time zonetime [ (p) ] with time zone。 只寫 time 等效於 time without time zone

這些類型的有效輸入由當日時間後面跟著可選的時區組成。 (參閱 Table 8-11。) 如果在 time without time zone 類型的輸入 中聲明了時區,那麼它會被無聲地忽略。

Table 8-11. 時間輸入

例子描述
04:05:06.789ISO 8601
04:05:06ISO 8601
04:05ISO 8601
040506ISO 8601
04:05 AM與 04:05 一樣;AM 不影響數值
04:05 PM與 16: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用名字聲明的時區

Table 8-12. 時區輸入

例子描述
PST太平洋標準時間(Pacific Standard Time)
-8:00ISO-8601 與 PST 的偏移
-800ISO-8601 與 PST 的偏移
-8ISO-8601 與 PST 的偏移
zulu軍方對 UTC 的縮寫(譯註:可能是美軍)
zzulu 的縮寫

參考Appendix B 獲取可以識別的時區輸入。

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 zonetimestamp 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 zonePostgreSQL 與標準不同的地方是要求必須明確鍵入 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 zonetimestamp with time zone 之間的轉換通常假設 timestamp without time zone 數值應該以 timezone 本地時間的形式接受或者寫出。 其它的時區引用可以用 AT TIME ZONE 的方式為轉換聲明。

8.5.1.4. 間隔

interval數值可以用下面語法聲明:

[@] quantity unit [quantity unit...] [direction]

這裡:quantity 是一個數字(可能有符號); unitsecond, 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 之間, 並且預設是輸入文本的精度。

8.5.1.5. 特殊值

PostgreSQL 為方便起見支援幾個特殊輸入值, 如在 Table 8-13 裡面顯示的那樣。 值infinity-infinity 是特別在系統內部資料表示的,並且將按照同樣的方式顯示; 但是其它的都只是符號縮寫,在讀取的時候將被轉換成普通的日期/時間值。 (特別是,now 和相關的字串在讀取的時候就被轉換成對應的數值。) 所有這些值在 SQL 命令裡當作普通常量對待時,都需要寫在單引號裡面。

Table 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。最後四個接受一個可選的精度聲明。 (見 Section 9.9.4 。) 不過,請注意這些 SQL 函數不是被當作資料輸入串識別的。

8.5.2. 日期/時間輸出

使用 SET DateStyle,時間/日期類型的輸出格式可以設成四種風格之一: ISO 8601,SQL (Ingres),傳統的 POSTGRES,和 German 。預設是 ISO 格式。 (SQL 標準要求使用 ISO 8601 格式。"SQL" 輸出格式的名字是歷史偶然。)Table 8-14 顯示了每種輸出風格的例子。datetime 類型的 輸出當然只是給出的例子裡面的日期和時間部分。

Table 8-14. 日期/時間輸出風格

風格描述描述例子
ISOISO-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, DMYday/month/yearWed 17 Dec 07:37:16 1997 PST

interval 的輸出看起來像輸入格式,只是象 centuryweek 這樣的單位被轉換成年和日,而 ago 被轉換成合適的符號。在 ISO 模式下輸出看起來像

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

日期/時間風格可以由用戶用 SET datestyle 命令 選取,或者在 postgresql.conf 配置文件裡的參數 DateStyle 設置,或者伺服器或客戶端的 PGDATESTYLE 環境變量裡設置。我們也可以用格式化函數 to_char(參閱 Section 9.8) 來更靈活地控制時間/日期地輸出。

8.5.3. 時區

時區和時區習慣不僅僅受地球幾何形狀的影響,還受到政治決定的影響。 到了19世紀,全球的時區變得稍微標準化了些,但是還是易於遭受隨意的修改, 部分是因為夏時制規則。 PostgreSQL 目前支援 1902 年到 2038 年之間的夏時制訊息(對應於傳統 Unix 系統時間的完整跨度)。 如果時間超過這個範圍,那麼假設時間是選取的時區的"標準時間",不管它們落在哪個年份裡面。

PostgreSQL 在典型應用中盡可能與 SQL 的定義相兼容。但 SQL 標準在日期和時間類型和功能上有一些奇怪的混淆。兩個顯而易見的問題是:

為了克服這些困難,我們建議在使用時區的時候,使用那些同時包含日期和時間的日期/時間類型。 我們建議不要使用類型 time with time zone (儘管 PostgreSQL 出於合理應用以及為了與其他RDBMS實現兼容的考慮支援這個類型)。 PostgreSQL 假設您用於任何類型的本地時區都只包含日期或時間。

在系統內部,所有日期和時間都是用全球統一時間UTC格式儲存, 時間在發給客戶前端前由資料庫伺服器轉換成本地時間,使用的是配置參數 timezone 聲明的時區。

我們可以在 postgresql.conf 文件裡設置配置參數 timezone, 或者用任何其它在 Section 16.4 描述的標準方法。 還有好幾種特殊方法可以設置它:

請參考 Appendix B 獲取一個可用時區的列資料表。

8.5.4. 內部

PostgreSQL 使用儒略曆法用於所有日期/時間計算。 如果假設一年的長度是365.2425天時,這個方法可以 很精確地預計/計算從4713 BC(公元前4713年)到很久的未來的任意一天的日期。

19世紀以前的日期傳統(曆法)只是對一些趣味讀物有意義, 而在我們這裡好像沒有充分的理由把它們編碼入日期/時間控制器裡面去。