Querying data in 0-30 minute groups

Question

I have a script that runs every 30 minutes that runs a query looking for new records within the previous 30 minutes. To account for any type of delay of the script running, I'd like to look in the pervious 0:00-30:00 minute, and 30:01-59:59 chunks.

For example, if my script runs at 11:00am, I'd expect it to find new rows that have an

entered_date > 10:30 am AND < 11:00 am

And when it ran at 11:30am, I'd expect new rows from an `entered_date > 11:00am AND < 11:30am.

Doing something like this only gets the last running 30 minutes:

SELECT
    fa.entered_date,
    fa.text
FROM
    form_answers fa 
WHERE
    AND fa.value = 1
    AND fa.entered_date >= DATEADD(mi, -30, GETDATE())

So if my script was somehow delayed by a few seconds, and ran at 11:00:02, I could potentially miss a record that has an entered_date of 10:30:01.

EDIT:

This is what ended up working for me:

    fa.entered_date BETWEEN 
        DATEADD(mi,-30,CONVERT(VARCHAR(19),DATEADD(mi, DATEDIFF(mi, 0, GETDATE())/30*30, 0))) 
    AND 
        DATEADD(mi, DATEDIFF(mi, 0, GETDATE())/30*30, 0)

Answer 1

There is no sure-fire way to process "new" records based on time alone - even if you track the "last" processed time, there could be records that get missed, or records that get processed twice.

Some alternate methods:

• Track the last entered_date that was processed and process anything that is added after that time.
• Track the last ID that is processed, and process any records with a higher ID (assuming your table uses an incrementing key).
• Add a flag or status field to your records to know if they've been processed or not
• Use a queue that is filled when records are added (using a trigger) and processed periodically.

If you DO have to rely on the clock alone, then I would round the "current" time to the nearest 30-minute interval and lock back 30 minutes from there. So if the current time is 11:00:02, it would round it to 11:00:00 and process records that were added between 10:30:00 and 11:00:00.