Jul 082013
 
select case 
         when to_char(the_datetime_is, 'mi:ss')
                = '00:00' --midnight
         then 
           the_row_is_processed_twice 
       end unnecessarily
from   your_etl_process
where  the_datetime_is between trunc(:this_date)
                       and trunc(:this_date)+1
and    you_run_this_for = 'consecutive dates';

Remember, between is inclusive so it returns rows equal to the lower and upper bounds (in this 
case dates with times at midnight). Repeated executions of the same query moving the boundaries
will end up processing items multiple times. The correct way to do this is use greater than or
equal to your lower bound and strictly less than the upper bound (not less than or equal to the 
lower bound plus something slightly less than the upper bound!).

For a script showing this principle, click here.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)