此文主要是為那些剛接觸 DB2 數(shù)據(jù)庫并想理解如何操作日期與時(shí)間的新手準(zhǔn)備的。以下就是具體方案的描述，希望在你今后的學(xué)習(xí)中會(huì)有所幫助。如果使用過其它數(shù)據(jù)庫的大部分人都會(huì)很驚喜地發(fā)現(xiàn)在 DB2 數(shù)據(jù)庫中操作日期和時(shí)間是多么簡(jiǎn)單。

以下就是文章的主要內(nèi)容的詳細(xì)描述，望大家在瀏覽之后會(huì)對(duì)其有更深的了解。

基礎(chǔ)

要使用 SQL 獲得當(dāng)前的日期、時(shí)間及時(shí)間戳記，請(qǐng)參考適當(dāng)?shù)?DB2 寄存器：

SELECT current date FROM sysibm.sysdummy1  
SELECT current time FROM sysibm.sysdummy1  
SELECT current timestamp FROM sysibm.sysdummy1 

sysibm.sysdummy1 表是一個(gè)特殊的內(nèi)存中的表，用它可以發(fā)現(xiàn)如上面演示的 DB2 數(shù)據(jù)庫寄存器的值。您也可以使用關(guān)鍵字 VALUES 來對(duì)寄存器或表達(dá)式求值。例如，在 DB2 命令行處理器（Command Line Processor，CLP）上，以下 SQL 語句揭示了類似信息：

 

VALUES current date  
VALUES current time  
VALUES current timestamp 

在余下的示例中，我將只提供函數(shù)或表達(dá)式，而不再重復(fù) SELECT ... FROM sysibm.sysdummy1 或使用 VALUES 子句。

 

要使當(dāng)前時(shí)間或當(dāng)前時(shí)間戳記調(diào)整到 GMT/CUT，則把當(dāng)前的時(shí)間或時(shí)間戳記減去當(dāng)前時(shí)區(qū)寄存器：

current time - current timezone  
current timestamp - current timezone 

給定了日期、時(shí)間或時(shí)間戳記，則使用適當(dāng)?shù)暮瘮?shù)可以單獨(dú)抽取出（如果適用的話）年、月、日、時(shí)、分、秒及微秒各部分：

 

YEAR (current timestamp)  
MONTH (current timestamp)  
DAY (current timestamp)  
HOUR (current timestamp)  
MINUTE (current timestamp)  
SECOND (current timestamp)  
MICROSECOND (current timestamp) 

從時(shí)間戳記單獨(dú)抽取出日期和時(shí)間也非常簡(jiǎn)單：

 

DATE (current timestamp)  
TIME (current timestamp) 

因?yàn)闆]有更好的術(shù)語，所以您還可以使用英語來執(zhí)行日期和時(shí)間計(jì)算：

 

current date + 1 YEAR  
current date + 3 YEARS + 2 MONTHS + 15 DAYS  
current time + 5 HOURS - 3 MINUTES + 10 SECONDS 

要計(jì)算兩個(gè)日期之間的天數(shù)，您可以對(duì)日期作減法，如下所示：

 

days (current date) - days (date('1999-10-22')) 

而以下示例描述了如何獲得微秒部分歸零的當(dāng)前時(shí)間戳記：

 

CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS 

如果想將日期或時(shí)間值與其它文本相銜接，那么需要先將該值轉(zhuǎn)換成字符串。為此，只要使用 CHAR() 函數(shù)：

 

char(current date)  
char(current time)  
char(current date + 12 hours) 

要將字符串轉(zhuǎn)換成日期或時(shí)間值，可以使用：

 

TIMESTAMP ('2002-10-20-12.00.00.000000')  
TIMESTAMP ('2002-10-20 12:00:00')  
DATE ('2002-10-20')  
DATE ('10/20/2002')  
TIME ('12:00:00')  
TIME ('12.00.00') 

TIMESTAMP()、DATE() 和 TIME() 函數(shù)接受更多種格式。上面幾種格式只是示例，我將把它作為一個(gè)練習(xí)，讓讀者自己去發(fā)現(xiàn)其它格式。

 

警告:

