Highlight rows falling within a timestamp range meeting a condition

[mercurial_maverick]

I've a dataset output in Spark SQL which has multiple columns like defrostStatus, sensorTS, etc. What I'm trying to do here is whenever defrostStatus = 1, the following timestamps that fall within the range (sensorTS < following timestamps < sensorTS + 30 minutes) need to be highlighted in a new column and excluded later.

For example, in the image attached: row 14 has defrostStatus = 1 at sensorTS = 2020-11-08T02:10:00.000+0000. So rows 15,16,17 (reason why these rows is because they fall withhin the sensorTS < following stamps < sensorTS + 30 minute logic) need to be compared for the above logic and needs to be highlighted. This iteration needs to stop once we get to a row where the following timestamps > sensorTS+ 30 minutes.

In this case, row 18 has sensorTS = 2020-11-08T02:50:00.000+0000 which is > than 2020-11-08T02:10:00.000 + (30 minutes) and hence the above iteration shouldn't be applied to this row or any other rows than fall within this range. Please let me know how this needs to be done.

[Qiu]

@mercurial_maverick 
Sorry I have not get a chance to revisit your issue.
I will try to take a look today.

[mercurial_maverick]

@Qiu No worries, thank you for the update.

[Qiu]

@mercurial_maverick 
Before going to work, can I ask which row should flagged as "Included" in the sample data below?

Or you could provide a sample data that have both "Excluded" and "Included". A txt or Excel file is preferable.

[mercurial_maverick]

@Qiu Criteria for "Included":

All rows that do not fall within the 30 mins range from when the defrost value = 1. In the screenshot provided, row 20 is outside the 30min range from when the value becomes 1. So row 20 will be included and all following rows will be included until we reach a row where defrost value = 1 and the following rows fall within the 30min range. 

To summarize rows falling within the 30mins range from when defrost value = 1 -----> Excluded

Remaining all rows that do not fall within this range ------> Included

[Qiu]

@mercurial_maverick 
I gave a try yesterday and burned my brain out...

Will keep trying though.😁

[mercurial_maverick]

@Qiu Sure, no worries. Thank you for the update.