DB2的日期和时间

1 基础知识
2 日期函数
3 修改日期格式
4 客户化日期/时间格式
5 小节

这篇文章目的是让DB2的初学者了解DB2中的日期和时间的应用，相信使用过其它数据库的大部分人都会很惊喜地发现在DB2中操作日期和时间是多么简单。
本文适用于 IBM DB2 Universal Database for Linux、UNIX 和 Windows。


1 基础知识
为了用SQL语句得到当前的日期，时间和时间戳，可以使用相应的DB2寄存器：

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

sysibm.sysdummy1表是一个在内存中特殊的表，可以使用上面的语句得到DB2寄存器的值。您也还可以用关键字VALUES来获取寄存器中的值。例如，在DB2命令行处理器中，可以用下面的SQL语句获取同样的信息：

VALUES current date
VALUES current time
VALUES current timestamp

在下面的示例中，我将只提供函数或表达式，而不再重复 SELECT ... FROM sysibm.sysdummy1 或使用VALUES子句。

要使当前时间或当前时间戳调整到格林威治标准时间(GMT/CUT)，可以把当前的时间或时间戳减去当前时区寄存器：

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()函数：

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

TIMESTAMP()、DATE()和TIME()函数接受更多种格式。上面几种格式只是示例，我将把它作为一个练习，让读者自己去发现其它格式。

警告:
引用自Graeme Birchall著《 DB2 UDB V8.1 SQL Cookbook》
(http://ourworld.compuserve.com/homepages/Graeme_Birchall).

如果在DATE函数中忘记加单引号会发生什么呢？函数依然可以执行，可是结果是错的：

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

结果：
======
05/24/0006

为什么会有2000多年的差别呢？
当日期DATE函数读取字符作为输入的时候，它会将它转换为相应的DB2日期；如果读取的是数字，就会把其转换为从0001-01-01日起到该数字的那一天，上面的例子中，2001-09-22＝1970，也就是0001-01-01后的第1970天。

2 日期函数
有时，您需要知道两个时间戳记之间的时差。为此，DB2 提供了一个名为TIMESTAMPDIFF()的内置函数。但该函数返回的是近似值，因为它不考虑闰年，而且假设每个月只有 30 天。以下示例描述了如何得到两个日期的近似时差：

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

对于 <n>，可以使用以下各值来替代，以指出结果的时间单位：

1 = 秒的小数部分
2 = 秒
4 = 分
8 = 时
16 = 天
32 = 周
64 = 月
128 = 季度
256 = 年

当日期很接近时使用timestampdiff()比日期相差很大时精确。如果需要进行更精确的计算，可以使用以下方法来确定时差（按秒计）：

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

为方便起见，还可以对上面的方法创建SQL用户自定义函数：

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

如果需要确定给定年份是否是闰年，这里有一个很有用的SQL函数，您可以创建它来确定给定年份的天数：
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)@

最后，以下是一张用于日期操作的内置函数表。它旨在帮助您快速确定可能满足您要求的函数，但未提供完整的参考。
有关这些函数的更多信息，请参考SQL Reference。

SQL日期和时间函数：


DAYNAME :返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，Friday）。
DAYOFWEEK: 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期日。
DAYOFWEEK:_ISO 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期一。
DAYOFYEAR: 返回参数中一年中的第几天，用范围在 1-366 的整数值表示。
DAYS: 返回日期的整数表示。
JULIAN_DAY: 返回从公元前 4712 年 1 月 1 日（儒略日历的开始日期）到参数中指定日期值之间的天数，用整数值表示。
MIDNIGHT_SECONDS: 返回午夜和参数中指定的时间值之间的秒数，用范围在 0 到 86400 之间的整数值表示。
MONTHNAME: 对于参数的月部分的月份，返回一个大小写混合的字符串（例如，January）。
TIMESTAMP_ISO: 根据日期、时间或时间戳记参数而返回一个时间戳记值。
TIMESTAMP_FORMAT: 从已使用字符模板解释的字符串返回时间戳记。
TIMESTAMPDIFF: 根据两个时间戳记之间的时差，返回由第一个参数定义的类型表示的估计时差。
TO_CHAR: 返回已用字符模板进行格式化的时间戳记的字符表示。TO_CHAR: 是 VARCHAR_FORMAT 的同义词。
TO_DATE: 从已使用字符模板解释过的字符串返回时间戳记。TO_DATE 是 TIMESTAMP_FORMAT 的同义词。
WEEK: 返回参数中一年的第几周，用范围在 1-54 的整数值表示。以星期日作为一周的开始。
WEEK_ISO: 返回参数中一年的第几周，用范围在 1-53 的整数值表示。

3 修改日期格式
我经常会收到关于日期格式的问题。默认的日期格式由数据库的数据库国家／地区代码(TERRITORY CODE)决定(数据库国家／地区代码是在数据库创建时确定的)。例如，在我的数据库时由数据库国家／地区代码US创建的，时间格式的输出如下：

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

1 record(s) selected.

即时间格式为DD/MM/YYYY。如果希望修改格式，您需要使用不同的时间格式重新联编DB2工具包。支持的格式有：

DEF 使用和数据库国家／地区代码相关的日期时间格式。
EUR 使用IBM欧洲标准日期时间格式。
ISO 使用ISO日期时间格式。
JIS 使用日本工业标准日期时间格式。
LOC 使用和数据库国家／地区代码结合的本地日期时间格式。
USA 使用IBM美国标准时间日期格式。


使用下面的步骤修改时间日期格式为ISO格式(YYYY-MM-DD)：

1. 在命令行下，更改到sqllib\bnd目录。
例如：
在Windows平台: c:\program files\IBM\sqllib\bnd
在UNIX平台 : /home/db2inst1/sqllib/bnd

2．以SYSADM组成员的身份连接数据库：
db2 connect to 数据库名
db2 bind @db2ubind.lst datetime ISO blocking all grant public

(您实际应用中，修改数据库名和期望的时间格式)

上面工作完成后，您可以看到日期格式变更为：

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

1 record(s) selected.

4 客户化日期/时间格式
上面的例子，我们演示了如何修改DB2输出日期格式为那些本地化的格式。如果您的客户希望日期格式为YYYYMMDD怎么办呢？最好的方法时写一个客户化的格式化函数：

下面时就时用户自定义函数的例子：
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


这个公式乍看起来比较复杂，细看一下，您会发现它还是很简单易用的。首先，使用公共表表达式(Common Table Expression)将时间格式中每一个部分提取出来，然后根据用户提供的日期格式重新组装输出。这个函数很灵活，用户可以简单地添加WHEN子句来加上期望的日期格式。使用函数时，如果输入的日期格式没有，函数还可以输出出错信息。

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

5 小节

这些示例回答了我在日期和时间方面所遇到的最常见问题。如果读者的反馈中认为我应该用更多示例来更新本文，那么我会那样做的。（事实上，我已经对本文更新了三次，感谢读者对本文的反馈。）