How to compare time

Question

Question

Thembelani Mlalazi · Apr 5, 2019

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

Robert Cemper · Apr 6, 2019

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

1 0

score 0 · Answer 1 · 2019-04-06T03:49:41-04:00

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

score 0 · Answer 2 · 2019-04-08T02:04:03-04:00

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,!,r2

Result:

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