Monday, November 30, 2020

Subtraction DATE from DATE in Oracle database - implementation feature or what ?

Recently I used lag function to find interval of creating new objects of the same type in the database. Particularly I was interested by the time interval between such creations. The original query was

SELECT
    file#
    ,creation_time
    , (creation_time - lag (creation_time) over (order by creation_time)) as interval
FROM
    v$datafile a
    , v$tablespace b
WHERE
    a.ts# = b.ts#
    AND b.name = 'XXX'
ORDER BY
    2 desc
/

It turned out that it is possible to use interval expression during subtraction :

SELECT
    file#
    ,creation_time
    , (creation_time - lag (creation_time) over (order by creation_time)) day (3) to second as interval
FROM
    v$datafile a
    , v$tablespace b
WHERE
    a.ts# = b.ts#
    AND b.name = 'BARS_739_ADD'
ORDER BY
    2 desc
/

The result is more presentable than in previous query. 

The Oracle SQL Language documentation says than the NUMBER datatype will be generated as result of "DATE - DATE" operation. With help dump function, I saw some mysterious (internal ?) datatype 14, not 2 (number datatype). You could also use such "feature" when subtracting DATE datatype values and if trunc or round functions are not of your suitable option.

Good Luck !

No comments:

Post a Comment