Oracle Tips and Tricks — David Fitzjarrell

July 21, 2016

On An Average Day

Filed under: General — dfitzjarrell @ 09:18

"There are other advantages," continued the child. "For instance, if one rat were cornered by nine cats,
on the average, each cat would be ten percent rat and the rat would be ninety percent cat. If you happened
to be a rat, you can see how much nicer it would make things."
-- Norton Juster, The Phantom Tollbooth

Recently an interesting question appeared on “Ask Tom” with regard to the ‘average’ between two dates. Which is not saying it’s the ONLY interesting question on “Ask Tom” but it did catch my attention, notably the topic of the discussion. Why, on earth, would anyone want the ‘average’ of two dates? Digging a bit deeper it became obvious what the original poster wanted to accomplish. Let’s take that trip and see where it leads.

The average person doesn’t usually ask for the average between two dates; averages are computed on salaries, on distance, on temperatures, on volumes, on numbers, in general, not dates. If we look at what the ‘average’ would be between two dates, however, we find it’s the date smack-dab in the middle of the two dates supplied. Connor McDonald provided this solution to the question:


SQL> with t as
  2     ( select
  3         date '2015-09-01' d1,
  4         date '2015-12-07' d2
  5      from dual )
  6  select
  7    least(d1,d2) + abs(d1-d2)/2
  8  from t;

LEAST(D1,
---------
19-OCT-15

SQL>

which provides the correct answer given the date range specified. It’s not an ‘average’, really, it’s more like the interval middle value, but it can serve a purpose. Let’s say that Millicent Muggwumpp, super-star billing clerk, needs to determine the middle date for a billing cycle so invoices can be generated and sent to the various customers. The solution Connor provided (with some modification) can provide that information. Let’s ‘hack away’ at Connor’s code and see what we end up with:


SQL> with interval_middle as
  2     ( select
  3         to_date('&&1', 'RRRR-MM-DD') d1,
  4         to_date('&&2', 'RRRR-MM-DD') d2
  5      from dual )
  6  select
  7    least(d1,d2) + abs(d1-d2)/2
  8  from interval_middle;

LEAST(D1,
---------
15-JUN-16

SQL>

Looking at the changes it should be noted that replaceable parameters have been substituted for the hard-coded dates and a more robust explicit date conversion has been written, including the expected date string format. Millicent can now use this script and pass to it any valid date range and return the middle date of that range. [SQL*Plus allows us to shut off the parameter replacement message showing the original code and the current version with the parameters supplied, but we could easily allow that to be displayed:


SQL> with interval_middle as
  2     ( select
  3         to_date('&&1', 'RRRR-MM-DD') d1,
  4         to_date('&&2', 'RRRR-MM-DD') d2
  5      from dual )
  6  select
  7    least(d1,d2) + abs(d1-d2)/2
  8  from interval_middle;
old   3:        to_date('&&1', 'RRRR-MM-DD') d1,
new   3:        to_date('2016-06-01', 'RRRR-MM-DD') d1,
old   4:        to_date('&&2', 'RRRR-MM-DD') d2
new   4:        to_date('2016-06-29', 'RRRR-MM-DD') d2

LEAST(D1,
---------
15-JUN-16

SQL>

if confirmation of the passed-in date values is required.]

Of course Connor’s answer wasn’t the only one provided as Stew Ashton questioned the need for LEAST() and ABS() in the query:


No matter what date you start with, you either subtract a positive number from the later date or subtract a negative number from the earlier date.


alter session set nls_date_format='YYYY-MM-DD HH24:MI';

with t as (
  select date '2016-07-01' d1, date '2016-07-18' d2
  from dual
)
select d1 - (d1 - d2) / 2 result1,
       d2 - (d2 - d1) / 2 result2
from t;

RESULT1          RESULT2
---------------- ----------------
2016-07-09 12:00 2016-07-09 12:00

Modifying this code in the same manner as Connor’s was produces:


SQL> with t as (
  2    select to_date('&&1', 'RRRR-MM-DD') d1, to_date('&&2', 'RRRR-MM-DD') d2
  3    from dual
  4  )
  5  select d1 - (d1 - d2) / 2 result1,
  6         d2 - (d2 - d1) / 2 result2
  7  from t;

RESULT1          RESULT2
---------------- ----------------
2016-06-15 00:00 2016-06-15 00:00

SQL>

The same result is produced by both queries, so LEAST() and ABS() aren’t absolutely necessary. Choose the approach that provides a sufficient level of comfort to you.

Averaging dates may not be an obvious operation but it can be useful, if in a limited way. We all look at dates in unusual ways on occasion so what may seem illogical to you, at least on the face of it, may be completely and utterly logical to someone else. Who knows, Aunt Edna may ask you to find the date between Uncle Milton’s and Aunt Haggatha’s birthdays because she wants to ship a plaster cat to Guatemala. Stranger things have happened.

And that’s about average.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: