Solved Get Time between 2 Timestamps

Saphirah

Active member
Local time
Today, 21:44
Joined
Apr 5, 2020
Messages
163
Hello everyone,

i have a table with multiple timestamps and corresponding Actions.

00:00:01 - Action 1
00:00:04 - Action 2
00:00:10 - Action 3
00:01:00 - Action 4
00:01:23 - Action 5

I need a query that outputs the time that passed between those actions.
The end result should look similar to this:

00:00:01 - Action 1
00:00:03 - Action 2
00:00:06 - Action 3
00:00:50 - Action 4
00:00:23 - Action 5

To be honest though, i have no idea where to even start.
Can someone help me with this problem?

Thank you very much!
 
Code:
SELECT Format$([timestamp]-Nz((Select Top 1 [timestamp] from yourTable As T 
where T.[timestamp] < yourtable.[timestamp] order by T.[timestamp] desc),#12/30/1899#),"hh:nn:ss") AS Expr1, yourTable.action
FROM yourTable
ORDER BY yourTable.timestamp;
 
Code:
SELECT Format$([timestamp]-Nz((Select Top 1 [timestamp] from yourTable As T
where T.[timestamp] < yourtable.[timestamp] order by T.[timestamp] desc),#12/30/1899#),"hh:nn:ss") AS Expr1, yourTable.action
FROM yourTable
ORDER BY yourTable.timestamp;
Oh wait, i did not refresh the page :D Thank you very much for this! Why do you do the #12/30/1899# though?
The rest is very understandable. One more thing, isn't using a reference to the outer query table very performance intense?
 
#12/30/1899#, access corrected it.
i put #00:00:00# to it.

about your last comment, you should try on larger datasets to find out.
 
Nice! That makes more sense! Thank you very much for your help everyone. I will mark the thread as solved :D
 
Just one point. Time doesn't exceed 24 hours, so you can get strange results.

MsgBox Format(#9:00:00 AM# + #5:00:00 PM#, "Medium Time") = 02:00 AM
MsgBox Format(#9:00:00 AM# - #5:00:00 PM#, "Medium Time") = 08:00 AM
 
Good point. In my case the timestamps will reach a maximum of 2 hours so this should not be a problem. Still good to know.
 

Users who are viewing this thread

Back
Top Bottom