Query from access log

fiff · #1 (**permalink**) 06-10-07, 15:01

Hi,

I need help with query. I have access log table something like this:

session_id (int) | user (varchar) | login_time (datetime) | logout_time (datetime)
1 | JOHN | 2007-03-02 15:32:01 | 2007-03-02 18:01:55
2 | JOHN | 2007-03-09 11:43:33 | 2007-03-09 21:02:25
3 | JOHN | 2007-03-15 22:00:05 | 2007-03-16 06:37:21
4 | MIKE | 2007-04-02 12:32:01 | 2007-03-02 14:01:55
5 | MIKE | 2007-03-09 10:41:33 | 2007-03-09 20:44:15

For a result I need report of working hours (user is a employee). The first idea was:

Code:

SELECT `user`, `login_time`, `logout_time`,
TIMEDIFF(`logout_time`, `login_time`) FROM `access_log`
ORDER BY `user`, `login_time`;

But access_log table may contain rows with a same time period (per user). For a example when user enters to the system twice from different PC-s (I can not disallow this), then this query gives double working hours

So I need somehow to take into consideration user time intersection.

Thank you in advance.

r937 · #2 (**permalink**) 06-10-07, 16:39

Quote:

Originally Posted by fiff

But access_log table may contain rows with a same time period (per user).

would you mind showing some sample data that illustrates this

fiff · #3 (**permalink**) 06-10-07, 17:03

Quote:

Originally Posted by r937

would you mind showing some sample data that illustrates this

Data:
MIKE | 13.05.2007 2:00 | 13.05.2007 7:00 -> 5h
MIKE | 13.05.2007 5:00 | 13.05.2007 9:00 -> 4h

I need result:
MIKE | 13.05.2007 | 7h -> 5+4-2=7h, 2 hours (5:00-7:00) is intersection

In resultset I dont need login_time and logout_time, I just need working hours per day.

Thank you for reply!

r937 · #4 (**permalink**) 06-10-07, 17:26

wow

off the top of my head i will recommend that you write an application script to detect these situations

doing it with sql alone is going to be very, very complex

fiff · #5 (**permalink**) 06-10-07, 18:13

Ok, but can you give me some ideas for this query/queries. I dont hope that you compile whole query for me.

Is there a way to get this time intersection (common period) per day? Then it's easy to calculate hours in application. As I understand it's possible with JOIN?