摘自 DB2 數(shù)據(jù)庫UDB V8.1 SQL Cookbook，作者 Graeme Birchall (see http://ourworld.compuserve.com/homepages/Graeme_Birchall).

 

如果你在日期函數(shù)中偶然地遺漏了引號(hào)，那將如何呢？結(jié)論是函數(shù)會(huì)工作，但結(jié)果會(huì)出錯(cuò)：

SELECT DATE(2001-09-22) FROM SYSIBM.SYSDUMMY1; 

結(jié)果:

 

 

======  
05/24/0006 

為什么會(huì)產(chǎn)生將近 2000 年的差距呢？當(dāng) DATE 函數(shù)得到了一個(gè)字符串作為輸入?yún)?shù)的時(shí)候，它會(huì)假定這是一個(gè)有效的 DB2 日期的表示，并對(duì)其進(jìn)行適當(dāng)?shù)剞D(zhuǎn)換。相反，當(dāng)輸入?yún)?shù)是數(shù)字類型時(shí)，函數(shù)會(huì)假定該參數(shù)值減 1 等于距離公元***天（0001-01-01）的天數(shù)。在上面的例子中，我們的輸入是 2001-09-22，被理解為 (2001-9)-22, 等于 1970 天，于是該函數(shù)被理解為 DATE(1970)。 #p#

日期函數(shù)

有時(shí)，您需要知道兩個(gè)時(shí)間戳記之間的時(shí)差。為此，DB2 數(shù)據(jù)庫提供了一個(gè)名為 TIMESTAMPDIFF() 的內(nèi)置函數(shù)。但該函數(shù)返回的是近似值，因?yàn)樗豢紤]閏年，而且假設(shè)每個(gè)月只有 30 天。以下示例描述了如何得到兩個(gè)日期的近似時(shí)差：

timestampdiff (<n>, char(  
timestamp('2002-11-30-00.00.00')-  
timestamp('2002-11-08-00.00.00'))) 

對(duì)于 <n>，可以使用以下各值來替代，以指出結(jié)果的時(shí)間單位：

 

1 = 秒的小數(shù)部分

2 = 秒

 

4 = 分

 

8 = 時(shí)

 

16 = 天

 

32 = 周

 

64 = 月

 

128 = 季度

 

256 = 年

 

當(dāng)日期很接近時(shí)使用 timestampdiff() 比日期相差很大時(shí)精確。如果需要進(jìn)行更精確的計(jì)算，可以使用以下方法來確定時(shí)差（按秒計(jì)）：

 

(DAYS(t1) - DAYS(t2)) * 86400 +   
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2)) 

為方便起見，還可以對(duì)上面的方法創(chuàng)建 SQL 用戶定義的函數(shù)：

 

CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)  
RETURNS INT  
RETURN (  
(DAYS(t1) - DAYS(t2)) * 86400 +   
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))  
)  
@ 

如果需要確定給定年份是否是閏年，以下是一個(gè)很有用的 SQL 函數(shù)，您可以創(chuàng)建它來確定給定年份的天數(shù)：

CREATE FUNCTION daysinyear(yr INT)  
RETURNS INT  
RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE   
CASE (mod(yr, 4)) WHEN 0 THEN   
CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END   
ELSE 365 END  
END)@ 

 

***，以下是一張用于日期操作的內(nèi)置函數(shù)表。它旨在幫助您快速確定可能滿足您要求的函數(shù)，但未提供完整的參考。有關(guān)這些函數(shù)的更多信息，請(qǐng)參考 SQL 參考大全。

 

SQL 日期和時(shí)間函數(shù)

DAYNAME 返回一個(gè)大小寫混合的字符串，對(duì)于參數(shù)的日部分，用星期表示這一天的名稱（例如，F(xiàn)riday）。

 

DAYOFWEEK 返回參數(shù)中的星期幾，用范圍在 1-7 的整數(shù)值表示，其中 1 代表星期日。

 

DAYOFWEEK_ISO 返回參數(shù)中的星期幾，用范圍在 1-7 的整數(shù)值表示，其中 1 代表星期一。

 

DAYOFYEAR 返回參數(shù)中一年中的第幾天，用范圍在 1-366 的整數(shù)值表示。

 

DAYS 返回日期的整數(shù)表示。

 

JULIAN_DAY 返回從公元前 4712 年 1 月 1 日（儒略日歷的開始日期）到參數(shù)中指定日期值之間的天數(shù)，用整數(shù)值表示。

 

