DB2中的日期和时间.docx

1、DB2中的日期和时间DB2中的日期和时间（v 1.0）作者胡自贵审核分类技术文档子类更新时间2018年9月8日关键字DB2 时间戳 自定义函数摘要容灾应用上线前，老大说需要一个db2的日期转换函数，把timestamp类型的时间戳值转换为自己需要的格式yyyymmdd。乘这个机会，查找了相应的资料，编写本技术文档，主要介绍DB2中的日期和时间的应用，以及自定义函数的编写及编译，需求对大家有所帮助。主要适用环境IBM DB2 Universal Database for Linux、UNIX 和 Windows版本说明版本日期内容编写人V1.02007-3-8初稿胡自贵目 录DB2中的日期和时间

2、 1版本说明 2目 录 31 基础知识 42 日期函数 63 修改日期格式 84 自定义日期/时间格式 95 小结 136 参考资料： 131 基础知识为了用SQL语句得到当前的日期，时间和时间戳，可以使用相应的DB2寄存器：db2 “SELECT current date FROM sysibm.sysdummy1”结果：1 -03/08/2007 1 record(s) selected.db2 “SELECT current time FROM sysibm.sysdummy1”db2 “SELECT current timestamp FROM sysibm.sysdummy1”sys

3、ibm.sysdummy1表是一个在内存中特殊的表，可以使用上面的语句得到DB2寄存器的值。您也还可以用关键字VALUES来获取寄存器中的值。例如，在DB2命令行处理器中，可以用下面的SQL语句获取同样的信息：VALUES current dateVALUES current timeVALUES current timestamp结果1 -2007-03-08-11.32.57.076697 1 record(s) selected.在下面的示例中，我将只提供函数或表达式，而不再重复 SELECT . FROM sysibm.sysdummy1 或使用VALUES子句。要使当前时间或当前时间

4、戳调整到格林威治标准时间(GMT/CUT)，可以把当前的时间或时间戳减去当前时区寄存器：current time - current timezonecurrent timestamp - current timezone结果：1 -2007-03-08-03.36.41.156602 1 record(s) selected.给定了日期、时间或时间戳，则使用适当的函数抽取出(如果适用的话)年、月、日、时、分、秒及微秒各部分：YEAR(current timestamp)MONTH(current timestamp)DAY(current timestamp)HOUR(current tim

5、estamp)MINUTE(current timestamp)SECOND(current timestamp)MICROSECOND(current timestamp)从时间戳单独抽取出日期和时间：date(current timestamp)time(current timestamp)还可以使用英语(因为没有更好的术语)来执行日期和时间计算：current date + 1 YEARcurrent date + 3 YEARS + 2 MONTHS + 15 DAYScurrent time + 5 HOURS - 3 MINUTES + 10 SECONDS要计算两个日期之间相差的

