DB2 日期時間相減

1.直接相減的結果

2.使用 DB2自有的系統函數(TIMESTAMPDIFF)

1.直接相減的結果
(END_TIME - START_TIME) = ????
(09:40:00 - 09:25:00)   = 1500

      10表示10秒，
     100表示 1分鐘，
    1000表示10分鐘，
   10000表示 1小時，
  100000表示10小時，
 1000000表示 1天
10000000表示10天
__--__--
日時分秒

2.使用 DB2自有的系統函數(TIMESTAMPDIFF)
用法：
  timestampdiff(<n>, char(Time1 - Time2)) 

  <n>，可填入以下的值，表示不同的輸出 
    1 = 毫秒(1/1000秒)
    2 = 秒  
    4 = 分  
    8 = 時  
   16 = 天  
   32 = 週  
   64 = 月  
  128 = 季  
  256 = 年   

例如(日期字串轉換)：

-- SQL syntax:
select timestampdiff(2, char(timestamp('2014-12-25 15:05:00') - timestamp('2014-12-25 14:53:00'))) from sysibm.sysdummy1

例如(完整 Datetime)：

-- SQL syntax:
select 
timestampdiff(2, char((current timestamp) - EDITTIME)) as diff,
(current timestamp) as NNN,
EDITTIME
from TRN_CARSTATESUB_N
  where DRIVEDATE = '2014-10-27'
  and CARNO = '92H'

例如(只有 time，需要補上 date)：

-- SQL syntax:
with TEMP1 as (
  select 
  CARNO, DRIVEDATE,
  (char(DRIVEDATE)|| ' ' || replace(char(STARTTIME), '.', ':')) as FULL_STARTTIME,
  (char(DRIVEDATE)|| ' ' || replace(char(ENDTIME), '.', ':')) as FULL_ENDTIME,
  STARTTIME, ENDTIME
  from TRN_CARSTATESUB_N
  where DRIVEDATE = '2014-10-27'
  and CARNO = '92H'
), TEMP2 as (
  select 
    CARNO, DRIVEDATE,
    timestamp(cast(FULL_STARTTIME as Varchar(20))) as STARTDATETIME,
    timestamp(cast(FULL_ENDTIME as Varchar(20))) as ENDDATETIME,
    STARTTIME, ENDTIME
  from TEMP1
)
  select 
    CARNO, DRIVEDATE,
    STARTDATETIME,
    ENDDATETIME,
    timestampdiff(2, char(STARTDATETIME - ENDDATETIME)) as diff,
    STARTTIME, ENDTIME
  from TEMP2