Guru: Elapsed Time For Human Beings
August 2, 2021 Ted Holt
Quick! Think of a weird animal! Perhaps you thought of the platypus, a duck-billed mammal that lays eggs. Maybe Dr. Doolittle’s pushmi-pullyu jumped to mind. Maybe you thought of a politician or a musician or your next-door neighbor. I know a weird animal that you probably didn’t think of.
The weird animal I have in mind is called the duration. This animal is found in SQL queries. There are three species: timestamp, date, and time. Today I write about the challenges of interacting with this strange entity. I’ll use the timestamp duration as an example. Interacting with date and time durations works along the same principles.
This story contains code, which you can download here.
The first thing to understand about a duration is its anatomy. A timestamp duration is a 20-digit number with six decimal positions, but weirdly, this number is not a quantity. If you take it at face value, it is meaningless. I’ll give you an example, but first let me create a table for the queries.
declare global temporary table Opers
( Order dec(5),
Operation dec(3),
Transaction char(6),
Stamp timestamp);
insert into session.Opers values
( 233, 1, 'JOBON', '2021-08-02-08.05.11.259322'),
( 234, 1, 'JOBON', '2021-08-02-08.06.41.112398'),
( 235, 1, 'JOBON', '2021-08-02-10.17.29.382983'),
( 236, 1, 'JOBON', '2021-08-02-13.22.52.329189'),
( 233, 1, 'JOBOFF', '2021-08-02-08.55.28.208372'),
( 233, 2, 'JOBON', '2021-08-02-09.15.46.589923'),
( 234, 1, 'JOBOFF', '2021-08-02-11.34.18.408323'),
( 234, 2, 'JOBON', '2021-08-02-12.32.57.829328'),
( 235, 1, 'JOBOFF', '2021-08-02-10.20.35.789236'),
( 236, 1, 'JOBOFF', '2021-08-03-02.18.28.109434'),
( 233, 2, 'JOBOFF', '2021-08-02-16.17.26.199384'),
( 234, 2, 'JOBOFF', '2021-08-04-03.29.27.949213');
This simple table is a log of manufacturing operations. When an operator begins to work on a part, the system logs a job-on transaction. When the operator finishes working on that part, the system logs a job-off transaction. Let’s ask the system how long each operation took to complete.
select on.Order, on.Operation,
on.Stamp as JobOnTime,
off.Stamp as JobOffTime,
off.Stamp - on.Stamp as Elapsed
from session.Opers as on
join session.Opers as off
on on .Transaction = 'JOBON'
and off.Transaction = 'JOBOFF'
and (on.Order, on.Operation) = (off.Order, off.Operation)
order by on.Order, on.Stamp;
Here is the result set.
Order Oper JobOnTime JobOffTime Elapsed 233 1 2021-08-02 08:05:11.259322 2021-08-02 08:55:28.208372 5016.949050 233 2 2021-08-02 09:15:46.589923 2021-08-02 16:17:26.199384 70139.609461 234 1 2021-08-02 08:06:41.112398 2021-08-02 11:34:18.408323 32737.295925 234 2 2021-08-02 12:32:57.829328 2021-08-04 03:29:27.949213 1145630.119885 235 1 2021-08-02 10:17:29.382983 2021-08-02 10:20:35.789236 306.406253 236 1 2021-08-02 13:22:52.329189 2021-08-03 02:18:28.109434 125535.780245
The duration is in the rightmost column. It is the result of subtracting the JobOnTime from the JobOffTime, but it’s not in hours, minutes, dog years, shakes of a lamb’s tale or any other unit of time that you’ve ever heard of.
The 20-digit number is divided like this:
| Beginning position | Ending position | Meaning |
| 1 | 4 | Years |
| 5 | 6 | Months |
| 7 | 8 | Days |
| 9 | 10 | Hours |
| 11 | 12 | Minutes |
| 13 | 14 | Seconds |
| 15 | 20 | Microseconds |
Therefore, the value 1145630.119885 means 0 years, 0 months, 1 day, 14 hours, 56 minutes and 30.119885 seconds.
I got tired of deconstructing such values, so I searched the Web to see how people derive meaningful information from such data. I was disappointed to find very little of practicality, as each relational DBMS has its own approach to dealing with dates and times.
One common approach is to retrieve the difference in seconds and then apply a lot of mathematical operations to convert seconds to something more germane. The TIMESTAMPDIFF function can handle this requirement.
With JobData as
(select on.Order, on.Operation,
on.Stamp as JobOnTime,
off.Stamp as JobOffTime,
timestampdiff(2, cast (off.Stamp - on.Stamp as char(22)))
as JobSeconds
from session.Opers as on
join session.Opers as off
on on .Transaction = 'JOBON'
and off.Transaction = 'JOBOFF'
and (on.Order, on.Operation) = (off.Order, off.Operation))
select jd.Order, jd.Operation, jd.JobOnTime,
jd.JobSeconds / 3600 as Hours,
(mod(jd.JobSeconds, 3600) / 60) as Minutes,
mod(jd.JobSeconds, 60) as Seconds
from JobData as jd
order by jd.Order, jd.JobOnTime;
Here’s the result set.
Order Operation JobOnTime Hours Minutes Seconds 233 1 2021-08-02 08:05:11.259322 0 50 16 233 2 2021-08-02 09:15:46.589923 7 1 39 234 1 2021-08-02 08:06:41.112398 3 27 37 234 2 2021-08-02 12:32:57.829328 38 56 30 235 1 2021-08-02 10:17:29.382983 0 3 6 236 1 2021-08-02 13:22:52.329189 12 55 35
It works, but I am not a big fan of division and modulo operations. Not only are they messy, I think they tend to degrade performance. Besides, they won’t work for extracting year, month, and date. Furthermore, TIMESTAMPDIFF can only yield an approximate duration because not all years and months have the same number of days.
You can do the same sort of thing with substrings.
With JobOn as
(select a.Order, a.Operation, a.Stamp as JobOnTime
from session.Opers as a
where a.Transaction = 'JOBON' ),
JobOff as
(select b.Order, b.Operation, b.Stamp as JobOffTime
from session.Opers as b
where b.Transaction = 'JOBOFF'),
Combined as
(select on.Order, on.Operation, on.JobOnTime, off.JobOffTime,
cast (off.JobOffTime - on.JobOnTime as numeric (20, 6)) as Elapsed
from JobOn as on
inner join JobOff as off
on (on.Order, on.Operation) = (off.Order, off.Operation))
select c.Order, c.Operation, c.JobOnTime,
dec(substr(digits(c.Elapsed), 1, 4), 4, 0) as Years,
dec(substr(digits(c.Elapsed), 5, 2), 2, 0) as Months,
dec(substr(digits(c.Elapsed), 7, 2), 2, 0) as Days,
dec(substr(digits(c.Elapsed), 9, 2), 2, 0) as Hours,
dec(substr(digits(c.Elapsed), 11, 2), 2, 0) as Minutes,
dec(substr(digits(c.Elapsed), 13, 2), 2, 0) as Seconds
from Combined as c
order by c.Order, c.JobOnTime
Order Oper JobOnTime Years Months Days Hours Min Sec 233 1 2021-08-02 08:05:11.259322 0 0 0 0 50 16 233 2 2021-08-02 09:15:46.589923 0 0 0 7 1 39 234 1 2021-08-02 08:06:41.112398 0 0 0 3 27 37 234 2 2021-08-02 12:32:57.829328 0 0 1 14 56 30 235 1 2021-08-02 10:17:29.382983 0 0 0 0 3 6 236 1 2021-08-02 13:22:52.329189 0 0 0 12 55 35
While I prefer this to the previous method, I don’t consider it orders of magnitude better.
I pondered the challenge and finally come up with something I consider more practical. If nothing else, it’s easier to read than a raw duration.
My idea is that two units of measurement generally suffice to provide practical information. For instance, knowing that a person is 21 years old is practical. In some cases I may need to know that a person is 21 years and 8 months old. But rarely would I need to know that a person is 21 years, 8 months, and 17 days old. It would even rarer to need to know the number of hours, minutes and seconds as well.
I wrote an SQL function that gives me two units of measure in plain English. I call it DIFF because I couldn’t think of a better name.
With JobOn as
(select a.Order, a.Operation, a.Stamp as JobOnTime
from session.Opers as a
where a.Transaction = 'JOBON' ),
JobOff as
(select b.Order, b.Operation, b.Stamp as JobOffTime
from session.Opers as b
where b.Transaction = 'JOBOFF')
select on.Order, on.Operation, on.JobOnTime,
diff (off.JobOffTime - on.JobOnTime) as Elapsed
from JobOn as on
inner join JobOff as off
on (on.Order, on.Operation) = (off.Order, off.Operation)
order by on.Order, on.JobOnTime
Order Operation JobOn Elapsed 233 1 2021-08-02 08:05:11.259322 50 minutes 16 seconds 233 2 2021-08-02 09:15:46.589923 7 hours 1 minute 234 1 2021-08-02 08:06:41.112398 3 hours 27 minutes 234 2 2021-08-02 12:32:57.829328 1 day 14 hours 235 1 2021-08-02 10:17:29.382983 3 minutes 6 seconds 236 1 2021-08-02 13:22:52.329189 12 hours 55 minutes
The DIFF function receives the timestamp duration, which is calculated by subtracting job-on time from job-off time, and returns a varying-length character string describing the two most significant parts of the duration. (See the Elapsed column.) If you want more or fewer parts of the duration, change the value of the LIMIT constant in the RPG source.
Here’s the RPG code for the DIFF module:
**free
ctl-opt option(*srcstmt: *nodebugio) nomain;
dcl-proc Diff export;
dcl-pi *n;
inDuration packed ( 20 :6);
ReturnValue varchar ( 64 );
DurationNull int ( 5 );
ReturnValNull int ( 5 );
ReturnState char ( 5 );
FunctionName varchar ( 517 );
SpecificName varchar ( 128 );
MessageText varchar (1000 );
end-pi;
dcl-ds *n len (26 );
Years zoned( 4: 0);
Months zoned( 2: 0);
Days zoned( 2: 0);
Hours zoned( 2: 0);
Minutes zoned( 2: 0);
Seconds zoned( 2: 0);
Microseconds zoned( 6: 0);
Duration zoned(20: 6) pos(1);
end-ds;
dcl-c Limit const(2);
dcl-s Text varchar(64);
dcl-s sign varchar( 2);
dcl-s counter uns ( 3);
ReturnState = *zeros;
ReturnValNull = *zeros;
clear MessageText;
monitor;
Duration = inDuration;
if Duration < *zero;
Duration = - Duration;
sign = '- ';
endif;
if Years > *zero;
Text = NumberOf(Years: 'year');
counter += 1;
endif;
if Counter < Limit and Months > *zero;
Text += NumberOf(Months: 'month');
counter += 1;
endif;
if Counter < Limit and Days > *zero;
Text += NumberOf(Days: 'day');
counter += 1;
endif;
if Counter < Limit and Hours > *zero;
Text += NumberOf(Hours: 'hour');
counter += 1;
endif;
if Counter < Limit and Minutes > *zero;
Text += NumberOf(Minutes: 'minute');
counter += 1;
endif;
if Counter < Limit and Seconds > *zero;
Text += NumberOf(Seconds: 'second');
counter += 1;
endif;
if Counter < Limit and Microseconds > *zero;
Text += NumberOf(Microseconds: 'microsecond');
counter += 1;
endif;
if sign <> *blank;
Text = Sign + %trim(Text);
endif;
ReturnValue = %triml(Text);
on-error;
ReturnState = '85555';
MessageText = 'Unexpected error.';
endmon;
end-proc Diff;
dcl-proc NumberOf;
dcl-pi *n varchar(24);
inQuantity uns (10) value;
inUnit varchar(16) value;
end-pi;
dcl-s Text varchar(24);
Text = ' ' + %char(inQuantity) + ' ' + inUnit;
if inQuantity <> 1;
Text += 's';
endif;
return Text;
end-proc NumberOf;
Create the DIFF module from this source member. Create the DIFF service program from the module.
CRTSQLRPGI OBJ(MYLIB/DIFF)
SRCFILE(MYLIB/QRPGLESRC)
SRCMBR(DIFF)
OBJTYPE(*MODULE)
REPLACE(*YES)
CRTSRVPGM SRVPGM(MYLIB/DIFF)
MODULE(MYLIB/DIFF)
EXPORT(*ALL)
Using your SQL interface of choice, create a function to run the DIFF subprocedure in the service program.
create or replace function mylib.diff (Duration dec(20, 6)) returns varchar(64) language rpgle parameter style sql deterministic returns null on null input external name 'MYLIB/DIFF(DIFF)';
And that’s all there is to it.
This function may also qualify as a weird animal, but I’ve done the grunt work for you and you’re welcome to change it to your liking. You’ll probably come up with something better than what I’ve done. If you do, please be sure to tell me about it.
RELATED STORIES
Online Db2 Guide – How to find difference between two timestamps, dates in Db2
Hello Ted.
You can use year(), month(), day()… with this animal.
——- SQL
with d(dif) as (
values (
current timestamp –
timestamp(‘2020-01-01-10.00.15.000000′)
) )
select case when
year(dif) = 0 then ” else trim(char(year(dif)))
||’ years ‘ end ||
case when month(dif) = 0 then ”
else trim(char(month(dif)))||’ months ‘ end
||
case when day(dif) = 0 then ”
else trim(char(day(dif)))||’ days ‘ end
||
case when hour(dif) = 0 then ”
else trim(char(hour(dif)))||’ minutes ‘ end
from d
————
CONCAT
1 years 7 months 2 days