database - Finding Dataset "edges" in SQL -

i have enormous set of data loaded sql database incorrectly (it's vertica db, if matters).

each row has id , timestamp. data loaded sequentially (sequential ids), timestamps converted incorrectly, resulting in times being loaded "am".

here's simplified example of what's in db:

id    |      time 001   | 2013-01-01 00:00:01 // jan 1st 002   | 2013-01-01 01:20:00 ...   | ... 500   | 2013-01-01 11:59:59 501   | 2013-01-01 00:00:01 // should 12:00:01 (pm) 502   | 2013-01-01 00:10:00 // should 12:10:00 (pm) ...   | ... 750   | 2013-01-01 11:59:59 // should 23:59:59 (pm) 751   | 2013-01-02 00:00:00 // next day (the 2nd) 

i need query find me id ranges rows need 12 hours added time. example, example data above, row returned should 501, 750. way add 12 hours id >= 501, , <= 750.

basically, need sort of look-back + look-forward mechanism. programmatically, mean iterating on each row , checking whether day of date same next row, , checking whether time earlier previous row. however, i'm sure there's better way in sql...

this not exactly you're looking for, give row pairs out of sync given day.

select t1.id 'id1', t1.timestamp 'stamp1', t2.id 'id2', t2.timestamp 'stamp2'  table1 t1 join table1 t2 on date(t1.timestamp) = date(t2.timestamp) ,                    time(t2.timestamp) <= time(t1.timestamp) ,                   t1.id <= t2.id t1.id <> t2.id or time(t2.timestamp) <> time(t1.timestamp) order t1.id; 

here's sql fiddle