Order-based Calculations – Time Difference
【Question】
I have a table structured like this:
index date time status
1 2015-01-01 13:00:00 start
2 2015-01-01 13:10:00 continue
3 2015-01-01 13:20:00 continue
4 2015-01-01 13:30:00 end
5 2015-01-01 13:30:00 ready
6 2015-01-01 13:40:00 start
7 2015-01-01 13:50:00 continue
8 2015-01-01 15:00:00 end`
What I would like to do is to count the time between start and end (ie. index 1-4 is 30min, 6-8 is 20min) by taking into account only the first start and first end. So the query doesn’t choose the time difference of index 1-8. I need a query to calculate time difference between two statuses (start-end) and show the result for multiple instances of start-end without them getting batched into one event?
A solution:
SELECT x.*
, TIMEDIFF(MIN(y.dt),x.dt)diff
FROM my_table x
JOIN my_table y
ON y.dt >= x.dt
WHERE x.status = 'start'
AND y.status = 'end'
GROUP
BY x.id;
【Answer】
It’s complicated to handle order-based calculations in SQL. You can do it in esProc SPL (Structured Process Language) with simple and easy code:
A |
|
1 |
$select * from tb1 |
2 |
=A1.group((#-1)\2) |
3 |
=A2.new(~(1).time:start,~(2).time:end,interval@s(start,end)/60:diff) |
A3: Calculate time differences to generate a new table sequence.
The SPL script can be easily embedded into a Java application via esProc JDBC. See How to Call an SPL Script in Java.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL