Difference between two date time stamp in Intersystems Cache

Question

I would like to find out the number of hours and minutes between two date time stamp.

if for example

 sDateTime = 2016-01-01 01:00 
 eDateTime = 2016-01-03 02:30

I would like it to output it as 49:30 (49hours and 30minutes) I am unable to figure a method to work this out.

what I have so far:

    Set oMNOF=##class(MNOF.MNOF).%OpenId(Id)

Set zstartDt=oMNOF.sDateTime 
Set startDt=$PIECE(zstartDt,",",1)          
Set startTime=$PIECE(zstartDt,",",2)    

Set zendDt=oMNOF.eDateTime 
Set endDt=$PIECE(zendDt,",",1)          
Set endTime=$PIECE(zendDt,",",2) 

    set dateDiff=((endDt - startDt))     //2 days 
set timeDiff=(endTime - startTime)    //outputs 5400 seconds

     set d = (dateDiff * 24 * 60 * 60)
set h = ((timeDiff - d) / 60)
set m = timeDiff - (d) - (h * 60)

Thank you for the help.

score 3 · Accepted Answer · answered Sep 21 '16 at 15:20

3

Another option:

USER>set mm=$system.SQL.DATEDIFF("mi","2016-01-02 01:00","2016-01-03 02:30")

USER>write "hours=", mm \ 60
hours=25
USER>write "minutes=", mm # 60
minutes=30

answered Sep 21 '16 at 15:20

adaptun

494
4
11

is there a way I could out put the 25.5 as 25:30 format instead? thank you very much – jk1844 Sep 21 '16 at 15:37
1

write $zt(25.5*60) `00:25:30` – DAiMor Sep 21 '16 at 16:10
$zt(99.5*60) gives me 01:39:30 I would really like to display 99:30 , any suggestions? – jk1844 Sep 21 '16 at 22:24
1

something like this `set time=99.5 write !,$p(time,".")_":"_$e($J("."_$p(time,".",2),0,2)*60_"00",1,2)` – DAiMor Sep 22 '16 at 06:52

score 0 · Answer 2 · answered Sep 22 '16 at 21:55

Hi thanks to all for the help. I managed to come up with the below, appreciate if someone can improve on this.

<script language="cache" method="MGetData" arguments="pStartDt:%String,pEndDt:%String,pTimeField:%String" returntype="%Library.String">
set val1="00"

//HOUR: check if length equals 1
if $LENGTH($SYSTEM.SQL.FLOOR($system.SQL.DATEDIFF("ss",pStartDt,pEndDt)/3600))=1{
    //add leading zero
    set val1 ="0"_$SYSTEM.SQL.FLOOR($system.SQL.DATEDIFF("ss",pStartDt,pEndDt)/3600)

}
else{
    //get without leading zero
    set val1 = $SYSTEM.SQL.FLOOR($system.SQL.DATEDIFF("ss",pStartDt,pEndDt)/3600)
}

//MINUTES: check if length equals 1
if $LENGTH($SYSTEM.SQL.FLOOR($system.SQL.DATEDIFF("ss",pStartDt,pEndDt)/60) - ($SYSTEM.SQL.FLOOR($system.SQL.DATEDIFF("ss",pStartDt,pEndDt)/3600)*60))=1{

    //add leading zero
    set val2 ="0"_($SYSTEM.SQL.FLOOR($system.SQL.DATEDIFF("ss",pStartDt,pEndDt)/60) - ($SYSTEM.SQL.FLOOR($system.SQL.DATEDIFF("ss",pStartDt,pEndDt)/3600)*60))

}
else{

    //get without leading zero
    set val2 = ($SYSTEM.SQL.FLOOR($system.SQL.DATEDIFF("ss",pStartDt,pEndDt)/60) - ($SYSTEM.SQL.FLOOR($system.SQL.DATEDIFF("ss",pStartDt,pEndDt)/3600)*60))

}

//insert result data into the time field
Write "document.getElementById('"_pTimeField_"').value='"_val1_":"_val2_"';"

//Write "alert('"_val1_"^"_val2_"');"

QUIT 1

Why when you need to find difference in hours, you getting difference in seconds. DATEDIFF [supports](http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_datediff) hours too. — DAiMor, Sep 24 '16 at 21:59

Difference between two date time stamp in Intersystems Cache

2 Answers2