2002 年 11 月 01 日
這篇短文說(shuō)明了如何在 DB2 Universal Database for Windows、UNIX 和 Linux 平臺上使用 SQL 操作日期、時(shí)間和時(shí)間戳記。
重要：在閱讀本文之前請先看一下
免責聲明。
本文適用于 IBM? DB2? Universal Database? for Linux、UNIX? 和 Windows?
這篇短文是為那些剛接觸 DB2 并想理解如何操作日期和時(shí)間的新手而寫(xiě)的。使用過(guò)其它數據庫的大部分人都會(huì )很驚喜地發(fā)現在 DB2 中操作日期和時(shí)間是多么簡(jiǎn)單。
回頁(yè)首要使用 SQL 獲得當前的日期、時(shí)間及時(shí)間戳記，請參考適當的 DB2 寄存器：
SELECT current date FROM sysibm.sysdummy1 SELECT current time FROM sysibm.sysdummy1 SELECT current timestamp FROM sysibm.sysdummy1
sysibm.sysdummy1表是一個(gè)特殊的內存中的表，用它可以發(fā)現如上面演示的 DB2 寄存器的值。您也可以使用關(guān)鍵字 VALUES 來(lái)對寄存器或表達式求值。例如，在 DB2 命令行處理器（Command Line Processor，CLP）上，以下 SQL 語(yǔ)句揭示了類(lèi)似信息：
VALUES current date VALUES current time VALUES current timestamp
在余下的示例中，我將只提供函數或表達式，而不再重復 SELECT ... FROM sysibm.sysdummy1 或使用 VALUES 子句。
要使當前時(shí)間或當前時(shí)間戳記調整到 GMT/CUT，則把當前的時(shí)間或時(shí)間戳記減去當前時(shí)區寄存器：
current time - current timezone current timestamp - current timezone
給定了日期、時(shí)間或時(shí)間戳記，則使用適當的函數可以單獨抽取出（如果適用的話(huà)）年、月、日、時(shí)、分、秒及微秒各部分：
YEAR (current timestamp) MONTH (current timestamp) DAY (current timestamp) HOUR (current timestamp) MINUTE (current timestamp) SECOND (current timestamp) MICROSECOND (current timestamp)
從時(shí)間戳記單獨抽取出日期和時(shí)間也非常簡(jiǎn)單：
DATE (current timestamp) TIME (current timestamp)
因為沒(méi)有更好的術(shù)語(yǔ)，所以您還可以使用英語(yǔ)來(lái)執行日期和時(shí)間計算：
current date + 1 YEAR current date + 3 YEARS + 2 MONTHS + 15 DAYS current time + 5 HOURS - 3 MINUTES + 10 SECONDS
要計算兩個(gè)日期之間的天數，您可以對日期作減法，如下所示：
days (current date) - days (date('1999-10-22'))
而以下示例描述了如何獲得微秒部分歸零的當前時(shí)間戳記：
CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS
如果想將日期或時(shí)間值與其它文本相銜接，那么需要先將該值轉換成字符串。為此，只要使用 CHAR() 函數：
char(current date) char(current time) char(current date + 12 hours)
要將字符串轉換成日期或時(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() 函數接受更多種格式。上面幾種格式只是示例，我將把它作為一個(gè)練習，讓讀者自己去發(fā)現其它格式。
警告:
摘自 DB2 UDB V8.1 SQL Cookbook，作者 Graeme Birchall(see
http://ourworld.compuserve.com/homepages/Graeme_Birchall).
如果你在日期函數中偶然地遺漏了引號，那將如何呢？結論是函數會(huì )工作，但結果會(huì )出錯：
SELECT DATE(2001-09-22) FROM SYSIBM.SYSDUMMY1;
結果:
====== 05/24/0006
為什么會(huì )產(chǎn)生將近 2000 年的差距呢？當 DATE 函數得到了一個(gè)字符串作為輸入參數的時(shí)候，它會(huì )假定這是一個(gè)有效的 DB2 日期的表示，并對其進(jìn)行適當地轉換。相反，當輸入參數是數字類(lèi)型時(shí)，函數會(huì )假定該參數值減 1 等于距離公元第一天（0001-01-01）的天數。在上面的例子中，我們的輸入是 2001-09-22，被理解為 (2001-9)-22, 等于 1970 天，于是該函數被理解為 DATE(1970)。
回頁(yè)首有時(shí)，您需要知道兩個(gè)時(shí)間戳記之間的時(shí)差。為此，DB2 提供了一個(gè)名為 TIMESTAMPDIFF() 的內置函數。但該函數返回的是近似值，因為它不考慮閏年，而且假設每個(gè)月只有 30 天。以下示例描述了如何得到兩個(gè)日期的近似時(shí)差：
timestampdiff (<n>, char( timestamp('2002-11-30-00.00.00')- timestamp('2002-11-08-00.00.00')))
對于 <n>，可以使用以下各值來(lái)替代，以指出結果的時(shí)間單位：
1 = 秒的小數部分
2 = 秒
4 = 分
8 = 時(shí)
16 = 天
32 = 周
64 = 月
128 = 季度
256 = 年
當日期很接近時(shí)使用 timestampdiff() 比日期相差很大時(shí)精確。如果需要進(jìn)行更精確的計算，可以使用以下方法來(lái)確定時(shí)差（按秒計）：
(DAYS(t1) - DAYS(t2)) * 86400 + (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))
為方便起見(jiàn)，還可以對上面的方法創(chuàng )建 SQL 用戶(hù)定義的函數：
CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP) RETURNS INT RETURN ( (DAYS(t1) - DAYS(t2)) * 86400 + (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2)) ) @
如果需要確定給定年份是否是閏年，以下是一個(gè)很有用的 SQL 函數，您可以創(chuàng )建它來(lái)確定給定年份的天數：
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)@
最后，以下是一張用于日期操作的內置函數表。它旨在幫助您快速確定可能滿(mǎn)足您要求的函數，但未提供完整的參考。有關(guān)這些函數的更多信息，請參考 SQL 參考大全。
SQL 日期和時(shí)間函數
DAYNAME 返回一個(gè)大小寫(xiě)混合的字符串，對于參數的日部分，用星期表示這一天的名稱(chēng)（例如，Friday）。
DAYOFWEEK 返回參數中的星期幾，用范圍在 1-7 的整數值表示，其中 1 代表星期日。
DAYOFWEEK_ISO 返回參數中的星期幾，用范圍在 1-7 的整數值表示，其中 1 代表星期一。
DAYOFYEAR 返回參數中一年中的第幾天，用范圍在 1-366 的整數值表示。
DAYS 返回日期的整數表示。
JULIAN_DAY 返回從公元前 4712 年 1 月 1 日（儒略日歷的開(kāi)始日期）到參數中指定日期值之間的天數，用整數值表示。
MIDNIGHT_SECONDS 返回午夜和參數中指定的時(shí)間值之間的秒數，用范圍在 0 到 86400 之間的整數值表示。
MONTHNAME 對于參數的月部分的月份，返回一個(gè)大小寫(xiě)混合的字符串（例如，January）。
TIMESTAMP_ISO 根據日期、時(shí)間或時(shí)間戳記參數而返回一個(gè)時(shí)間戳記值。
TIMESTAMP_FORMAT 從已使用字符模板解釋的字符串返回時(shí)間戳記。
TIMESTAMPDIFF 根據兩個(gè)時(shí)間戳記之間的時(shí)差，返回由第一個(gè)參數定義的類(lèi)型表示的估計時(shí)差。
TO_CHAR 返回已用字符模板進(jìn)行格式化的時(shí)間戳記的字符表示。TO_CHAR 是 VARCHAR_FORMAT 的同義詞。
TO_DATE 從已使用字符模板解釋過(guò)的字符串返回時(shí)間戳記。TO_DATE 是 TIMESTAMP_FORMAT 的同義詞。
WEEK 返回參數中一年的第幾周，用范圍在 1-54 的整數值表示。以星期日作為一周的開(kāi)始。
WEEK_ISO 返回參數中一年的第幾周，用范圍在 1-53 的整數值表示。
回頁(yè)首在日期的表示方面，這也是我經(jīng)常碰到的一個(gè)問(wèn)題。用于日期的缺省格式由數據庫的地區代碼決定，該代碼在數據庫創(chuàng )建的時(shí)候被指定。例如，我在創(chuàng )建數據庫時(shí)使用 territory=US 來(lái)定義地區代碼，則日期的格式就會(huì )像下面的樣子：
values current date 1 ---------- 05/30/2003 1 record(s) selected.
也就是說(shuō)，日期的格式是 MM/DD/YYYY. 如果想要改變這種格式，你可以通過(guò)綁定特定的 DB2 工具包來(lái)實(shí)現. 其他被支持的日期格式包括:
DEF 使用與地區代碼相匹配的日期和時(shí)間格式。
EUR 使用歐洲日期和時(shí)間的 IBM 標準格式。
ISO 使用國際標準組織（ISO）制訂的日期和時(shí)間格式。
JIS 使用日本工業(yè)標準的日期和時(shí)間格式。
LOC 使用與數據庫地區代碼相匹配的本地日期和時(shí)間格式。
USA 使用美國日期和時(shí)間的 IBM 標準格式。
在 Windows 環(huán)境下，要將缺省的日期和時(shí)間格式轉化成 ISO 格式（YYYY-MM-DD），執行下列操作：
在命令行中，改變當前目錄為 sqllib\bnd 。
例如:
在 Windows 環(huán)境: c:\program files\IBM\sqllib\bnd
在 UNIX 環(huán)境: /home/db2inst1/sqllib/bnd
從操作系統的命令行界面中用具有 SYSADM 權限的用戶(hù)連接到數據庫: db2 connect to DBNAME db2 bind @db2ubind.lst datetime ISO blocking all grant public
(在你的實(shí)際環(huán)境中， 用你的數據庫名稱(chēng)和想使用的日期格式分別來(lái)替換 DBNAME and ISO。)
現在，你可以看到你的數據庫已經(jīng)使用 ISO 作為日期格式了：
values current date 1 ---------- 2003-05-30 1 record(s) selected.
回頁(yè)首在上面的例子中，我們展示了如何將 DB2 當前的日期格式轉化成系統支持的特定格式。但是，如果你想將當前日期格式轉化成定制的格式（比如‘yyyymmdd’），那又該如何去做呢？按照我的經(jīng)驗，最好的辦法就是編寫(xiě)一個(gè)自己定制的格式化函數。
下面是這個(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,' <null> ') || ' not recognized.' end from tmp
乍一看，函數的代碼可能顯得很復雜，但是在仔細研究之后，你會(huì )發(fā)現這段代碼其實(shí)非常簡(jiǎn)單而且很優(yōu)雅。最開(kāi)始，我們使用了一個(gè)公共表表達式（CTE）來(lái)將一個(gè)時(shí)間戳記（第一個(gè)輸入參數）分別剝離為單獨的時(shí)間元素。然后，我們檢查提供的定制格式（第二個(gè)輸入參數）并將前面剝離出的元素按照該定制格式的要求加以組合。
這個(gè)函數還非常靈活。如果要增加另外一種模式，可以很容易地再添加一個(gè) WHEN 子句來(lái)處理。在使用過(guò)程中，如果用戶(hù)提供的格式不符合任何在 WHEN 子句中定義的任何一種模式時(shí)，函數會(huì )返回一個(gè)錯誤信息。
使用方法示例：
values ts_fmt(current timestamp,'yyyymmdd') '20030818' values ts_fmt(current timestamp,'asa') 'date format asa not recognized.'
回頁(yè)首這些示例回答了我在日期和時(shí)間方面所遇到的最常見(jiàn)問(wèn)題。如果讀者的反饋中認為我應該用更多示例來(lái)更新本文，那么我會(huì )那樣做的。（事實(shí)上，我已經(jīng)對本文更新了三次，不是嗎？我要感謝讀者的反饋。）
