CFIF based on time range

[ghanna1]

Hello,

I have time-based tasks stored in a database table. I want to run a scheduled task in CF that runs every 10 min and scans the table for tasks that should have been completed in the last 10 minutes. I'm looking for help writing the CFIF statement for the time range. Something like: If query.time is between Now and LT 10 min Ago

Thanks in advance for any suggestions...

Gary

[WolfShade]

Here's the thing.  If you are running it every ten minutes, the CFIF isn't required.  You should just add a column to the table for time/date completed, and if it's set to a default value of 01-Jan-1970 00:00:00, then run it and set the completion time/date to now(), after it completes.  Otherwise, do nothing.

HTH,

^ _ ^

[ghanna1]

I get what you're suggesting. That will flag the record as completed for today. Where I'm still lost is how do I then tell the select statement only return the events that were supposed to happen in the last 10 minutes?  

Maybe I can explain it a bit better... The goal is at approximately the same time every day to trigger a particular piece of JSON code to trigger a preset. Every day at noon xyz customer wants the channel to change to station 2. The table contains the identifier, 12:00 and channel 2.  The scheduled task scans the table every 10 minutes to see if there's something it needs to do. 

[WolfShade]

Can't say for sure without knowing your schema, but a pseudocode example might be:

SELECT col1

FROM myTable

WHERE timeCompleted = <cfqueryparam value="01-Jan-1970 00:00:00" />

This will get anything that has not been run, period.  (Assuming the database is using that value as the default value when the record is inserted and does not supply a time/date.)

V/r,

^ _ ^

[BigRigMike]

This really sounds like a job for your SQL server to do the filtering of what jobs need to be done. For instance, if your column with the time (12:00) is a date/time in MySQL I would do something like :

<cfset beginTime = timeFormat( now().add("n",-10), "HH:mm:ss") >

<cfset endTime = timeFormat( now(), "HH:mm:ss") >

SELECT id, theChannel, theTime FROM theTable

WHERE TIME(theTime) >= <cfqueryparam value="#beginTime#"> 

AND TIME(theTime) <= <cfqueryparam value="#endTime#"> 

[BKBK]

I think you have mentioned a viable solution yourself. Twice in fact. 🙂

Scheduled task.

The URL attribute of the cfschedule tag should point to your CFM page and the interval attribute should be 600 (the number of seconds in 10 mintes)