MIDNIGHT_SECONDS 返回午夜和參數(shù)中指定的時(shí)間值之間的秒數(shù)，用范圍在 0 到 86400 之間的整數(shù)值表示。

 

MONTHNAME 對(duì)于參數(shù)的月部分的月份，返回一個(gè)大小寫混合的字符串（例如，January）。

 

TIMESTAMP_ISO 根據(jù)日期、時(shí)間或時(shí)間戳記參數(shù)而返回一個(gè)時(shí)間戳記值。

 

TIMESTAMP_FORMAT 從已使用字符模板解釋的字符串返回時(shí)間戳記。

 

TIMESTAMPDIFF 根據(jù)兩個(gè)時(shí)間戳記之間的時(shí)差，返回由***個(gè)參數(shù)定義的類型表示的估計(jì)時(shí)差。

 

TO_CHAR 返回已用字符模板進(jìn)行格式化的時(shí)間戳記的字符表示。TO_CHAR 是 VARCHAR_FORMAT 的同義詞。

 

TO_DATE 從已使用字符模板解釋過的字符串返回時(shí)間戳記。TO_DATE 是 TIMESTAMP_FORMAT 的同義詞。

 

WEEK 返回參數(shù)中一年的第幾周，用范圍在 1-54 的整數(shù)值表示。以星期日作為一周的開始。

 

WEEK_ISO 返回參數(shù)中一年的第幾周，用范圍在 1-53 的整數(shù)值表示。

 

改變?nèi)掌诟袷?/p>

在日期的表示方面，這也是我經(jīng)常碰到的一個(gè)問題。用于日期的缺省格式由數(shù)據(jù)庫的地區(qū)代碼決定，該代碼在數(shù)據(jù)庫創(chuàng)建的時(shí)候被指定。例如，我在創(chuàng)建數(shù)據(jù)庫時(shí)使用 territory=US 來定義地區(qū)代碼，則日期的格式就會(huì)像下面的樣子：

 

values current date  
1  
05/30/2003  
1 record(s) selected.  

也就是說，日期的格式是 MM/DD/YYYY. 如果想要改變這種格式，你可以通過綁定特定的 DB2 數(shù)據(jù)庫工具包來實(shí)現(xiàn). 其他被支持的日期格式包括: DEF 使用與地區(qū)代碼相匹配的日期和時(shí)間格式。

 

EUR 使用歐洲日期和時(shí)間的 IBM 標(biāo)準(zhǔn)格式。

 

ISO 使用國際標(biāo)準(zhǔn)組織（ISO）制訂的日期和時(shí)間格式。

 

JIS 使用日本工業(yè)標(biāo)準(zhǔn)的日期和時(shí)間格式。

 

LOC 使用與數(shù)據(jù)庫地區(qū)代碼相匹配的本地日期和時(shí)間格式。

 

USA 使用美國日期和時(shí)間的 IBM 標(biāo)準(zhǔn)格式。

 

在 Windows 環(huán)境下，要將缺省的日期和時(shí)間格式轉(zhuǎn)化成 ISO 格式（YYYY-MM-DD），執(zhí)行下列操作：

 

在命令行中，改變當(dāng)前目錄為 sqllib\bnd。

例如:

 

在 Windows 環(huán)境: c:\program files\IBM\sqllib\bnd

 

在 UNIX 環(huán)境: /home/db2inst1/sqllib/bnd

 

從操作系統(tǒng)的命令行界面中用具有 SYSADM 權(quán)限的用戶連接到數(shù)據(jù)庫:

db2 connect to DBNAME  
db2 bind @db2ubind.lst datetime ISO blocking all grant public 

(在你的實(shí)際環(huán)境中， 用你的數(shù)據(jù)庫名稱和想使用的日期格式分別來替換 DBNAME and ISO。)

 

現(xiàn)在，你可以看到你的數(shù)據(jù)庫已經(jīng)使用 ISO 作為日期格式了：

 

values current date  
1  
2003-05-30  
1 record(s) selected.  

定制日期/時(shí)間格式

在上面的例子中，我們展示了如何將 DB2 數(shù)據(jù)庫當(dāng)前的日期格式轉(zhuǎn)化成系統(tǒng)支持的特定格式。但是，如果你想將當(dāng)前日期格式轉(zhuǎn)化成定制的格式（比如‘yyyymmdd’），那又該如何去做呢？按照我的經(jīng)驗(yàn)，***的辦法就是編寫一個(gè)自己定制的格式化函數(shù)。

