DB2中的日期和时间.docx
《DB2中的日期和时间.docx》由会员分享，可在线阅读，更多相关《DB2中的日期和时间.docx（12页珍藏版）》请在冰点文库上搜索。
DB2中的日期和时间
DB2中的日期和时间
（v1.0）
作者
胡自贵
审核
分类
技术文档
子类
更新时间
2018年9月8日
关键字
DB2时间戳自定义函数
摘要
容灾应用上线前，老大说需要一个db2的日期转换函数，把timestamp类型的时间戳值转换为自己需要的格式yyyymmdd。
乘这个机会，查找了相应的资料，编写本技术文档，主要介绍DB2中的日期和时间的应用，以及自定义函数的编写及编译，需求对大家有所帮助。
主要适用环境
IBMDB2UniversalDatabaseforLinux、UNIX和Windows
版本说明
版本
日期
内容
编写人
V1.0
2007-3-8
初稿
胡自贵
目录
DB2中的日期和时间1
版本说明2
目录3
1基础知识4
2日期函数6
3修改日期格式8
4自定义日期/时间格式9
5小结13
6参考资料：
13
1基础知识
为了用SQL语句得到当前的日期，时间和时间戳，可以使用相应的DB2寄存器：
db2“SELECTcurrentdateFROMsysibm.sysdummy1”
结果：
1
----------
03/08/2007
1record（s）selected.
db2“SELECTcurrenttimeFROMsysibm.sysdummy1”
db2“SELECTcurrenttimestampFROMsysibm.sysdummy1”
sysibm.sysdummy1表是一个在内存中特殊的表，可以使用上面的语句得到DB2寄存器的值。
您也还可以用关键字VALUES来获取寄存器中的值。
例如，在DB2命令行处理器中，可以用下面的SQL语句获取同样的信息：
VALUEScurrentdate
VALUEScurrenttime
VALUEScurrenttimestamp
结果
1
--------------------------
2007-03-08-11.32.57.076697
1record（s）selected.
在下面的示例中，我将只提供函数或表达式，而不再重复SELECT...FROMsysibm.sysdummy1或使用VALUES子句。
要使当前时间或当前时间戳调整到格林威治标准时间（GMT/CUT），可以把当前的时间或时间戳减去当前时区寄存器：
currenttime-currenttimezone
currenttimestamp-currenttimezone
结果：
1
--------------------------
2007-03-08-03.36.41.156602
1record（s）selected.
给定了日期、时间或时间戳，则使用适当的函数抽取出（如果适用的话）年、月、日、时、分、秒及微秒各部分：
YEAR"（currenttimestamp）"
MONTH"（currenttimestamp）"
DAY"（currenttimestamp）"
HOUR"（currenttimestamp）"
MINUTE"（currenttimestamp）"
SECOND"（currenttimestamp）"
MICROSECOND"（currenttimestamp）"
从时间戳单独抽取出日期和时间：
date"（currenttimestamp）"
time"（currenttimestamp）"
还可以使用英语（因为没有更好的术语）来执行日期和时间计算：
currentdate+1YEAR
currentdate+3YEARS+2MONTHS+15DAYS
currenttime+5HOURS-3MINUTES+10SECONDS
要计算两个日期之间相差的天数，可以对日期作减法，例如：
days"（currentdate）"-days"（date（'1999-10-22'））
返回值:
2694
而以下示例描述了如何获得微秒部分归零的当前时间戳记：
CURRENTTIMESTAMP-MICROSECOND"（currenttimestamp）"MICROSECONDS
返回值：
2007-03-08-14.23.34.000000
如果想将日期或时间值与其它文本相衔接，那么需要先将该值转换成字符串。
为此，可以方便地使用CHAR（）函数：
char（currentdate）
char（currenttime）
char（currentdate+12hours）
要将字符串转换成日期或时间值，可以使用：
TIMESTAMP（'2002-10-20-12.00.00.000000'）
TIMESTAMP（'2002-10-2012:
00:
00'）
DATE（'2002-10-20'）
DATE（'10/20/2002'）
TIME（'12:
00:
00'）
TIME（'12.00.00'）
TIMESTAMP（）、DATE（）和TIME（）函数接受更多种格式。
上面几种格式只是示例。
如果在DATE函数中忘记加单引号会发生什么呢？
函数依然可以执行，可是结果是错的：
SELECTDATE"（2001-09-22）"FROMSYSIBM.SYSDUMMY1;
结果：
======
05/24/0006
为什么会有2000多年的差别呢？
当日期DATE函数读取字符作为输入的时候，它会将它转换为相应的DB2日期；如果读取的是数字，就会把其转换为从0001-01-01日起到该数字的那一天，上面的例子中，2001-09-22＝1970，也就是0001-01-01后的第1970天。
2日期函数
有时，可能需要知道两个时间戳记之间的时差。
为此，DB2提供了一个名为TIMESTAMPDIFF（）的内置函数。
但该函数返回的是近似值，因为它不考虑闰年，而且假设每个月只有30天。
以下示例描述了如何得到两个日期的近似时差：
timestampdiff"（,char（timestamp（'2002-11-30-00.00.00'）-timestamp（'2002-11-08-00.00.00'）））"
对于，可以使用以下各值来替代，以指出结果的时间单位：
1=秒的小数部分
2=秒
4=分
8=时
16=天
32=周
64=月
128=季度
256=年
当日期很接近时使用timestampdiff（）比日期相差很大时精确。
如果需要进行更精确的计算，可以使用以下方法来确定时差（按秒计）：
（DAYS（t1）-DAYS（t2））*86400+（MIDNIGHT_SECONDS（t1）-MIDNIGHT_SECONDS（t2））
为方便起见，还可以对上面的方法创建SQL用户自定义函数：
CREATEFUNCTIONsecondsdiff（t1TIMESTAMP,t2TIMESTAMP）
RETURNSINT
RETURN（
（DAYS（t1）-DAYS（t2））*86400+（
MIDNIGHT_SECONDS（t1）-MIDNIGHT_SECONDS（t2））
）
@
如果需要确定给定年份是否是闰年，创建以下函数来确定给定年份的天数：
CREATEFUNCTIONdaysinyear（yrINT）
RETURNSINT
RETURN（CASE（mod（yr,400））WHEN0THEN366ELSE
CASE（mod（yr,4））WHEN0THEN
CASE（mod（yr,100））WHEN0THEN365ELSE366END
ELSE365END
END）@
最后，以下是一张用于日期操作的内置函数表。
它旨在帮助您快速确定可能满足您要求的函数，但未提供完整的参考。
有关这些函数的更多信息，请参考SQLReference。
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修改日期格式
默认的日期格式由数据库的数据库国家／地区代码（TERRITORYCODE）决定（数据库国家／地区代码是在数据库创建时确定的）。
例如，在创建数据库时由数据库国家／地区代码US创建的，时间格式的输出如下：
valuescurrentdate
1
----------
03/08/2007
1record（s）selected.
即时间格式为DD/MM/YYYY。
如果希望修改格式，您需要使用不同的时间格式重新联编DB2工具包。
支持的格式有：
✓DEF使用和数据库国家／地区代码相关的日期时间格式。
✓EUR使用IBM欧洲标准日期时间格式。
✓ISO使用ISO日期时间格式。
✓JIS使用日本工业标准日期时间格式。
✓LOC使用和数据库国家／地区代码结合的本地日期时间格式。
✓USA使用IBM美国标准时间日期格式。
使用下面的步骤修改时间日期格式为ISO格式（YYYY-MM-DD）：
1.在命令行下，更改到sqllib\bnd目录。
例如：
在Windows平台:
c:
\programfiles\IBM\sqllib\bnd
在UNIX平台:
/home/db2inst1/sqllib/bnd
2．以SYSADM组成员的身份连接数据库：
db2connectto数据库名
db2bind@db2ubind.lstdatetimeISOblockingallgrantpublic
（实际应用中，修改数据库名和期望的时间格式）
上面工作完成后，可以看到日期格式变更为：
valuescurrentdate
1
----------
2007-03-08
1record（s）selected.
4自定义日期/时间格式
上面的例子，我们演示了如何修改DB2输出日期格式为那些本地化的格式。
如何有需求，要求日期格式为YYYYMMDD，怎么办呢？
最好的方法时写一个自定义的格式化函数：
下面是我为了应付项目的需求，编写的用户自定义函数的例子（文件名为：
ts2date.sql）：
----------------------------------------------------------------------------------------
--NAME:
ts2date.sql
--PURPOSE:
--
--REVISIONS:
--VerDateAuthorDescription
------------------------------------------------------------------------
--1.02007-3-6huzgCreatedthisfunction.
--
--NOTES:
--desciption:
--TocreatetheSQLfunction:
--1.Connecttothedatabase
--2.Enterthecommand"db2-td@-v-fts2date.sql"
dropfunctionts2date@
createfunctionts2date（tstimestamp,fmtvarchar（20））
returnsvarchar（50）
return
withtmp（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）））
fromsysibm.sysdummy1
）
select
casefmt
when'yyyymmdd'
thenyyyy||mm||dd
when'mm/dd/yyyy'
thenmm||'/'||dd||'/'||yyyy
when'yyyy/mm/dd'
thenyyyy||'/'||mm||'/'||dd
when'yyyy-mm-dd'
thenyyyy||'-'||mm||'-'||dd
when'yyyy/dd/mmhh:
mi:
ss'
thenyyyy||'/'||mm||'/'||dd||''||hh||':
'||mi||':
'||ss
when'nnnnnn'
thennnnnnn
else
'dateformat'||coalesce（fmt,''）||'notrecognized.'
end
fromtmp@
commit@
这个公式乍看起来比较复杂，细看一下，您会发现它还是很简单易用的。
首先，使用公共表表达式（CommonTableExpression）将时间格式中每一个部分提取出来，然后根据用户提供的日期格式重新组装输出。
这个函数很灵活，用户可以简单地添加WHEN子句来加上期望的日期格式。
使用函数时，如果输入的日期格式没有，函数还可以输出出错信息。
往数据库中创建该函数：
db2connectto数据库名
db2-td@-v-fts2date.sql
ok,现在你可以使用该函数了。
例如：
valuests2date"（currenttimestamp,'yyyymmdd'）"
返回结果：
20070308
valuests2date"（currenttimestamp,'mm/dd/yyyy'）"
返回结果：
03/08/2007
valuests2date"（currenttimestamp,'yyyy/mm/dd'）"
返回结果：
2007/03/08
valuests2date"（currenttimestamp,'yyyy-mm-dd'）"
返回结果：
2007-03-08
valuests2date"（currenttimestamp,'yyyy/dd/mmhh:
mi:
ss'）"
返回结果：
2007/03/0814:
59:
32
valuests2date"（currenttimestamp,'nnnnnn'）"
返回结果：
676137
valuests2date"（currenttimestamp,'sad'）"
返回结果：
dateformatsadnotrecognized.
5小结
以上对DB2中基本的日期和时间的基础知识和函数进行了简单的介绍，另外也编写了几个自定义的函数，符合自己日常的需求，相信对大家以后的工作中，会有用处。
6参考资料：
《DB2UDBV8.1SQLCookbook》
《DB2UDBSQLReference》
《DB2UniversalDatabasev8forLinux,UNIX,andWindowsDatabaseAdministrationCertificationGuide,5thEdition》
升级会员 