Highlighted

CFIF based on time range

Participant ,
Feb 04, 2020

Copy link to clipboard

Copied

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

TOPICS
Advanced techniques, Getting started

Views

455

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

CFIF based on time range

Participant ,
Feb 04, 2020

Copy link to clipboard

Copied

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

TOPICS
Advanced techniques, Getting started

Views

456

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 04, 2020 0
LEGEND ,
Feb 04, 2020

Copy link to clipboard

Copied

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,

 

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 04, 2020 0
Participant ,
Feb 04, 2020

Copy link to clipboard

Copied

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. 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 04, 2020 0
LEGEND ,
Feb 04, 2020

Copy link to clipboard

Copied

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,

 

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 04, 2020 0
Community Beginner ,
Feb 06, 2020

Copy link to clipboard

Copied

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#"> 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 06, 2020 0
BKBK LATEST
Adobe Community Professional ,
Feb 06, 2020

Copy link to clipboard

Copied

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)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 06, 2020 0