a picture of fish oil tablets spilling from a vitamin container

SQL Macros – complex SQL made easy. Elapsed time calculation

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” 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 PM         16-NOV-21 PM         +000000001 04:48:00.000000
15-NOV-21 PM         15-NOV-21 PM         +000000000 09:36:00.000000
15-NOV-21 PM         15-NOV-21 PM         +000000000 00:31:41.000000
15-NOV-21 PM         15-NOV-21 PM         +000000000 00:01:46.000000
15-NOV-21 PM         15-NOV-21 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 PM         16-NOV-21 PM             103680
15-NOV-21 PM         15-NOV-21 PM              34560
15-NOV-21 PM         15-NOV-21 PM               1901
15-NOV-21 PM         15-NOV-21 PM                106
15-NOV-21 PM         15-NOV-21 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 PM         16-NOV-21 PM             103680
15-NOV-21 PM         15-NOV-21 PM              34560
15-NOV-21 PM         15-NOV-21 PM               1901
15-NOV-21 PM         15-NOV-21 PM                106
15-NOV-21 PM         15-NOV-21 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.


  1. 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.

      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.

    1. 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)

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.