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.