6、天数，可以对日期作减法，例如：days(current date)-days(date(1999-10-22)返回值: 2694 而以下示例描述了如何获得微秒部分归零的当前时间戳记：CURRENT TIMESTAMP - MICROSECOND(current timestamp) MICROSECONDS返回值： 2007-03-08-14.23.34.000000如果想将日期或时间值与其它文本相衔接，那么需要先将该值转换成字符串。为此，可以方便地使用CHAR()函数：char(current date)char(current time)char(current date + 12 hou

7、rs)要将字符串转换成日期或时间值，可以使用：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/0

8、006为什么会有2000多年的差别呢？当日期DATE函数读取字符作为输入的时候，它会将它转换为相应的DB2日期；如果读取的是数字，就会把其转换为从0001-01-01日起到该数字的那一天，上面的例子中，2001-09-221970，也就是0001-01-01后的第1970天。2 日期函数有时，可能需要知道两个时间戳记之间的时差。为此，DB2 提供了一个名为TIMESTAMPDIFF()的内置函数。但该函数返回的是近似值，因为它不考虑闰年，而且假设每个月只有 30 天。以下示例描述了如何得到两个日期的近似时差：timestampdiff(,char(timestamp(2002-11-30-00

9、.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用户自定义函

10、数：CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)RETURNS INTRETURN ( (DAYS(t1) - DAYS(t2) * 86400 + (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2)如果需要确定给定年份是否是闰年，创建以下函数来确定给定年份的天数：CREATE FUNCTION daysinyear(yr INT)RETURNS INTRETURN (CASE (mod(yr, 400) WHEN 0 THEN 366 ELSE CASE (mod(yr, 4) WHEN

11、0 THEN CASE (mod(yr, 100) WHEN 0 THEN 365 ELSE 366 END ELSE 365 ENDEND)最后，以下是一张用于日期操作的内置函数表。它旨在帮助您快速确定可能满足您要求的函数，但未提供完整的参考。有关这些函数的更多信息，请参考SQL Reference。SQL日期和时间函数： DAYNAME :返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，Friday）。 DAYOFWEEK: 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期日。 DAYOFWEEK:_ISO 返回参数中的星期几，用范围在 1

12、-7 的整数值表示，其中 1 代表星期一。 DAYOFYEAR: 返回参数中一年中的第几天，用范围在 1-366 的整数值表示。DAYS: 返回日期的整数表示。 JULIAN_DAY: 返回从公元前 4712 年 1 月 1 日（儒略日历的开始日期）到参数中指定日期值之间的天数，用整数值表示。 MIDNIGHT_SECONDS: 返回午夜和参数中指定的时间值之间的秒数，用范围在 0 到 86400 之间的整数值表示。 MONTHNAME: 对于参数的月部分的月份，返回一个大小写混合的字符串（例如，January）。 TIMESTAMP_ISO: 根据日期、时间或时间戳记参数而返回一个时间戳记值

13、。 TIMESTAMP_FORMAT: 从已使用字符模板解释的字符串返回时间戳记。 TIMESTAMPDIFF: 根据两个时间戳记之间的时差，返回由第一个参数定义的类型表示的估计时差。 TO_CHAR: 返回已用字符模板进行格式化的时间戳记的字符表示。TO_CHAR: 是 VARCHAR_FORMAT 的同义词。 TO_DATE: 从已使用字符模板解释过的字符串返回时间戳记。TO_DATE 是 TIMESTAMP_FORMAT 的同义词。 WEEK: 返回参数中一年的第几周，用范围在 1-54 的整数值表示。以星期日作为一周的开始。 WEEK_ISO: 返回参数中一年的第几周，用范围在 1-5

14、3 的整数值表示。3 修改日期格式默认的日期格式由数据库的数据库国家地区代码(TERRITORY CODE)决定(数据库国家地区代码是在数据库创建时确定的)。例如，在创建数据库时由数据库国家地区代码US创建的，时间格式的输出如下： values current date1-03/08/20071 record(s) selected.即时间格式为DD/MM/YYYY。如果希望修改格式，您需要使用不同的时间格式重新联编DB2工具包。支持的格式有： DEF 使用和数据库国家地区代码相关的日期时间格式。 EUR 使用IBM欧洲标准日期时间格式。 ISO 使用ISO日期时间格式。 JIS 使用日本工业

15、标准日期时间格式。 LOC 使用和数据库国家地区代码结合的本地日期时间格式。 USA 使用IBM美国标准时间日期格式。使用下面的步骤修改时间日期格式为ISO格式(YYYY-MM-DD)：1. 在命令行下，更改到sqllibbnd目录。例如： 在Windows平台: c:program filesIBMsqllibbnd 在UNIX平台 : /home/db2inst1/sqllib/bnd 2以SYSADM组成员的身份连接数据库： db2 connect to 数据库名db2 bind db2ubind.lst datetime ISO blocking all grant public (实

16、际应用中，修改数据库名和期望的时间格式)上面工作完成后，可以看到日期格式变更为： values current date1-2007-03-081 record(s) selected.4 自定义日期/时间格式上面的例子，我们演示了如何修改DB2输出日期格式为那些本地化的格式。如何有需求，要求日期格式为YYYYMMDD，怎么办呢？最好的方法时写一个自定义的格式化函数：下面是我为了应付项目的需求，编写的用户自定义函数的例子（文件名为：ts2date.sql）：- NAME: ts2date.sql - PURPOSE: - - REVISIONS: - Ver Date Author Descr

17、iption - - - - - - 1.0 2007-3-6 huzg Created this function. - - NOTES: - desciption:- To create the SQL function:- 1. Connect to the database- 2. Enter the command db2 -td -v -f ts2date.sqldrop function ts2datecreate function ts2date(ts timestamp,fmt varchar(20)returns varchar(50)returnwith tmp (dd,

18、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 |

19、mm | dd when mm/dd/yyyy then mm | / | dd | / | yyyy when yyyy/mm/dd then yyyy | / | mm | / | dd when yyyy-mm-dd then yyyy | - | mm | - | dd 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. endfr

20、om tmpcommit这个公式乍看起来比较复杂，细看一下，您会发现它还是很简单易用的。首先，使用公共表表达式(Common Table Expression)将时间格式中每一个部分提取出来，然后根据用户提供的日期格式重新组装输出。这个函数很灵活，用户可以简单地添加WHEN子句来加上期望的日期格式。使用函数时，如果输入的日期格式没有，函数还可以输出出错信息。往数据库中创建该函数：db2 connect to 数据库名db2 -td -v -f ts2date.sql ok,现在你可以使用该函数了。例如：values ts2date(current timestamp,yyyymmdd)返回结果

21、：20070308values ts2date(current timestamp,mm/dd/yyyy)返回结果：03/08/2007values ts2date(current timestamp,yyyy/mm/dd)返回结果：2007/03/08values ts2date(current timestamp,yyyy-mm-dd)返回结果：2007-03-08values ts2date(current timestamp,yyyy/dd/mm hh:mi:ss)返回结果：2007/03/08 14:59:32values ts2date(current timestamp,nnnn

22、nn)返回结果：676137values ts2date(current timestamp,sad)返回结果：date format sad not recognized.5 小结以上对DB2中基本的日期和时间的基础知识和函数进行了简单的介绍，另外也编写了几个自定义的函数，符合自己日常的需求，相信对大家以后的工作中，会有用处。6 参考资料：DB2 UDB V8.1 SQL CookbookDB2 UDB SQL ReferenceDB2 Universal Database v8 for Linux, UNIX, and Windows Database Administration Certification Guide, 5th Edition