10.3 Calculate the number of seconds/minutes between two datetime values
Calculate the number of seconds/minutes between two specified datetime values.
Based on the following table, calculate the cumulative time beginning from when value 1 under Value field appears the first time until value 0 appears; if 0 does not appear at all, compare the current value with the system time. Below is part of the original data:
| ID | Time | Value |
|---|---|---|
| 1 | 2020/07/08 15:00:00 | 1 |
| 1 | 2020/07/08 15:02:00 | 1 |
| 1 | 2020/07/08 15:04:00 | 1 |
| 1 | 2020/07/08 15:06:00 | 0 |
| 1 | 2020/07/08 15:08:00 | 0 |
| 1 | 2020/07/08 15:10:00 | 1 |
| 1 | 2020/07/08 15:20:00 | 0 |
| 2 | 2020/07/08 15:02:00 | 1 |
SPL offers now()function to get the current system datetime, and interval (datetimeExp1,datetimeExp2) function to calculate the interval between two datetime values, where @s option enables returning the number of seconds between them.
SPL script:
| A | |
|---|---|
| 1 | =T(“table.txt”) |
| 2 | =A1.group(ID).(~.group@o1(Value)|[null]) |
| 3 | =A2.news(~.len()\2;ID,(s=A2.~(#*2-1).Time):StartTime, interval@s(s,ifn(A2.~(#*2).Time,now()))/60:CumTime) |
A1 Read the data file.
A2 Group records by ID and then group records in each group according to whether the next Value value is the same; get the first record from each subgroup. And insert a null record into each group for the convenience of later computation.
A3 In each group, take the Time in each odd number row as the start time and that in the next even number row as the end time, and calculate their interval. If the Time in the even number row is null, use the current system time as the end time to calculate the interval.
Execution result:
| ID | StartTime | CumTime |
|---|---|---|
| 1 | 2020/07/08 15:00:00 | 6.0 |
| 1 | 2020/07/08 15:10:00 | 10.0 |
| 2 | 2020/07/08 15:02:00 | 28.0 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
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
