How do I calculate time difference between two values in the same column and also group them?

SamCam

I'm trying to calculate the time difference between API calls but also group them by requestID and day. So far, I have the following Beast Mode to reformat my `requestTime` into time and tell the difference. Meanwhile, I'm trying to use the card itself to group by requestID and date. Currently nothing is coming back.

 

Any suggestions to do this either in a card, Beast Mode or MySQL is greatly appreciated. (The simple TIME function didn't seem to format correctly.)

 

TIMEDIFF(MIN(TIME_FORMAT(SUBSTRING(`requestTime`,13,8),'%h:%i:%s')),MAX(TIME_FORMAT(SUBSTRING(`requestTime`,13,8),'%h:%i:%s')))

 

requestTime format example:
22/Oct/2018:20:22:24 +0000

BlueRooster

So is this just a list of requestIDs and requestTimes? Are you using the requestTimes to calculate your dates as well? 

Are there multiple instances of a requestID on the same day? (Ex, does requestID 5 get called 10 times with 10 responses?) If so, can you explain a bit more about what you're trying to calculate? 

Are you trying to calculate the avg time difference between calls for each requestID or the difference between the first and last call of the day? (Which is what the current beastmode will do)

 

As far as your current BeastMode, this updated version will give you the time difference between two date times of your provided format while also taking the current day into account (ie, 22/Oct/2018 23:59:59 is only 1 minute different than 23/Oct/2018 00:00:59. The one you provided would see a 23 hour difference)

 

TIMEDIFF(MAX(LEFT(
        CONCAT(REPLACE(LEFT(`requestTime`, INSTR(`requestTime`, ':')), ':', ' '), SUBSTRING(`requestTime`, INSTR(`requestTime`, ':') + 1))
                  ,INSTR(`requestTime`, ' +'))),
         MIN(LEFT(CONCAT(REPLACE(LEFT(`requestTime`, INSTR(`requestTime`, ':')), ':', ' '), SUBSTRING(`requestTime`, INSTR(`requestTime`, ':') + 1))
                  ,INSTR(`requestTime`, ' +'))))

I'll be happy to help more if you can help me understand the calculation you're after and the data coming in.

 

Sincerely,
Valiant

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

SamCam

Valiant,

 

Thank you for your detailed response!

 

I do want to know what is the average session duration by day - by summing the difference between each user's last and first API call and dividing by total users - so your Beast Mode is helpful but I'm not sure I understand all of it and it's currently not populating when I create it.

 

Eventually, I would also like to build a behavior flow type column that could label in what order those calls happened by user, if you wouldn't mind also helping with that!

 

Thanks again!

BlueRooster

Sounds good. Any chance you can provide a few rows of sample data for me to play with? 

 

The beastmode I gave you does the following, replaces the first ':' found between date and time with a space. The second part removes the ' +0000' part of the column and the last part is just the timediff conversion. Basically it's cleaning up a your datetime field and then using timediff, instead of just using the time.

SamCam

Of course! And makes complete sense.