query has to compare each previous query if > 60 minutes

[Janis]

I have an events table:
id, session_id, user_id, when_occurred
468267 8244 674 04
468268 8244 674 1202962612
468269 8244 674 1202962612
468270 8244 674 1202962612
468271 8244 674 1202962612
479272 8244 674 1202962900

This was imported from a UNIX text file and the when_occurred is a UNIX
timestamp. I need a calc to convert it to a date.

I am trying to arrive at a complicated query. One session has more than one
record for a user. Each record per session and user is a page visit. I'm
trying to count page views per one visit. The first 4 records are one visit.

SELECT id, session_id, user_id, when_occurred
FROM events where id = id AND session_id = session_id AND when_occurred < 60
WHERE when_occurred EXISTS [when_occurred < 60 ]
ORDER_BY when_occurred DESC

The caveat here is the definition of when a session ends is if there has
been no page visit for 1 hour. So I think each record needs to compare the
previous record to see if there was a 60 minute lapse.

tnx,

 

[John W. Vinson]

I have an events table:
id, session_id, user_id, when_occurred
468267 8244 674 04
468268 8244 674 1202962612
468269 8244 674 1202962612
468270 8244 674 1202962612
468271 8244 674 1202962612
479272 8244 674 1202962900

This was imported from a UNIX text file and the when_occurred is a UNIX
timestamp. I need a calc to convert it to a date.

A Unix date is, if I recall correctly, the number of seconds since midnight,
January 1, 1960; so

DateAdd("s", [when_occurred], #1/1/1960#)

should give you an Access date/time.

I am trying to arrive at a complicated query. One session has more than one
record for a user. Each record per session and user is a page visit. I'm
trying to count page views per one visit. The first 4 records are one visit.

SELECT id, session_id, user_id, when_occurred
FROM events where id = id AND session_id = session_id AND when_occurred < 60
WHERE when_occurred EXISTS [when_occurred < 60 ]
ORDER_BY when_occurred DESC

The caveat here is the definition of when a session ends is if there has
been no page visit for 1 hour. So I think each record needs to compare the
previous record to see if there was a 60 minute lapse.

If the ID's are sequential (are they?) then a Self Join query would work. Add
the table to the query grid twice; initially join ID to ID. Then open it in
SQL view and edit

FROM Events INNER JOIN Events AS Events_1
ON Events.ID = Events_1.ID

to

FROM Events INNER JOIN Events AS Events_1
ON Events.ID = Events_1.ID - 1

You can then subtract Events.WhenOccured from Events_1.WhenOccured to get the
time lapse.