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

Answers

inside &SQL() only SQL compatible code is allowed
so $ZDT($H,3,1,3) is not known in SQL

you may do

set %myts=$ZDT($H,3,1,3), %tx=tx
&sql(SELECT {fn TIMESTAMPDIFF(SQL_TSI_HOUR,:%myts,:%tx)})

 

mind the colon :%myts and :%tx to pass global  accessible variables to embedded SQL

 

You can use DATEDIFF:

set tx = $ZDT($H,3,1,3)
hang 5
write $SYSTEM.SQL.DATEDIFF("hour", $ZDT($H,3,1,3), tx)
>0
write $SYSTEM.SQL.DATEDIFF("second", $ZDT($H,3,1,3), tx)
>-6

 

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