How to get duration of transactions within the earliest and latest time?

[limalbert]

Hi all,

Below is how the data I have.

currentDate user _time
2017-02-01 aaa 8:00:00
2017-02-01 aaa 9:12:00
2017-02-01 aaa 11:15:00
2017-02-01 aaa 14:16:00
2017-02-01 aaa 17:00:00

As of now, I know how to find the duration between the earliest and the latest time (8:00:00 - 17:00:00).
mySearch
| stats earliest(_time) AS start last(_time) AS stop by currentDate, user
| eval durationHour = round(((stop - start)/3600),2)
| table currentDate user durationHour start stop

How do I create a query to find duration in between the earliest and the latest time in the format like below?
1. Duration between 8:00:00 and 9:12:00 --> NOTE: Duration between the earliest and the next earlier time
2. Duration between 9:12:00 and 11:15:00 --> NOTE: Treat the latest hour of the previous duration as the earliest time
3. Duration between 11:15:00 and 14:16:00
4. Duration between 14:16:00 and 17:00:00

[woodcock]

Like this:

|makeresults | eval raw="2017-02-01 aaa 8:00:00::2017-02-01 aaa 9:12:00::2017-02-01 bbb 8:00:00::2017-02-01 bbb 9:12:00::2017-02-01 ccc 11:15:00::2017-02-01 ccc 14:16:00::2017-02-02 aaa 7:00:00::2017-02-02 aaa 8:00:00"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "(?<currentDate>\S+)\s+(?<user>\S+)\s+(?<time>\S+)$"
| table time currentDate user

| rename COMMENT AS "Everything above fakes your data; everything below is your solution"

| eval _time = strptime(currentDate . " " . time, "%Y-%m-%d %H:%M:%S")
| sort 0 - _time
| streamstats global=f window=2 range(_time) AS duration BY user

[woodcock · Accepted solution]

Like this:

Your Base Search Here | delta _time AS duration

[limalbert]

Sorry. I wasn't clear on the question.

There are multiple dates, users. The delta won't work since it always subtracts different user from same day/ different day. How would I do this? I working on data with at least 5000 users

currentDate user _time
2017-02-01 aaa 8:00:00
2017-02-01 aaa 9:12:00
2017-02-01 bbb 8:00:00
2017-02-01 bbb 9:12:00
2017-02-01 ccc 11:15:00
2017-02-01 ccc 14:16:00
2017-02-02 aaa 7:00:00
2017-02-02 aaa 8:00:00

[woodcock]

See new answer (you can unaccept and accept the other answer).