date & time difference calculation

Wed, 19 May 2004 00:30:38 +1000

hi

Here is a possible solution, I think it is right, ugly and untested.

define from-last date = lastday(from-date)
define from-ldy       = ncon(ascii(from-last,8)[7:2])

define from-dt char*8 = ascii(from-date,8)
define from-yr        = ncon(from-dt[1:4])
define from-mt        = ncon(from-dt[5:2])
define from-dy        = ncon(from-dt[7:2])
define from-hr        = ncon(ascii(from-time,4)[1:2])
define from-mn        = ncon(ascii(from-time,4)[3:2])

define to-dt char*8   = ascii(to-date,8)
define to-yr          = ncon(to-dt[1:4])
define to-mt          = ncon(to-dt[5:2])
define to-dy          = ncon(to-dt[7:2])
define to-hr          = ncon(ascii(to-time,4)[1:2])
define to-mn          = ncon(ascii(to-time,4)[3:2])

define yrs  = to-yr - from-yr - 1 &
              if from-mt > to-mt else &
              to-yr - from-yr
define mt   = 12 - from-mt + to-mt &
              if from-mt > to-mt else &
              to-mt - from-mt
define mts  = mt - 1 &
              if from-dy > to-dy else &
              mt
define dy   = from-ldy - from-dy + to-dy &
              if from-dy > to-dy else &
              to-dy - from-dy
define dys  = dy - 1 &
              if from-time > to-time else &
              dy
define hr   = 24 - from-hr + to-hr &
              if from-hr > to-hr else &
              to-hr - from-hr
define hrs  = hr - 1 &
              if from-mn > to-mn else &
              hr
define mns  = 60 - from-mn + to-mn &
              if from-mn > to-mn else &
              to-mn - from-mn

define ddiff char*10 = ascii(yrs,2) + &
                       ascii(mts,2) + &
                       ascii(dys,2) + &
                       ascii(hrs,2) + &
                       ascii(mns,2)

good luck
Jeff

At 02:17 PM 17-05-04 -0500, you wrote:
>Hi Joe,
>
>The email attachment got lost in the replies.
>Here's the info. At the end is the problem I'm still trying to work out.
>
>> Lorry Litman wrote:
>>
>> Hi,
>> 
>> How to do a date/time difference calculation?
>> 
>> VMS 7.2, PH 7.10G, RDB 7.0
>> In the RDB database the DATE field is INTEGER and the TIME field is
>SMALLINT.
>> In the dictionary (century included), the DATE field is type D, size 8,
>datatype ZU, and the TIME field is type N, size 4, datatype ZU.
>> 
>> A new field is being created, call it DATE_DIFF, I suspect it will be
>INTEGER in RDB and Numeric size 10, ZU in the dictionary.
>> I need to store the DATE_DIFF result in format YYMMDDHHMM.
>> The display of the DATE_DIFF field on the screen is to be in the format of
>YY:MM:DD:HH:MM.
>> 
>> Any help/suggestions would be much appreciated.
>
>> Chris replied
>>
>> decimaltime(vmsdatetime1, vmsdatetime2) will return (vmsdatetime1 - 
>> vmsdatetime2) as a signed, floating point, number of days.
>> 
>> You're on your own in deciding how many difference days in a difference 
>> month or year, it being variable. I'd've recommended the VMS delta time 
>> format of dddd-hh:mm (good for just under 30 years).
>> 
>> ; convert own-brand date & time into vms date/time format
>> def vmsdt1 vmsdate = floor(mydate) + floor(mytime*0.01)/24 + &
>>       mod(mytime,100)/1440
>> ; calculate difference
>> def fdays = decimaltime(vmsdt1,vmsdt2)
>> def idays int*4 = floor(fdays)
>> def ihours int*2 = floor((fdays - idays)*24)
>> def imins int*2 = floor((fdays - idays)*1440 - 24 * ihours)
>> def date_dif int*10 = idays*10000 + ihours*100 + imins
>
>
>I tried Chris' suggestion which was very helpful (thank-you Chris).
>I ran into a problem with the hour & minute calculation.
>As an example, 
> difference between 21-jan-1999-10:15 and 20-jan-1999-14:00
> resulted in 2735 (27 hrs, 35 min)
> I expected a result of 2015 (20 hrs, 15 min), 
> 
> difference between 10:15 and 10:00 comes up as 14 instead of 15. 
>
>Because I'm not familiar with these functions (or the math) in Chris's
>solution
>I have changed the imins define. 
>
>This e-mail and/or any documents in this transmission is intended for the
address(s) only and may contain legally privileged or confidential
information. Any unauthorized use, disclosure, distribution, copying or
dissemination is strictly prohibited. If you receive this transmission in
error, please notify the sender immediately and return the original.from:
def imins int*2 = floor((fdays - idays)*1440 - 24 * ihours)
>to:     def imins int*2 = floor((((fdays - idays)*24) - ihours)* 60 + .01)
>MIME-Version: 1.0
>
>I believe this now gives me the correct result in days,hours,minutes.
>However, I need the result in years,months,days,hours,minutes.
>
>As an example
> difference between 30-dec-2004-10:06 and 11-oct-2003-10:30
> results in 4452336 (445 days, 23 hours, 36 minutes)
> I need the result as 0102192336 (1 year, 2 months, 19 days, 23 hours, 36
>minutes)
>
>Any suggestions?
>Maybe there are different powerhouse functions I can/should use.
>
>Thanx
>    Lorry
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ Jeff Hoffman                          + e-mail: wonicon@optushome.com.au  +
+ Director                              +                                   +
+ WoniCon Pty Ltd   (ABN 93 083 502 630)+                          ,-_|\    +
+ 21 Prices Circuit                     + voice: +61 2 9542 1527  /     \   +
+ Woronora    NSW     2232              + fax:   +61 2 9576 7029  \_,-._*   +
+ AUSTRALIA                             + mobile: 040 790 3929         v    +
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
+