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

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

基礎

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

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

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

 

VALUES current date  
VALUES current time  
VALUES current timestamp 

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

 

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

current time - current timezone  
current timestamp - current timezone 

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

 

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

從時間戳記單獨抽取出日期和時間也非常簡單：

 

DATE (current timestamp)  
TIME (current timestamp) 

因為沒有更好的術(shù)語，所以您還可以使用英語來執(zhí)行日期和時間計算：

 

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

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

 

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

而以下示例描述了如何獲得微秒部分歸零的當前時間戳記：

 

CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS 

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

 

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

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

 

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ù)接受更多種格式。上面幾種格式只是示例，我將把它作為一個練習，讓讀者自己去發(fā)現(xiàn)其它格式。

 

警告:

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

 

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

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

結(jié)果:

 

 

======  
05/24/0006 

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

日期函數(shù)

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

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

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

 

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

2 = 秒

 

4 = 分

 

8 = 時

 

16 = 天

 

32 = 周

 

64 = 月

 

128 = 季度

 

256 = 年

 

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

 

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

為方便起見，還可以對上面的方法創(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))  
)  
@ 

如果需要確定給定年份是否是閏年，以下是一個很有用的 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ù)，但未提供完整的參考。有關這些函數(shù)的更多信息，請參考 SQL 參考大全。

 

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

DAYNAME 返回一個大小寫混合的字符串，對于參數(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ù)，用范圍在 0 到 86400 之間的整數(shù)值表示。

 

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

 

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

 

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

 

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

 

TO_CHAR 返回已用字符模板進行格式化的時間戳記的字符表示。TO_CHAR 是 VARCHAR_FORMAT 的同義詞。

 

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

 

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

 

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

 

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

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

 

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

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

 

EUR 使用歐洲日期和時間的 IBM 標準格式。

 

ISO 使用國際標準組織（ISO）制訂的日期和時間格式。

 

JIS 使用日本工業(yè)標準的日期和時間格式。

 

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

 

USA 使用美國日期和時間的 IBM 標準格式。

 

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

 

在命令行中，改變當前目錄為 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 

(在你的實際環(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.  

定制日期/時間格式

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

下面是這個 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ā)現(xiàn)這段代碼其實非常簡單而且很優(yōu)雅。最開始，我們使用了一個公共表表達式（CTE）來將一個時間戳記（***個輸入?yún)?shù)）分別剝離為單獨的時間元素。然后，我們檢查提供的定制格式（第二個輸入?yún)?shù)）并將前面剝離出的元素按照該定制格式的要求加以組合。

 

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

使用方法示例：

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

總結(jié)

 

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

致謝

Bill Wilkins，DB2 Partner Enablement   
Randy Talsma  

免責聲明

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

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

【編輯推薦】

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