DB2 关于时间的内容及函数

select year(current_timestamp)||'-'||month(current_timestamp)||'-'||day(current_timestamp)||' '||hour(current_timestamp)||':'||minute(current_timestamp)||':'||second(current_timestamp) from sysibm.sysdummy1
//毫秒
select char(current_timestamp),MICROSECOND(current_timestamp)from sysibm.sysdummy1;

select current_timestamp from sysibm.sysdummy1;   2019-11-15 09:39:45
select current_date from sysibm.sysdummy1;   2019-11-15
select current_time from sysibm.sysdummy1;    09:40:39

select to_char(to_date('2019-11-15 15:46:03','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from sysibm.sysdummy1
select to_date('2019-11-15 15:46:03','yyyy-mm-dd hh24:mi:ss') from sysibm.sysdummy1

DB2也有TO_CHAR 和 TO_DATE函数，但只能提供固定的转换格式，而且数据类型是timestamp，如下：

TO_CHAR (timestamp_expression,'YYY-MM-DD HH24:MI:SS')
TO_DATE (string_expression, 'YYY-MM-DD HH24:MI:SS')

如果要转换date的格式，直接用CHAR()函数，如下：

CHAR(date_expression,ISO)
CHAR(time_expression,ISO)

SELECT current_date FROM sysibm.sysdummy1;  当前日期时间
SELECT current_time FROM sysibm.sysdummy1;
SELECT current_timestamp FROM sysibm.sysdummy1;

给定了日期、时间或时间戳记，则使用适当的函数可以单独抽取出（如果适用的话）年、月、日、时、分、秒及微秒各部分：

YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)

因为没有更好的术语，所以您还可以使用英语来执行日期和时间计算：

current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS

如果想将日期或时间值与其它文本相衔接，那么需要先将该值转换成字符串。为此，只要使用 CHAR() 函数：

values char(current_date)||'   '||char(current_time)

char(current date)
char(current time)
char(current date + 12 hours)

要将字符串转换成日期或时间值，还可以使用：

select microsecond(timestamp('2019-11-15-10.55.15.027757')) from sysibm.sysdummy1;   27757
select  (time('2019-11-15-10.55.15.027757')) from sysibm.sysdummy1;     10:55:15
select date(current_timestamp) from sysibm.sysdummy1;   2019-11-15

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

TO_CHAR(A.EVENT_CREATE_TIME,'YYY-MM-DD HH24:MI:SS') between TO_CHAR(current_date - 1 DAY,'YYYY-MM-DD')     ||' 08:00:00' and TO_CHAR(current_date,'YYYY-MM-DD')||' 07:59:59'

要计算两个日期之间的天数，您可以对日期作减法，如下所示：

days (current date) - days (date('1999-10-22'))
select days('2019-11-12') - days('2019-11-11') from sysibm.sysdummy1;          1

--计算两个时间戳记之间的时差： 
例子   values  timestampdiff(1,char(timestamp('2019-11-15-10.55.15.027760')-timestamp('2019-11-15-10.55.15.027750'))) 

--秒的小数部分为单位　 
values　timestampdiff(1,char(current　timestamp　-　timestamp('2010-01-01-00.00.00')));　 
--秒为单位　 
values　timestampdiff(2,char(current　timestamp　-　timestamp('2010-01-01-00.00.00')));　 
--分为单位　 
values　timestampdiff(4,char(current　timestamp　-　timestamp('2010-01-01-00.00.00')));　 
--小时为单位　 
values　timestampdiff(8,char(current　timestamp　-　timestamp('2010-01-01-00.00.00')));　 
--天为单位　 
values　timestampdiff(16,char(current　timestamp　-　timestamp('2010-01-01-00.00.00')));　 
--周为单位　 
values　timestampdiff(32,char(current　timestamp　-　timestamp('2010-01-01-00.00.00')));　 
--月为单位　 
values　timestampdiff(64,char(current　timestamp　-　timestamp('2010-01-01-00.00.00')));　 
--季度为单位　 
values　timestampdiff(128,char(current　timestamp　-　timestamp('2010-01-01-00.00.00')));　 
--年为单位　 
values timestampdiff(256,char(current timestamp - timestamp('2010-01-01-00.00.00')));