9.9. 時間/日期函數和操作符

Table 9-26 顯示了 PostgreSQL 裡可以用於處理日期/時間數值的函數,隨後一節裡描述了細節。 Table 9-25 演示了基本算術操作符 (+* 等等)的行為。 而與格式化相關的函數,可以參考Section 9.8。 您應該很熟悉來自 Section 8.5 的日期/時間資料類型的背景知識。

所有下面描述的函數和操作符接收的time或者timestamp輸入實際上都來自兩種可能: 一個是接收time with time zonetimestamp with time zone, 另外一種是time without time zone 或者 timestamp without time zone。 出於簡化考慮,這些變種沒有獨立顯示出來。還有,+* 操作符都是以可交換的操作符對方式存在的 (比如,date + integer 和 integer + date);我們只顯示了這樣的交換操作符對中的一個。

Table 9-25. 日期/時間操做符

操作符例子結果
+ date '2001-09-28' + integer '7'date '2001-10-05'
+ date '2001-09-28' + interval '1 hour'timestamp '2001-09-28 01:00'
+ date '2001-09-28' + time '03:00'timestamp '2001-09-28 03:00'
+ interval '1 day' + interval '1 hour'interval '1 day 01:00'
+ timestamp '2001-09-28 01:00' + interval '23 hours'timestamp '2001-09-29 00:00'
+ time '01:00' + interval '3 hours'time '04:00'
- - interval '23 hours'interval '-23:00'
- date '2001-10-01' - date '2001-09-28'integer '3'
- date '2001-10-01' - integer '7'date '2001-09-24'
- date '2001-09-28' - interval '1 hour'timestamp '2001-09-27 23:00'
- time '05:00' - time '03:00'interval '02:00'
- time '05:00' - interval '2 hours'time '03:00'
- timestamp '2001-09-28 23:00' - interval '23 hours'timestamp '2001-09-28 00:00'
- interval '1 day' - interval '1 hour'interval '23:00'
- timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'interval '1 day 15:00'
* interval '1 hour' * double precision '3.5'interval '03:30'
/ interval '1 hour' / double precision '1.5'interval '00:40'

Table 9-26. 日期/時間函數

