How to write "Last Seen" logic like that on Stack Overflow

Question

I'm working on an application that has similar logic as SO with regards to when the user was last seen. I've run into a conceptual problem that I'm hoping some of you Guru's can help me out with.

All activity is logged in an ActivityLog table in the database

When a logged in user hits the site and a new session is created, I update the activity log with the UserID and some very generic info. Same thing happens when they create a new record, update their profile, etc.

The problem I'm having is this.

If I use the most recent activity item, then navigate to my personal account page, the "Last Seen" shows up as 1 second ago because I JUST hit the db on session start... This is not good because I want to see what I was "last" there, not when I'm there "now".

However, if I use Skip(1).Take(1) to get the second record in the database, then when someone else views my profile while I may have "just" signed on... they'll see that I was on say a week ago and not today.

What kind of logic would you use in order to have your cake and eat it too?

I'm using ASP.NET MVC2 and Linq to SQL, but I think this question is more language agnostic.

Answer 1

It sounds like you could just show the second most recent record for the current user, and for all other users show the most recent one.

Answer 2

What I would do (simply to avoid a large logic loop) would be to add two fields. current_seen and last_seen. On login move current_seen to last_seen and set current_seen to the current timestampe. Then display last_seen as their "Last seen on XX/XX/XXX".

Answer 3

One place to look is at the source code to OSQA (the open source q&a system) -

http://www.osqa.net/

And yes, it looks a lot like StackOverflow (to say the least).