DB2基础:日期和时间的使用

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

VALUES current date
VALUES current time
VALUES current timestamp

current time - current timezone
current timestamp - current timezone

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)

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

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

CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS

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

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")

警告:
摘自 DB2 UDB V8.1 SQL Cookbook，作者 Graeme Birchall(see http://ourworld.compuserve.com/homepages/Graeme_Birchall).

如果你在日期函数中偶然地遗漏了引号，那将如何呢？结论是函数会工作，但结果会出错：

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

结果:

======
05/24/0006

为什么会产生将近 2000 年的差距呢？当 DATE 函数得到了一个字符串作为输入参数的时候，它会假定这是一个有效的 DB2 日期的表示，并对其进行适当地转换。相反，当输入参数是数字类型时，函数会假定该参数值减 1 等于距离公元第一天（0001-01-01）的天数。在上面的例子中，我们的输入是 2001-09-22，被理解为 (2001-9)-22, 等于 1970 天，于是该函数被理解为 DATE(1970)。

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

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

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

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)@

values current date
1
----------
05/30/2003

1 record(s) selected.

values current date
1
----------
2003-05-30

  1 record(s) selected.

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

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