Question
· Apr 5, 2019

How to compare time

I am trying to get the time difference between two time stamps one is recorded earlier to the one happening current but the problem is sql expect string while I have the other stored in a variable and if I do the following I get errors any help please

 

&sql(SELECT {fn TIMESTAMPDIFF(SQL_TSI_HOUR,$ZDT($H,3,1,3),tx)})

 

please note that tx is a variable holding the time formatted the same way as the one being compared to

Discussion (3)0
Log in or sign up to continue

You can do it even easier:

&sql(SELECT {fn TIMESTAMPDIFF(SQL_TSI_HOUR,CURRENT_TIMESTAMP(3),:tx)})

Small example:

&sql(SELECT CURRENT_TIMESTAMP(3),
            {fn TIMESTAMPADD(SQL_TSI_MINUTE,67,CURRENT_TIMESTAMP(3))} into :txBefore,:txAfter)
txBefore," -> ",txAfter,!!

&sql(SELECT {fn TIMESTAMPDIFF(SQL_TSI_HOUR,CURRENT_TIMESTAMP(3),:txAfter)},
            {fn TIMESTAMPDIFF(SQL_TSI_HOUR,:txBefore,:txAfter)} into :r1,:r2)
r1,!,r2

Result:

USER>^test
2019-04-08 08:51:34.916 -> 2019-04-08 09:58:34.916
 
1
1
USER>^test
2019-04-08 08:55:11.907 -> 2019-04-08 10:02:11.907
 
2
2