【Question】
Here’s a table rangeData:

 

sensorNumber rangeStartTime rangeEndTime sensorLow sensorAverage sensorHigh

 

1 -100 0 32 29 90

 

3 23 30400 902  1021 1200

 

3 1000 122200 3111 5400 7000

 

5 4222 310400 563  764  890

 

3 8792 10400 802  930  2019

 

3 0 304000 1001 1200 2190

 

3 1990 550400 4 31 90

 

4 123  20400 765  870  930

 

3 -56 440400 333  521  900

 

3 234  210400 15 98 130

 

3 9021 70400 100001 130000 150000

 

6 2111 100400 62 67 75

 

4 537  70400 77 80 90

 

3 32 87 910  1000 1102

 

I have the following MySQL query:

 

SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh ``FROM rangeData ``WHERE rangeStartTime >= 0 ``AND rangeEndTime <= 43200 ``AND (sensorNumber = 3)``ORDER BY sensorNumber;

 

How can I grab just one previous value and one next value outside the rangeStartTime and rangeEndTime time span?

In English we can say:“Select all values within 5:00 pm and 7:00 pm, and also select next value right before 5:00 pm and next value right after 7:00 pm.”

I tried the following query by looking at other examples but it did not work:

SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh ``FROM rangeData ``WHERE rangeStartTime >= 0 ``AND rangeEndTime <= 43200 ``AND rangeStartTime < 0 LIMIT 1``AND rangeEndTime > 43200 LIMIT 1``AND (sensorNumber = 3)``ORDER BY sensorNumber;

 

Each query adds 5-10 seconds of wait time so I would like to make this into one query.

 

Here’s a solution:

(SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh

 

FROM rangeData

 

WHERE (rangeStartTime BETWEEN 0 AND 43200) AND (sensorNumber = 3))

 

UNION

 

(SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh

 

FROM rangeData

 

WHERE (rangeStartTime < 0) AND (sensorNumber = 3)

 

ORDER BY rangeStartTime DESC

 

LIMIT 1)

 

UNION

 

(SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh

 

FROM rangeData

 

WHERE (rangeStartTime > 43200) AND (sensorNumber = 3)

 

ORDER BY rangeStartTime ASC

 

LIMIT 1)

 

ORDER BY sensorNumber;

 

【Answer】
Here’s my solution: Sort the table and get the sequence numbers of the records within the time span 5:00 pm-7:00 pm”, and then the sequence number of the record before all those records and that of the record after them. Then you can get the desired records according to their sequence numbers.

But, since MySQL doesn’t support sequence numbers directly, it’s difficult to perform order-based calculations. It’s much simpler to do it in SPL (Structured Process Language):

 

	A
1	$select sensorNumber,   rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh from   rangeData where sensorNumber=3 order by rangeStartTime
2	=A1.pselect@a(rangeStartTime   >= 0 && rangeEndTime <= 43200)
3	=A1.m@0(A2&(A2(1)-1)&(A2.m(-1)+1))

A2: Get sequence numbers of records within the time span: rangeStartTime>= 0 && rangeEndTime <= 43200.

 

A3: Get records according to A1’s sequence numbers.