ADD_TIME, but variable

[Michele Adamski]

So, I've been presented with a unique request...

I need to run a REST API to the Tableau scheduler to run some extracts. It must run immediately after a long running worklfow completes. 

The problem that I'm facing is that it must run on the five minute increment of the hour e.g. 12:05, 12:10, 12:15. 12:20, etc. So, I need to determine the current time and ADD_TIME to pass to Tableau in the job's direct input. 

For example, if the workflow finishes then the REST job starts at 12:06:41, I need to add 00:03:19 to get to the next five minute increment of the hour. 

Has anyone run into a similar requirement and, if so, how did you handle it? 

[Markus Embacher]

Hi Michele,

please try the following script:

:SET &ENDTIME# = SYS_TIMESTAMP_PHYSICAL()
:PRINT &ENDTIME#
:SET &HH#=STR_CUT(&ENDTIME#,12,2)
:SET &MM#=STR_CUT(&ENDTIME#,15,2)
:SET &SS#=STR_CUT(&ENDTIME#,18,2)
:SET &HHS#=&HH# * 60 * 60
:SET &MMS#=&MM# * 60
:SET &SECONDS#=&HHS# + &MMS# + &SS#
:SET &DIFF# = MOD(&SECONDS#,300)
:SET &DIFF# = 300 - &DIFF#
:SET &DIFFS#=MOD(&DIFF#,60)
:SET &DIFFM#=&DIFF# - &DIFFS#
:SET &DIFFM#=&DIFFM# / 60
:SET &DIFFM#=FORMAT(&DIFFM#,"00")
:SET &DIFFS#=FORMAT(&DIFFS#,"00")
:SET &NEXT#=ADD_TIMESTAMP(&ENDTIME#,"00:&DIFFM#:&DIFFS#")
:PRINT &NEXT#

Regards, Markus

[Michele Adamski]

Thanks, Markus! This worked perfectly and I appreciate your time!