下面是這個(gè) UDF 的代碼:

create function ts_fmt(TS timestamp, fmt varchar(20))  
returns varchar(50)  
return  
with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as  
(  
select  
substr( digits (day(TS)),9),  
substr( digits (month(TS)),9) ,  
rtrim(char(year(TS))) ,  
substr( digits (hour(TS)),9),  
substr( digits (minute(TS)),9),  
substr( digits (second(TS)),9),  
rtrim(char(microsecond(TS)))  
from sysibm.sysdummy1  
)  
select  
case fmt  
when 'yyyymmdd'  
then yyyy || mm || dd  
when 'mm/dd/yyyy'  
then mm || '/' || dd || '/' || yyyy  
when 'yyyy/dd/mm hh:mi:ss'  
then yyyy || '/' || mm || '/' || dd || ' ' ||   
hh || ':' || mi || ':' || ss  
when 'nnnnnn'  
then nnnnnn  
else  
'date format ' || coalesce(fmt,' ') ||   
' not recognized.'  
end  
from tmp 

乍一看，函數(shù)的代碼可能顯得很復(fù)雜，但是在仔細(xì)研究之后，你會(huì)發(fā)現(xiàn)這段代碼其實(shí)非常簡(jiǎn)單而且很優(yōu)雅。最開始，我們使用了一個(gè)公共表表達(dá)式（CTE）來將一個(gè)時(shí)間戳記（***個(gè)輸入?yún)?shù)）分別剝離為單獨(dú)的時(shí)間元素。然后，我們檢查提供的定制格式（第二個(gè)輸入?yún)?shù)）并將前面剝離出的元素按照該定制格式的要求加以組合。

 

這個(gè)函數(shù)還非常靈活。如果要增加另外一種模式，可以很容易地再添加一個(gè) WHEN 子句來處理。在使用過程中，如果用戶提供的格式不符合任何在 WHEN 子句中定義的任何一種模式時(shí)，函數(shù)會(huì)返回一個(gè)錯(cuò)誤信息。

使用方法示例：

values ts_fmt(current timestamp,'yyyymmdd')  
'20030818'  
values ts_fmt(current timestamp,'asa')   
'date format asa not recognized.' 

總結(jié)

 

這些示例回答了我在日期和時(shí)間方面所遇到的最常見問題。如果讀者的反饋中認(rèn)為我應(yīng)該用更多示例來更新本文，那么我會(huì)那樣做的。（事實(shí)上，我已經(jīng)對(duì)本文更新了三次，不是嗎？我要感謝讀者的反饋。）

致謝

Bill Wilkins，DB2 Partner Enablement   
Randy Talsma  

免責(zé)聲明

本文包含樣本代碼。IBM 授予您（“被許可方”）使用這個(gè)樣本代碼的非專有的、版權(quán)免費(fèi)的許可證。然而，樣本代碼是以“按現(xiàn)狀”的基礎(chǔ)提供的，不附有任何形式的（不論是明示的，還是默示的）保證，包括對(duì)適銷性、適用于某特定用途或非侵權(quán)性的默示保證。IBM 及其許可方不對(duì)被許可方使用該軟件所導(dǎo)致的任何損失負(fù)責(zé)。

任何情況下，無論損失是如何發(fā)生的，也不管責(zé)任條款怎樣，IBM 或其許可方都不對(duì)由使用該軟件或不能使用該軟件所引起的收入的減少、利潤的損失或數(shù)據(jù)的丟失，或者直接的、間接的、特殊的、由此產(chǎn)生的、附帶的損失或懲罰性的損失賠償負(fù)責(zé)，即使 IBM 已經(jīng)被明確告知此類損害的可能性，也是如此。以上的相關(guān)內(nèi)容就是對(duì)DB2 基礎(chǔ)之日期和時(shí)間的使用的介紹，望你能有所收獲。

【編輯推薦】

1. DB2基本命令和知識(shí)新手敲門磚
2. DB2 V9.7新特征可以給你帶來哪些好處？
3. 如何輕松實(shí)現(xiàn)DB2自動(dòng)增長主鍵？
4. DB2常用函數(shù)與Oracle有什么不同？
5. DB2手工添加數(shù)據(jù)庫的實(shí)際操作方案描述