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

One comment

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 )

Google photo

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

Twitter picture

You are commenting using your Twitter 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.