Written by

Question Thembelani Mlalazi · Apr 5, 2019

How to compare time

#Caché #Beginner #CSP #Databases

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

Add reply

Comments

Eduard Lebedyuk · Apr 6, 2019

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

0 0

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

0 0

Vitaliy Serdtsev · Apr 8, 2019

You can do it even easier:

<FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">fn </FONT><FONT COLOR="#808000">TIMESTAMPDIFF</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">SQL_TSI_HOUR</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#808000">CURRENT_TIMESTAMP</FONT><FONT COLOR="#000000">(3),</FONT><FONT COLOR="#800000">:tx</FONT><FONT COLOR="#000000">)}</FONT><FONT COLOR="#800080">)</FONT>

Small example:

<FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#808000">CURRENT_TIMESTAMP</FONT><FONT COLOR="#000000">(3),
            {</FONT><FONT COLOR="#000080">fn </FONT><FONT COLOR="#808000">TIMESTAMPADD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">SQL_TSI_MINUTE</FONT><FONT COLOR="#000000">,67,</FONT><FONT COLOR="#808000">CURRENT_TIMESTAMP</FONT><FONT COLOR="#000000">(3))} </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#800000">:txBefore</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">:txAfter</FONT><FONT COLOR="#800080">)
</FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#800000">txBefore</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" -> "</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">txAfter</FONT><FONT COLOR="#000000">,!!
</FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">fn </FONT><FONT COLOR="#808000">TIMESTAMPDIFF</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">SQL_TSI_HOUR</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#808000">CURRENT_TIMESTAMP</FONT><FONT COLOR="#000000">(3),</FONT><FONT COLOR="#800000">:txAfter</FONT><FONT COLOR="#000000">)},
            {</FONT><FONT COLOR="#000080">fn </FONT><FONT COLOR="#808000">TIMESTAMPDIFF</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">SQL_TSI_HOUR</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">:txBefore</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">:txAfter</FONT><FONT COLOR="#000000">)} </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#800000">:r1</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">:r2</FONT><FONT COLOR="#800080">)
</FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#800000">r1</FONT><FONT COLOR="#000000">,!,</FONT><FONT COLOR="#800000">r2</FONT>

Result:

USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">^test</FONT>
2019-04-08 08:51:34.916 -> 2019-04-08 09:58:34.916
1
1
USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">^test</FONT>
2019-04-08 08:55:11.907 -> 2019-04-08 10:02:11.907
2
2

0 0