?
Dieses Dokument verwendet PHP-Handbuch für chinesische Websites Freigeben
9.9. 時間/日期函數(shù)和操作符
Table 9-27顯示了 PostgreSQL 里可以用于處理日期/時間數(shù)值的函數(shù)， 隨后一節(jié)里描述了細節(jié)。表Table 9-26演示了基本算術(shù)操作符的行為。(+， *， 等)。而與格式化相關的函數(shù)，可以參考Section 9.8。 你應該很熟悉Section 8.5的日期/時間數(shù)據(jù)類型的背景知識。
所有下述函數(shù)和操作符接收的time或timestamp輸入實際上都來自兩種可能： 一種是接收time with time zoneortimestamp with time zone， 另外一種是接收time without time zone或timestamp without time zone。 出于簡化考慮，這些變種沒有獨立顯示出來。還有，+和*操作符都是以可交換的操作符對(比如， date + integer 和 integer + date)；我們只顯示了這樣的交換操作符對中的一個。
Table 9-26. 日期/時間操作符
| 操作符 | 示例 | 結(jié)果 |
|---|---|---|
| + | date '2001-09-28' + integer '7' | date '2001-10-05' |
| + | date '2001-09-28' + interval '1 hour' | timestamp '2001-09-28 01:00:00' |
| + | date '2001-09-28' + time '03:00' | timestamp '2001-09-28 03:00:00' |
| + | interval '1 day' + interval '1 hour' | interval '1 day 01:00:00' |
| + | timestamp '2001-09-28 01:00' + interval '23 hours' | timestamp '2001-09-29 00:00:00' |
| + | time '01:00' + interval '3 hours' | time '04:00:00' |
| - | - interval '23 hours' | interval '-23:00:00' |
| - | date '2001-10-01' - date '2001-09-28' | integer '3'(days) |
| - | date '2001-10-01' - integer '7' | date '2001-09-24' |
| - | date '2001-09-28' - interval '1 hour' | timestamp '2001-09-27 23:00:00' |
| - | time '05:00' - time '03:00' | interval '02:00:00' |
| - | time '05:00' - interval '2 hours' | time '03:00:00' |
| - | timestamp '2001-09-28 23:00' - interval '23 hours' | timestamp '2001-09-28 00:00:00' |
| - | interval '1 day' - interval '1 hour' | interval '1 day -01:00:00' |
| - | timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' | interval '1 day 15:00:00' |
| * | 900 * interval '1 second' | interval '00:15:00' |
| * | 21 * interval '1 day' | interval '21 days' |
| * | double precision '3.5' * interval '1 hour' | interval '03:30:00' |
| / | interval '1 hour' / double precision '1.5' | interval '00:40:00' |
Table 9-27. 日期/時間函數(shù)
| 函數(shù) | 返回類型 | 描述 | 示例 | 結(jié)果 |
|---|---|---|---|---|
age(timestamp，timestamp) |
interval | 參數(shù)相減， 返回一個使用年數(shù)和月份數(shù)的"符號"表示的結(jié)果 | age(timestamp '2001-04-10'， timestamp '1957-06-13') | 43 years 9 mons 27 days |
age(timestamp) |
interval | 從current_date（午夜）減去參數(shù)后的結(jié)果 |
age(timestamp '1957-06-13') | 43 years 8 mons 3 days |
clock_timestamp() |
timestamp with time zone | 實時時鐘的當前時間戳（在語句執(zhí)行中值會改變）；見Section 9.9.4。 | ? | ? |
current_date |
date | 當前日期；見Section 9.9.4 | ? | ? |
current_time |
time with time zone | 當前時間；見Section 9.9.4 | ? | ? |
current_timestamp |
timestamp 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 |
extract(fieldfrom
timestamp) |
double precision | 獲取子域；見Section 9.9.1 | extract(hour from timestamp '2001-02-16 20:38:40') | 20 |
extract(fieldfrom
interval) |
double precision | 獲取子域；又見 seeSection 9.9.1 | extract(month from interval '2 years 3 months') | 3 |
isfinite(date) |
boolean | 測試是否為有窮日期 | isfinite(date '2001-02-16') | true |
isfinite(timestamp) |
boolean | 測試是否為有窮時間戳 | isfinite(timestamp '2001-02-16 21:28:30') | true |
isfinite(interval) |
boolean | 測試是否為有窮時間間隔 | isfinite(interval '4 hours') | true |
justify_days(interval) |
interval | 按照每月 30 天計算時間間隔 | justify_days(interval '35 days') | 1 mon 5 days |
justify_hours(interval) |
interval | 按照每天 24 小時計算時間間隔 | justify_hours(interval '27 hours') | 1 day 03:00:00 |
justify_interval(interval) |
interval | 用justify_days和justify_hours計算時間間隔的，加上相應的正負號。
|
justify_interval(interval '1 mon -1 hour') | 29 days 23:00:00 |
localtime |
time | 當前時間；見Section 9.9.4 | ? | ? |
localtimestamp |
timestamp | 當前事務開始時的時間戳；見Section 9.9.4 | ? | ? |
now() |
timestamp with time zone | 當前事務開始時的時間戳；見Section 9.9.4 | ? | ? |
statement_timestamp() |
timestamp with time zone | 當前語句開始的時間戳；見Section 9.9.4 | ? | ? |
timeofday() |
text | 當前日期時間，與clock_timestamp相同，但結(jié)果是一個text字符串；見Section 9.9.4。
|
? | ? |
transaction_timestamp() |
timestamp with time zone | 當前事務開始時的時間戳；見Section 9.9.4 | ? | ? |
除了這些函數(shù)以外，還支持 SQL 的 OVERLAPS 操作符:
(start1，end1) OVERLAPS (start2，end2) (start1，length1) OVERLAPS (start2，length2)
這個表達式在兩個時間域(用它們的終點定義)重疊的時候生成真值，否則為假。 終點可以用一對日期、時間、時間戳來聲明；或者是一個后面跟著時間間隔的日期、時間、時間戳。 當提供一對值，不管先寫開始還是結(jié)束；OVERLAPS自動將這對值較早的作為開始。 每段時間取值為半開區(qū)間開始<=時間<結(jié)束， 除非開始和結(jié)束相等，表示單一的時刻。這意味著兩個時間段只有一個共同的端點沒有重疊。
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
SELECT (DATE '2001-10-29'， DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30'， DATE '2001-10-31');
Result:false
SELECT (DATE '2001-10-30'， DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30'， DATE '2001-10-31');
Result:true
當把interval值添加到timestamp with time zone上(或從中減去)的時候， days 部分會按照指定的天數(shù)增加(或減少)timestamp with time zone的日期。 對于橫跨夏令時的變化(會話的時區(qū)設置被識別為夏時制)，interval '1 day'并 不一定等于interval '24 hours'。例如，當會話的時區(qū)設置為CST7CDT的時候， timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' 的結(jié)果 是 timestamp with time zone '2005-04-03 12:00-06' ，而 將interval '24 hours'增加到相同的timestamp with time zone之上的結(jié)果 則是timestamp with time zone '2005-04-03 12:00-06'， 因為CST7CDT時區(qū)在2005-04-03 02:00的時候有一個夏令時變更。
注意age返回的月數(shù)可能有歧義，因為不同的月份有不同的天數(shù)。
PostgreSQL的方法是當計算部分月數(shù)時，采用兩個日期較早的月。例如：age('2004-06-01'， '2004-04-30')
使用4月份產(chǎn)生1 mon 1 day，當用5月分時產(chǎn)生1 mon 2 days，因為5月有31天，而4月只有30天。
9.9.1. EXTRACT，date_part
EXTRACT(fieldFROMsource)
extract函數(shù)從日期/時間數(shù)值里抽取子域，比如年、小時等。
source必須是一個 typetimestamp，time，interval類型的值
表達式(類型為date的表達式將轉(zhuǎn)換為timestamp，因此也可以用)。
field是一個標識符或者字符串，它指定從源數(shù)據(jù)中抽取的域。
extract函數(shù)返回類型為 double precision 的數(shù)值。下列數(shù)值是有效數(shù)據(jù)域的名字：
- 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 世紀。 如果你認為這個不合理，那么請把抱怨發(fā)給：梵蒂岡，羅馬圣彼得教堂，教皇收
PostgreSQL8.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)
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); Result:5
請注意，
extract的周中天編號和to_char(...， 'D')函數(shù)不同。 - doy
-
一年的第幾天(1-365/366)
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40'); Result:47
- epoch
-
對于date和timestamp值而言，是自 1970-01-01 00:00:00-00 以來的秒數(shù) (結(jié)果可能是負數(shù))；對于interval值而言，它是時間間隔的總秒數(shù)。
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08'); 結(jié)果:982384720.12 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); 結(jié)果:442800
下面是把 epoch 值轉(zhuǎn)換回時間戳的方法：
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';
(The
to_timestampfunction encapsulates the above conversion.) - hour
-
The hour field (0 - 23)小時域(0-23)
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); 結(jié)果:20
- isodow
-
周中的第幾天 [1-7] 星期一：1) 星期天：(7)。
SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40'); 結(jié)果:7
dow除了星期天外，都相同。這與ISO8601標準周中的第幾天編碼相匹配。 matches theISO8601 day of the week numbering.
- isoyear
-
日期中的ISO8601標準年（不適用于間隔）。
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01'); 結(jié)果:2005 SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02'); 結(jié)果:2006
每個帶有星期一開始的周中包含1月4日的ISO年， 所以在年初的1月或12月下旬的ISO年可能會不同與陽歷的年。 見week獲取更多信息。
這個域不能用于 PostgreSQL 8.3之前的版本。
- microseconds
-
秒域(包括小數(shù)部分)乘以 1，000，000 。請注意它包括全部的秒。
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5'); 結(jié)果:28500000
- millennium
-
千年
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40'); 結(jié)果:3
20 世紀(19xx 年)里面的年份在第二個千年里。第三個千年從 2001 年 1 月 1 日零時開始。
PostgreSQL8.0 之前的版本并不遵循千年編號的習慣，只是返回年份除以 1000 。
- milliseconds
-
秒域(包括小數(shù)部分)乘以 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
-
Fortimestampvalues， the number of the month within the year (1 - 12) ; forintervalvalues the number of months， modulo 12 (0 - 11) 對于timestamp值，它是一年里的月份數(shù)(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
-
秒域，包括小數(shù)部分(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的時區(qū)偏移量，以秒記。正數(shù)對應 UTC 東邊的時區(qū)，負數(shù)對應 UTC 西邊的時區(qū)
- timezone_hour
時區(qū)偏移量的小時部分。
- timezone_minute
時區(qū)偏移量的分鐘部分。
- week
-
該天在所在的年份里是第幾周。 (ISO8601)定義一年的第一周包含該年的一月四日(ISO-8601 的周從星期一開始)。 換句話說，一年的第一個星期四在第一周。(只用于 timestamp 值)
因此，一月的頭幾天可能是前一年的第 52 或者第 53 周。 比如，2005-01-01是 2004 年的第 53 周，而2006-01-01是 2005 年的第 52 周
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函數(shù)主要的用途是運算。對于用于顯示的日期/時間數(shù)值格式化，見Section 9.8。
date_part函數(shù)是仿照在傳統(tǒng)的Ingres函數(shù)。等效于 SQL 標準函數(shù)extract：
date_part('field'，source)
Note that here thefieldparameter needs to
be a string value， not a name. The valid field names for
date_partare the same as for
extract.
請注意這里的field參數(shù)必須是一個字符串值，而不是一個名字。
date_part的有效域名參數(shù)和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函數(shù)在概念上和用于數(shù)字的trunc函數(shù)類似。
date_trunc('field'，source)
source是timestamp或interval類型的值表達式 (date和time類型的值都分別自動轉(zhuǎn)換成timestamp或interval)。 用field選擇對該時間戳值用什么樣的精度進行截斷。返回的數(shù)值 是timestamp或interval類型， 所有小于選定的精度的域都設置為零(日期和月份域則為 1)。
field的有效數(shù)值是：
| microseconds |
| milliseconds |
| second |
| minute |
| hour |
| day |
| week |
| month |
| quarter |
| year |
| decade |
| century |
| millennium |
例如：
SELECT date_trunc('hour'， TIMESTAMP '2001-02-16 20:38:40');
Result:2001-02-16 20:00:00
SELECT date_trunc('year'， TIMESTAMP '2001-02-16 20:38:40');
Result:2001-01-01 00:00:00
9.9.3. AT TIME ZONE
AT TIME ZONE構(gòu)造允許把時間戳轉(zhuǎn)換成不同的時區(qū)。表Table 9-28顯示了其變體。
Table 9-28. AT TIME ZONE變體
| 表達式 | 返回類型 | 描述 |
|---|---|---|
| timestamp without time zoneAT TIME ZONEzone | timestamp with time zone | 將不帶時區(qū)without time zone的時間戳轉(zhuǎn)換成給定時區(qū)的時間戳 |
| timestamp with time zoneAT TIME ZONEzone | timestamp without time zone | 將帶時區(qū)with time zone的時間戳轉(zhuǎn)換為不帶時區(qū)的時間。 |
| time with time zoneAT TIME ZONEzone | time with time zone | 將帶時區(qū)with time zone的時間轉(zhuǎn)換成給定時區(qū)的時間 |
在這些表達式里，zone可以聲明為文本串(比如'PST')或者一個 時間間隔(比如INTERVAL '-08:00')。 在文本的情況下，可用的時區(qū)名字在Section 8.5.3有詳細描述
Examples (assuming the local time zone isPST8PDT): 例子(假設本地時區(qū)是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
第一個例子接受一個無時區(qū)的時間戳然后把它解釋成 MST(UTC-7) 時間生成 UTC 時間戳，然后把這個時間轉(zhuǎn)換為 PST(UTC-8) 顯示。第二個例子接受一個 聲明為 EST(UTC-5) 的時間戳，然后把它轉(zhuǎn)換成 MST(UTC-7) 的當?shù)貢r間。
timezone(zone，timestamp)
函數(shù)等效于 SQL 兼容的構(gòu)造timestampAT TIME ZONEzone
9.9.4. 當前日期/時間
PostgreSQL提供許多返回當前日期和時間的函數(shù)。 這些符合 SQL 標準的函數(shù)全部都按照當前事務的開始時刻返回結(jié)果：
CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TIME(precision) CURRENT_TIMESTAMP(precision) LOCALTIME LOCALTIMESTAMP LOCALTIME(precision) LOCALTIMESTAMP(precision)
CURRENT_TIME和CURRENT_TIMESTAMP返回帶有時區(qū)的值；
LOCALTIME和LOCALTIMESTAMP返回不帶時區(qū)的值
CURRENT_TIME，
CURRENT_TIMESTAMP，
LOCALTIME和LOCALTIMESTAMP
可以有選擇地給予一個精度參數(shù)，該精度導致結(jié)果的秒數(shù)域四舍五入到指定小數(shù)位。
如果沒有精度參數(shù)，將給予所能得到的全部精度。
一些示例:
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
因為這些函數(shù)全部都按照當前事務的開始時刻返回結(jié)果，所以它們的值在事務運行的整個期間內(nèi)都不改變。 我們認為這是一個特性：目的是為了允許一個事務在"當前時間"上有連貫的概念， 這樣在同一個事務里的多個修改可以保持同樣的時間戳。
Note: 許多其它數(shù)據(jù)庫系統(tǒng)更頻繁地更新這些數(shù)值
PostgreSQL同樣也提供了返回實時時間值的函數(shù)， 它們的返回值會在事務中隨時間的前進而變化。 這些不附合 SQL 標準的函數(shù)列表如下:
transaction_timestamp() statement_timestamp() clock_timestamp() timeofday() now()
transaction_timestamp()等效于CURRENT_TIMESTAMP，不過其命名準確的表明了其
含義。statement_timestamp()返回當前事務開始時刻的時間戳(更準確的說是收到
客戶端最后一條命令的時間)。statement_timestamp()和transaction_timestamp()
在一個事務的第一條命令里返回值相同，但是在隨后的命令中卻不一定相同。
clock_timestamp()返回實時時鐘的當前時間戳，因此它的值甚至在同一條
SQL 命令中都會變化。timeofday()相當于clock_timestamp()，也返回實時
時鐘的當前時間戳，由于歷史原因， 它返回一個text字符串，而不是timestamp with time zone值。
now()是PostgreSQL的一個傳統(tǒng)的等效于transaction_timestamp()。
所有日期/時間類型還接受特殊的文本值now，用于聲明當前的日期和時間(重申：當前事務的開始時刻)。 因此，下面三個都返回相同的結(jié)果
SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT
Tip: 在創(chuàng)建表的時候你不應該用第三種形式聲明一個DEFAULT值。 系統(tǒng)將在分析這個常量的時候把now轉(zhuǎn)換為一個timestamp， 因此這個缺省值就會變成創(chuàng)建表的時間！而前兩種形式要到實際使用缺省值的時候才計算， 因為它們是函數(shù)調(diào)用。因此它們可以給出每次插入行的時刻。
9.9.5. 延時執(zhí)行
下面的這個函數(shù)可以用于讓服務器進程延時執(zhí)行：
pg_sleep(seconds)
pg_sleep讓當前的會話進程休眠seconds秒以后再執(zhí)行。
seconds是一個double precision類型的值，所以可以指定帶小數(shù)的秒數(shù)。
例如：
SELECT pg_sleep(1.5);
Note: 有效的休眠時間間隔精度是平臺相關的，通常 0.01 秒是通用的。休眠的時間將至少等于指定的時間， 也有可能由于服務器荷載較重等原因而比指定的時間長。
| Warning |
請確保調(diào)用 |
Notes
| [1] | 60 ，如果閏秒由操作系統(tǒng)實現(xiàn)。 |