DB2时间日期操作详解

这篇文章目的是让BD2的初学者了解BD2中的日期和时间的应用,相信使用过其它数据库的大部分人都会很惊喜地发现在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()函数接受更多种格式。上面几种格式只是示例,我将把它作为一个练习,让读者自己去发现其它格式。

 

如果在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 小节

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

©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值