How do I filter a database query based on date/time or a timestamp

Question

What is the best way to monitor an existing table in SQL for a specific value (e.g. select * from DOCUMENT where PENDTX = 1) and then make sure rows are not picked up more than once, without updating the record or modifying the data schema?

Answer 1

The best way to handle this would be to use a timestamp filter in your query, for example:

select * from DOCUMENT
where PENDTX = 1
   and DB_CREATE_DATE >= {lastTimeStamp}
   and DB_CREATE_DATE < {currentTimeStamp}

Each time you  execute the query, you would:

1. Pull the lastTimeStamp from channel cache
2. Calculate your currentTimeStamp
3. Execute the query
4. Update the lastTimeStamp channel cache variable with the currentTimeStamp value

It is important that you do it in this order to ensure that you don't miss any records and you don't overlap or pull any records multiple times.  This method assumes that the timestamp value (e.g. DB_CREATE_DATE) is static and does not change.