2020年6月17日 星期三

[日期函數]日期/時間差

取得兩個日期(時間)的差距。

可直接將兩個日期進行減法運算(d1- d2),即可得到兩個日期的差距天數,ORACLE將傳回具有浮點數的天數,MSSQL則會將時間差轉換為相對於基準日期(1900-01-01 00:00:00)的日期(含時間)。
#
方法
ORACLE
MSSQL
1
d1 - d2
差距天數(數值型態-浮點數)
時分秒: 1.運算(246060)
2.轉換為INTERVAL型態
差距時間-相對基準日期(日期型態)
差距天數:1. 轉為數值型態
2. 改用DATEDIFF函數。
2
函數
MONTHS_BETWEEN(Edate, SDate)
DATEDIFF(Interval , SDate, EDate)


傳回以月為單位的差距
(可為小數)
傳回兩個日期間相差的時間間隔單位數目(帶正負號的整數

MSSQL
MSSQL提供DATEDIFF 函數,傳回兩個日期間相差的時間間隔單位(如年、季、月、日、時、分及秒)之數目(整數),語法如下:
語法
說明
DATEDIFF(Interval , SDate, EDate)
- 1st: 時間間隔單位(Interval: 年月日時分秒
- 2nd3rd: 目標日期
第一個引數為時間間隔單位,用以指定時間差異運算的時間間隔單位,參數如下表。
第二個引數、第三個引數為目標日期。
傳回值為兩時間的單位差異個數,此函數傳回值的資料型態為整數。
Interval
縮寫
yyyyyy
qqq
mmm
ddd
wkww
hh
min
sss
毫秒
ms

SQL
SELECT DT
   , DATEDIFF(DD, '2020-01-01 22:45', DT)              Diff
, CAST(CAST(DT as datetime) as float)               "DT(Float)"
, FLOOR(CAST(CAST(DT as datetime) as float))     "DT(Int)"
, CAST(
             CAST(DT as datetime)
- CAST('2020-01-01 22:45' AS datetime) 
               as float)                                                Ref
  --, CAST(DT as datetime)
- CAST('2020-01-01 22:40' AS datetime)    Ref1
FROM (VALUES ('2020-01-01 00:00')
                 , ('2020-01-01 22:45')
                 , ('2020-01-02 00:00')
         ) T (DT)
: 2020-01-01 22:45為比較基準點(Now)
結果

DT
Diff
DT(Float)
DT(Int)
Ref
1
2020/1/1 00:00
0
43829
43829
-0.947916666666667
2
2020/1/1 22:45
0
43829.9479166667
43829
0
3
2020/1/2 00:00
1
43830
43830
0.0520833333333333
0.0520833333333333 * 24 = 1.2499999999999992  (1小時)
0.2499999999999992 * 60 = 14.9999999999999520 (15分鐘)
: 本案例為呈現概念因此將時間差轉為浮點數後再以運算方式轉回時間產生誤差

首先建議先參考《[日期函數]日期/時間加減運算》文中對於DATEADD函數介紹,函數語法如下。
DATEADD (Interval , count, date)
第二個引數為增、減時間間隔單位的次數(Count),此值可為正數(未來),或負數(過去),但需特別注意,此值可接受浮點數,當指定的數值不是整數時,系統會自動捨棄值小數部分即可說明MSSQL日期運算概念。上表中以FLOOR函數進行捨位運算後,可得知:
(#2 / #1)與基準日期(1900-01-01)差距均為43829,兩者相減後為『0』,兩者時差為22:45
(#2 / #3)則分別為4382943830則兩差距為1單位()兩者時差為01:15
案例中兩者時間差雖1.25小時但已跨日DATEDIFF函數依定義傳回為1運算時先進行捨位再進行差異比較此即為MSSQL日期運算概念可能不符合一些需求原始想法,常見會將間隔單位降為『秒』再行運算比較。

ORACLE
ORALCE提供MONTHS_BETWEEN函數,此函數是以月為計算單位的時間差異函數,由於實際案例中鮮少使用(未曾使用)而不多作著墨,如需使用可自行參考官方文件。
本文主要探討兩個日期相減(d1 - d2)將可得到數值型態的差異天數(浮點數),再將「差異天數」以運算方式轉換成其他單位,或轉換為INTERVAL型態後再行運用,如下
SQL
SELECT TO_CHAR(NOW, 'YYYY-MM-DD HH24:MI:SS')    "Now"
     --, TO_CHAR(Target, 'YYYY-MM-DD')     "Target"
     , Target - Now                               "Diff"
     , (Target - Now) DAY(8) TO SECOND          INTVL
     , REGEXP_REPLACE(
TO_CHAR((Target - Now) DAY(8) TO SECOND)
, '(^.{1})(0*)(.+)(\.{1}.*$)'
, '\1\3'
           )                                       "INTVL(char)"                         
FROM
    (
    SELECT TO_DATE('202001012245', 'YYYYMMDDHH24MISS') Now
         , DATE'2020-01-02' Target
    FROM DUAL  
    )
: 2020-01-01 22:45為比較基準點(Now)
結果
Now
Diff
INTVL
INTVL(char)
2020-01-01 22:45:
0.0520833333333333
+00000000 01:15:00.000000
+ 01:15:00
0.0520833333333333 * 24 = 1.2499999999999992  (1小時)
0.2499999999999992 * 60 = 14.9999999999999520 (15分鐘)
ORACLE可將時間差(數值)轉換為INTERVAL資料型態(即與MSSQL運算結果雷同)再轉換為字元型態在此是使用REGEXP_REPLACE將額外字元剔除可參考[ORACLE]REGEXP_REPLACE函數使用


延伸閱讀:
函數
資料庫
延伸閱讀
REGEXP_REPLACE
ORACLE

MSSQL




沒有留言:

張貼留言