Re: Need to find job start, job end, calculate total job time for a group of users, TST*, for a selected date range

[Booth Martin <booth@xxxxxxxxxxxx>]

Not SQL, but this works:

wSeconds = %diff(%timestamp(S1DATE2): %timestamp(S1DATE1): *seconds)
// get days. hours, minutes, & seconds
  S1DAYS = %div(wSeconds: 86400);
  wSeconds = %rem(wSeconds: 86400);
  S1HOURS = %div(wSeconds: 3600);
  wSeconds = %rem(wSeconds: 3600);
  S1MINUTES = %div(wSeconds: 60);
  S1SECONDS = %rem(wSeconds: 60);
// get duration as char field
  S1DATE = %editc(S1YEARS: 'X')   + '-'
         + %editc(S1MONTHS: 'X')  + '-'
         + %editc(S1DAYS: 'X')    + '-'
         + %editc(S1HOURS: 'X')   + '.'
         + %editc(S1MINUTES: 'X') + '.'
         + %editc(S1SECONDS: 'X') + '.000000';
 
(years & months is a bit more convoluted but probably outside your range of need.)


On 4/26/2017 4:33 PM, Rob Berendt wrote:
> Actually I can get all but formatting that last column as a time.
> Picture this
> ts1-ts2 equals these digit representations of a time
> 000059
> 001904
> ...