SQL Macros – complex SQL made easy. Elapsed time calculation

November 16, 2021

2 minutes

The more you play with SQL macros, the more you find simple but powerful use cases which will make life so much easier as a developer.

In the “good ol’ days” Smile we only had the DATE data type in the Oracle database, and finding the duration in seconds between two dates is a breeze, because subtracting two dates returns a numeric result as the number of DAYS difference. So with a simple multiplication the job is done.


SQL> select started, ended, (ended-started)*24*60*60
  2  from t1;

STARTED                              ENDED                                (ENDED-STARTED)*24*60*60
------------------------------------ ------------------------------------ ------------------------
15/11/2021 12:04:09                  16/11/2021 16:52:09                                    103680
15/11/2021 12:04:09                  15/11/2021 21:40:09                                     34560
15/11/2021 12:04:09                  15/11/2021 12:35:50                                      1901
15/11/2021 12:04:09                  15/11/2021 12:05:55                                       106
15/11/2021 12:04:09                  15/11/2021 12:06:00                                       111

Fast forward to the more modern TIMESTAMP range of data types, and suddenly things get more complicated. Now the difference is returned as an INTERVAL.


SQL> select started, ended, (ended-started) ela
  2  from   t;

STARTED                              ENDED                                ELA
------------------------------------ ------------------------------------ -------------------------------
15-NOV-21 12.04.09.000000 PM         16-NOV-21 04.52.09.000000 PM         +000000001 04:48:00.000000
15-NOV-21 12.04.09.000000 PM         15-NOV-21 09.40.09.000000 PM         +000000000 09:36:00.000000
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.35.50.000000 PM         +000000000 00:31:41.000000
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.05.55.000000 PM         +000000000 00:01:46.000000
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.06.00.123000 PM         +000000000 00:01:51.123000

If you want that result in seconds…well… you’re in for some fun Smile


SQL> select started, ended, extract(day from (ended-started))*86400+    --| W
  2                           extract(hour from (ended-started))*3600+  --| T
  3                           extract(minute from (ended-started))*60+  --| H
  4                           extract(second from (ended-started)) ela  --| !
  5  from t;

STARTED                              ENDED                                       ELA
------------------------------------ ------------------------------------ ----------
15-NOV-21 12.04.09.000000 PM         16-NOV-21 04.52.09.000000 PM             103680
15-NOV-21 12.04.09.000000 PM         15-NOV-21 09.40.09.000000 PM              34560
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.35.50.000000 PM               1901
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.05.55.000000 PM                106
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.06.00.123000 PM            111.123

There really isn’t a way to avoid this, but with 21c scalar SQL macros, you can at least reduce the effort to being only needing to do this just once, plus we get the additional benefit of easier to understand and maintain SQL code. All we need do is bundle that expression into a macro.


SQL> create or replace function elapsed(
  2    ts1 in timestamp,
  3    ts2 in timestamp
  4  ) return varchar2 sql_macro(scalar) is
  5  begin
  6    return '
  7  extract(day from (ts2-ts1))*86400+
  8  extract(hour from (ts2-ts1))*3600+
  9  extract(minute from (ts2-ts1))*60+
 10  extract(second from (ts2-ts1))';
 11  end;
 12  /

Function created.

And voila! The code is now much nicer.


SQL> select started, ended, elapsed(started,ended) ela from t;

STARTED                              ENDED                                       ELA
------------------------------------ ------------------------------------ ----------
15-NOV-21 12.04.09.000000 PM         16-NOV-21 04.52.09.000000 PM             103680
15-NOV-21 12.04.09.000000 PM         15-NOV-21 09.40.09.000000 PM              34560
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.35.50.000000 PM               1901
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.05.55.000000 PM                106
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.06.00.123000 PM            111.123

“Hold on” I hear you say. “Why not just have a virtual column?” Or perhaps “Just that expression in a view!”. Whilst that might be appropriate for some scenarios, think about (say) wanting the elapsed time between one of the columns and “systimestamp”. No more virtual column for you Smile . Or if you push systimestamp into a view definition, then you’re now locked into to only that value and nothing else.

SQL macros are going to be a game changer for better SQL.

4 responses to “SQL Macros – complex SQL made easy. Elapsed time calculation”

Rich Soule

November 16, 2021 at 10:04 pm

Love it!

Function name should be elapsed_seconds though. 😀

Reply
sqlmdx

December 5, 2021 at 1:02 am
Hi Connor. SQL Macros is super powerful and cool feature but this specific case it’s not really necessary if we leverage implicit data type conversions in Oracle.

If we simply want the difference for timestamps without milliseconds – i.e. in the same way if they were dates then we can convert the difference into date by using “+ sysdate – sysdate” trick – see ela_ts1. If, however, we want full precision (including milliseconds) then we can use the trick with mod to get subsecond part – see ela_ts0. Finally, if we want an expression to work for both date and timestamp then we need to apply to_timestamp once before extracting second.
```
with
  cte (ts1, ts2) as(select timestamp '2021-11-15 12:04:09.000000' ts1, timestamp '2021-11-15 12:06:00.123000' ts2 from dual),
  t as(select cast(ts1 as date) d1, cast(ts2 as date) d2, cte.* from cte)
select t.*,
       ts2 - ts1 ela_ts,
       (d2 - d1) * 24 * 60 * 60 ela_d,
       (ts2 - ts1 + sysdate - sysdate) * 86400 + mod(extract(second from ts2 - ts1), 1) ela_ts0,
       (ts2 - ts1 + sysdate - sysdate) * 86400 ela_ts1,
       (d2 - d1 + sysdate - sysdate) * 86400 + mod(extract(second from to_timestamp(d2) - d1), 1) ela_d0
from t;
```
So an expression “(x2 – x1 + sysdate – sysdate) * 86400 + mod(extract(second from to_timestamp(x2) – x1), 1)” returns exact difference in seconds regardless whether x1 and x2 are dates or timestamps.
Reply
1. sqlmdx
  
  December 5, 2021 at 5:25 am
  A bit longer expression but probably easier to understand
```
(cast(x2 as date) - cast(x1 as date)) * 86400 + mod(extract(second from to_timestamp(x2) - to_timestamp(x1)), 1)
```
  Reply
  1. sqlmdx
    
    December 5, 2021 at 7:02 pm
    
    Last one is not correct. Difference must be casted to date and not each argument separately.
    
    Reply