函數返回類型描述例子結果
age(timestamp, timestamp)interval減去參數,生成一個使用年、月的"符號化"的結果age('2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 days
age(timestamp)intervalcurrent_date減去得到的數值age(timestamp '1957-06-13')43 years 8 mons 3 days
current_datedate今天的日期;見 Section 9.9.4   
current_timetime with time zone現在的時間;見 Section 9.9.4   
current_timestamptimestamp with time zone日期和時間;見 Section 9.9.4   
date_part(text, timestamp)double precision獲取子域(等效於 extract);又見 Section 9.9.1 date_part('hour', timestamp '2001-02-16 20:38:40')20
date_part(text, interval)double precision獲取子域(等效於 extract);又見 Section 9.9.1 date_part('month', interval '2 years 3 months')3
date_trunc(text, timestamp)timestamp截斷成指定的精度;又見Section 9.9.2 date_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00+00
extract(field from timestamp)double precision獲取子域;又見 Section 9.9.1 extract(hour from timestamp '2001-02-16 20:38:40')20
extract(field from interval)double precision獲取子域;又見 Section 9.9.1 extract(month from interval '2 years 3 months')3
isfinite(timestamp)boolean測試有窮時間戳(非無窮)isfinite(timestamp '2001-02-16 21:28:30')true
isfinite(interval)boolean測試有窮時間間隔isfinite(interval '4 hours')true
localtimetime今日的時間;見 Section 9.9.4   
localtimestamptimestamp日期和時間;見 Section 9.9.4   
now()timestamp with time zone目前的日期和時間(等效於 current_timestamp);見Section 9.9.4   
timeofday()text目前日期和時間;見Section 9.9.4   

除了這些函數以外,還支援 SQL 操作符 OVERLAPS

( start1, end1 ) OVERLAPS ( start2, end2 )
( start1, length1 ) OVERLAPS ( start2, length2 )

這個資料表達式在兩個時間域(用它們的終點定義)重疊的時候生成真值。 終點可以以一對日期,時間,或者時間戳來聲明;或者是一個後面跟著一個時間間隔的 日期,時間,時間戳。

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false

9.9.1. EXTRACTdate_part

EXTRACT (field from source)

extract 函數從日期/時間數值裡抽取 子域,比如年或者小時等。source 必須是一個類型 timestamptime,或者 interval 的值資料表達式。 (類型為 date 的資料表達式將轉換為 timestamp,因此也可以用。) field 是一個標識符 或者字串,它指定從源資料中抽取的數域。extract 函數返回類型為double precision 的數值。 下列數值是有效資料域的名字︰

century

世紀。

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
Result: 20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 21

第一個世紀從 0001-01-01 00:00:00 AD 開始, 儘管那時候人們還不知道這是第一個世紀。這個定義適用於所有使用格裡高利曆法的國家。 沒有 0 世紀,我們直接從公元前 1 世紀到公元 1 世紀。 如果您認為這個不合理,那麼請把抱怨發給:羅馬聖彼得教堂,梵蒂岡,教皇收。

PostgreSQL 8.0 以前版本裡並不遵循世紀的習慣編號,只是把年份除以 100。

day

(月分)裡的日期域(1-31)

SELECT EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40');
Result: 16
decade

年份域除以10

SELECT EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40');
Result: 200
dow

每週的星期號(0 - 6;星期天是 0) (僅用於 timestamp)

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 5

請注意 extract 的星期幾編號和 to_char 函數的不同。

doy

一年的第幾天(1 -365/366) (僅用於 timestamp)

SELECT EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40');
Result: 47
epoch

對於 datetimestamp 數值而言, 是自 1970-01-01 00:00:00 以來的秒數(結果可能是負數。); 對於 interval 數值而言,它是時間間隔的總秒數。

SELECT EXTRACT(EPOCH from TIMESTAMP '2001-02-16 20:38:40');
Result: 982352320

SELECT EXTRACT(EPOCH from INTERVAL '5 days 3 hours');
Result: 442800

下面是把 epoch 值轉換回時間戳的方法:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
hour

小時域 (0 - 23)

SELECT EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40');
Result: 20
microseconds

秒域,包括小數部分,乘以 1,000,000。請注意它包括全部的秒。

SELECT EXTRACT(MICROSECONDS from TIME '17:12:28.5');
Result: 28500000
millennium

千年。

SELECT EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40');
Result: 3

20世紀(19xx年)裡面的年份在第二個千年裡。第三個千年從 2001 年一月一日開始。

PostgreSQL 8.0 之前的版本並不遵循前年編號的習慣,只是返回年份除以 1000。

milliseconds

秒域,包括小數部分,乘以 1000。請注意它包括完整的秒。

SELECT EXTRACT(MILLISECONDS from TIME '17:12:28.5');
Result: 28500
minute

分鐘域 (0 - 59)

SELECT EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40');
Result: 38
month

對於 timestamp 數值,它是一年裡的月份數(1 - 12); 對於 interval 數值,它是月的數目,然後對 12 取模(0 - 11)

SELECT EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40');
Result: 2

SELECT EXTRACT(MONTH from INTERVAL '2 years 3 months');
Result: 3

SELECT EXTRACT(MONTH from INTERVAL '2 years 13 months');
Result: 1
quarter

該天所在的該年的季度(1 - 4)(僅用於 timestamp)

SELECT EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');
Result: 1
second

秒域,包括小數部分 (0 - 59 [1])

SELECT EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40');
Result: 40

SELECT EXTRACT(SECOND from TIME '17:12:28.5');
Result: 28.5
timezone

與 UTC 的時區偏移,以秒記。正數對應 UTC 東邊的時區,負數對應 UTC 西邊的時區。

timezone_hour

時區偏移的小時部分。

timezone_minute

時區偏移的分鐘部分。

week

該天在所在的年份裡是第幾周。根據定義 (ISO 8601), 一年的第一周包含該年的一月四日。(ISO-8601的周從星期一開始。) 換句話說,一年的第一個星期四在第一周。(只用於 timestamp 數值)。

SELECT EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40');
Result: 7
year

年份域。要記住這裡沒有 0 AD,所以從 AD 年裡抽取 BC 年應該小心些。

SELECT EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40');
Result: 2001

extract 函數主要的用途是做運算用。 對於用於顯示的日期/時間數值格式化,參閱 Section 9.8

date_part 函數是在傳統的 Ingres 函數的基礎上製作的(該 函數等效於 SQL 標準函數 extract)︰

date_part('field', source)

請注意這裡的 field 參數必須是 一個字串值,而不是一個名字。有效的 date_part 數域名 和用於 extract 的是一樣的。

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 4

9.9.2. date_trunc

date_trunc 函數在概念上和用於 數字的 trunc 函數類似。

date_trunc('field', source)

source 是類型 timestamp 的值資料表達式(類型 datetime 的數值都分別自動轉換成timestamp或者interval)。 用 field 選擇對該時間戳數值 選用什麼樣的精度進行截斷)。 返回的數值是 timestamp 類型或者interval,所有小於選定的 精度的域都設置為零(或者一,如果是日期和月份域的話)。

