How to compute difference between times (1 Viewer)

[oostijen]

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]));

 

[Rich]

This article may be of help
Article ID: Q88657

 

[Jon K]

Without the ending time, how would you calculate the viewing time a visitor spent on his/her last page? For example, the time for visitor b, page game.html?

 

[oostijen]

Jon: that's the problem with log files of websites: you never know when a vistor leaves a site, you only know at what time the last page was requested. So for the time spent on the last page you have to make assumptions. Example: mean time of pages visited, mean time of other users spent on game.html etc.


Rich: thanx for your answer, but the suggestion on that page requires knowledge of programming in Visual Basic.

Has anybody an idea on how to solve this problem without using VBA?

 

[RV]

Jan,

>So for the time spent on the last page you have to make assumptions<

I don't know what your assumptions are going to be, anyway, try this

SELECT Logging.Visitor, Logging.RequestDate,
INT(CSng((Logging1.RequestTime)*86400)-CSng((Logging.RequestTime)*86400)) As TotalSeconds,
Logging.Page, Logging.RequestTime AS Started, Logging1.RequestTime As Ended
FROM Logging, Logging AS Logging1
WHERE Logging1.Visitor = Logging.Visitor
AND Logging1.RequestTime =
(SELECT MAX (Logging1.RequestTime)
FROM Logging AS Logging1
WHERE Logging1.RequestTime < Logging.RequestTime)
AND Logging.RequestDate =
(SELECT MAX(Logging1.RequestDate)
FROM Logging AS Logging1
WHERE Logging1.RequestDate <= Logging.RequestDate);
HTH,

Greetings (groeten en suc6),

RV

 

[raskew]

RV-

Tried this and your query worked like a charm-first time with no adjustments required!

Couple of cleanup suggestions:

Change:

Line 2:
INT(CSng((Logging1.RequestTime)*86400)-CSng((Logging.RequestTime)*86400))
to
INT(CSng((Logging.RequestTime)*86400)-CSng((Logging1.RequestTime)*86400))

… will display positive # of seconds

Line 4:
Logging.Page, Logging.RequestTime AS Started, Logging1.RequestTime As Ended
to
Logging.Page, Logging1.RequestTime AS Started, Logging.RequestTime As Ended

… will correct reverse display of start/end times

Bob

 

[oostijen]

WOW! Thanks a lot guys, this really helped me.
One last change:

Line 3
Logging.Page to Logging1.Page


One last question:
I i want to make the assumption that the time spent on the last page is the mean of the time spent on the previous pages. Anyone any ideas on how to do this?

 

[RV]

> I i want to make the assumption that the time spent on the last page is the mean of the time spent on the previous pages. Anyone any ideas on how to do this?<

Jan,

you'll have to define "last page" first.
And for a visitor, the last page can be the most interesting one....

Another remark; the query will have to be adapted for page visits which run into another data.
For example, starttime on 11/30/01 at 23:57:00, endtime on 12/01/01 at 00:01:00

RV

 

[oostijen]

One last problem

The query works fine. There's one problem: when I run the query on a small file, everyrhing works ok.

When I run it on the main log (135.000 records, 3.18 MB) access freezes. I tried this on several computers even a pentium IV.

Anybody any ideas on how to tackle this problem?

 

[RV]

I don't know whether freezing is due to number of records, type op log file, connections, network...

You could try to "cut" the query in pieces by date ranges.

HTH,

RV

 

[oostijen]

thanks for all the suggestions. I did the job in SPSS.
The used syntax:

If date=lag(date) AND visitor = lag(vistor) timeonpage=time-lag(time)