DB2中的日期和时间.docx
- 文档编号:11433415
- 上传时间:2023-03-01
- 格式:DOCX
- 页数:12
- 大小:24.28KB
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"(
对于
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》
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DB2 中的 日期 时间