field 的有效數值是︰

microseconds
milliseconds
second
minute
hour
day
week
month
year
decade
century
millennium

例子:

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00+00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00+00

9.9.3. AT TIME ZONE

AT TIME ZONE 構造允許把時間戳轉換成不同的 時區。Table 9-27 顯示了其變體。

Table 9-27. AT TIME ZONE變體

資料表達式返回類型描述
timestamp without time zone AT TIME ZONE zone timestamp with time zone把給定時區的當地時間轉換成 UTC
timestamp with time zone AT TIME ZONE zone timestamp without time zone把 UTC 轉換成給定時區的當地時間
time with time zone AT TIME ZONE zone time with time zone在時區之間轉換當地時間

在這些資料表達式裡,我們需要的 zone 可以聲明為 文本串(比如,'PST')或者一個時間間隔 (比如,INTERVAL '-08:00')。 在文本的情況下,可用的時區名字在 Table B-4 裡顯示。 (可能支援更通用的 Table B-6 會更好些,不過目前這些還沒有實現。)

例子(假設本地時區是 PST8PDT):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
Result: 2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40

第一個例子接受一個無時區的時間戳然後把她解釋成 MST 時間(UTC-7) 生成 UTC 時間戳,然後這個時間轉換為 PST(UTC-8)來顯示。 第二個例子接受一個聲明為 EST(UTC-5)的時間戳,然後把它 轉換成 MST(UTC-7)的當地時間。

函數timezone(zone, timestamp) 等效於 SQL 兼容的構造timestamp AT TIME ZONE zone

9.9.4. 目前日期/時間

我們可以使用下面的函數獲取目前的日期和/或時間︰

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME ( precision )
CURRENT_TIMESTAMP ( precision )
LOCALTIME
LOCALTIMESTAMP
LOCALTIME ( precision )
LOCALTIMESTAMP ( precision )

CURRENT_TIMECURRENT_TIMESTAMP 帶有時區值; LOCALTIMELOCALTIMESTAMP 的數值沒有時區值。

CURRENT_TIMECURRENT_TIMESTAMPLOCALTIMELOCALTIMESTAMP 可以有選擇地給予一個精度參數, 該精度導致結果的秒數域園整為指定小數位。如果沒有精度參數, 將給予所能得到的全部精度。

注意: PostgreSQL 7.2 之前沒有實現精度參數, 結果總是給出整數的秒。

一些例子:

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2001-12-23

SELECT CURRENT_TIMESTAMP;
Result: 2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Result: 2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
Result: 2001-12-23 14:39:53.662522

函數 now() 是傳統的 PostgreSQLCURRENT_TIMESTAMP 的等效物。

還有一個 timeofday() 函數,由於歷史原因, 它返回一個字串,而不是 timestamp 值︰

SELECT timeofday();
Result: Sat Feb 17 19:07:32.000126 2001 EST

還有一件事提醒大家,那就是 CURRENT_TIMESTAMP 和相關的函數把時間當做目前交易的開始返回;在交易執行的時候, 它們的數值並不改變。 我們認為這是一個特性:目的是為了允許一個交易在"目前" 時間上有連貫的概念,這樣在同一個交易離得多個修改可以有同樣的時間戳。 但 timeofday() 返回目前的實際時間,並且隨著交易的處理會前進。

注意: 許多其它資料庫系統更頻繁地更新這些數值。

所有日期/時間類型還接受特殊的文本值 now, 用於聲明目前的日期和時間。因此,下面三個都返回相同的結果︰

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';

提示: 在建立資料表聲明一個DEFAULT值的時候您是不會想用第三種形式的。 因為系統將在分析這個常量的時候把 now 轉換成 一個 timestamp,因此在需要預設值的時候, 就會使用建立資料表的時間! 而頭兩種形式要到實際使用預設值的時候才計算, 因為它們是函數調用。因此它們可以給出插入時間行的時候 需要的預設行為。

Notes

[1]

如果操作系統實現了潤秒, 那麼上限是 60