How to compute difference in time (between rows)
Hi, for my study i'm trying to analyse website logs in Access.
Below an example
First Comumn: Visitor x
Second Column: Date page requested
Third Column: Time page requested
Fouth Column: Page requested
a 11/30/01 21:07:25 home.html
a 11/30/01 21:08:27 info.html
a 11/30/01 21:10:35 game.html
a 12/30/01 20:00:00 home.html
a 12/30/01 20.00:55 info.html
b 11/30/01 17:00:10 home.html
b 11/30/01 17:04:12 game.html
I'd like to create a query that computes the time spent per page. For example for the first case (visitor a, page: home.html)
(21:08:27)-(21:07:25) = 62 seconds.
So in the query the difference in time must be calculated between two rows.
Does someone know how to create this query?
Jan Oostijen - The Netherlands.
Based on the suggestions till now (july 14), the correct query to do this would be:
SELECT Logging.Visitor, Logging.RequestDate, Int(CSng((Logging.RequestTime)*86400)-CSng((Logging1.RequestTime)*86400)) AS TotalSeconds, Logging1.Page, Logging.RequestTime AS Ended, Logging1.RequestTime AS Started
FROM Logging, Logging AS Logging1
WHERE (((Logging.RequestDate)=(SELECT MAX(Logging1.RequestDate)
FROM Logging AS Logging1
WHERE Logging1.RequestDate <= Logging.RequestDate)) AND ((Logging1.RequestTime)=(SELECT MAX (Logging1.RequestTime)
FROM Logging AS Logging1
WHERE Logging1.RequestTime < Logging.RequestTime)) AND ((Logging1.Visitor)=[Logging].[Visitor]));
Hi, for my study i'm trying to analyse website logs in Access.
Below an example
First Comumn: Visitor x
Second Column: Date page requested
Third Column: Time page requested
Fouth Column: Page requested
a 11/30/01 21:07:25 home.html
a 11/30/01 21:08:27 info.html
a 11/30/01 21:10:35 game.html
a 12/30/01 20:00:00 home.html
a 12/30/01 20.00:55 info.html
b 11/30/01 17:00:10 home.html
b 11/30/01 17:04:12 game.html
I'd like to create a query that computes the time spent per page. For example for the first case (visitor a, page: home.html)
(21:08:27)-(21:07:25) = 62 seconds.
So in the query the difference in time must be calculated between two rows.
Does someone know how to create this query?
Jan Oostijen - The Netherlands.
Based on the suggestions till now (july 14), the correct query to do this would be:
SELECT Logging.Visitor, Logging.RequestDate, Int(CSng((Logging.RequestTime)*86400)-CSng((Logging1.RequestTime)*86400)) AS TotalSeconds, Logging1.Page, Logging.RequestTime AS Ended, Logging1.RequestTime AS Started
FROM Logging, Logging AS Logging1
WHERE (((Logging.RequestDate)=(SELECT MAX(Logging1.RequestDate)
FROM Logging AS Logging1
WHERE Logging1.RequestDate <= Logging.RequestDate)) AND ((Logging1.RequestTime)=(SELECT MAX (Logging1.RequestTime)
FROM Logging AS Logging1
WHERE Logging1.RequestTime < Logging.RequestTime)) AND ((Logging1.Visitor)=[Logging].[Visitor]));
Last edited: