Highlight rows falling within a timestamp range meeting a condition

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.

timestamp.PNG

Datasets

Reporting

Date Time

Dynamic Processing

Time Series

Accepted answers

All comments

Qiu

@mercurial_maverick
It seems not so difficult, but having difficult to understand the point here.

1. by ' following timestamps' you mean the Next_TS column or the sensor_TS of next row?

2. "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) "

Which cell is this "2020-11-08T02:10:00.000" coming from?

mercurial_maverick

Hello @Qiu Thank you for your response. Let me clarify your questions.

1. Yes, I'm referring to Next_TS column. Let me explain the whole thing in a different way.

So whenever defrostStatus =1 (calling it "defrost row" for referencing purpose), that particular timestamp when it attained that status is called "defrost row timestamp" (for referencing purpose I'm calling it so). So the next 30mins from that particular TS needs to be highlighted. Which means every row that comes next after the "defrost row" needs to be validated for the criteria: (defrost row timestamp) < (every row timestamp after defrost row timestamp) < (defrost row timestamp) + 30 minutes. This validation needs to stop once we reach a row where the (row timestamp) > (defrost row timestamp) + 30 minutes.

2. Answering your 2nd question. The timestamp in question (2020-11-08T02:10:00.000) is coming from sensorTS in row 14 which is the "defrost row timestamp" as explained in previous point.

Please let me know if we need to schedule a zoom call if you need more clarifications.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335