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
Comments
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
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)})
&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 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)
w txBefore," -> ",txAfter,!!
&sql(SELECT {fn TIMESTAMPDIFF(SQL_TSI_HOUR,CURRENT_TIMESTAMP(3),:txAfter)},
{fn TIMESTAMPDIFF(SQL_TSI_HOUR,:txBefore,:txAfter)} into :r1,:r2)
w r1,!,r2Result:
USER>d ^test 2019-04-08 08:51:34.916 -> 2019-04-08 09:58:34.916 1 1 USER>d ^test 2019-04-08 08:55:11.907 -> 2019-04-08 10:02:11.907 2 2