Posted by 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” 
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
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 . 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.
Learning is not a spectator sport 
Love it!
Function name should be elapsed_seconds though. 😀
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.
A bit longer expression but probably easier to understand
Last one is not correct. Difference must be casted to date and not each argument separately.