Daevid Vincent wrote:
I have a SQL challenge I'm not sure how to solve. But it's so common, I feel
kind of stupid asking this...
I have a 'user' table with 'login_date' which is an auto updated DATETIME
column and a 'created_on' which is a DATETIME (but not updated after the
record is created the first time)
I want to show a list of users who are new since my last login. But the
problem is that my last login changes for every page load (i.e. it is
updated so that I can guestimate if a user is logged in still or not... I
consider 10m to be the window, since rarely do users ever officially
'logout').
So I think I need to have a SQL query that only deals with the date, not the
mins/secs? I'm just not sure the optimum way to do this.
The other challenging part seems to me that as soon as 'login_date' is
updated, then everyone that was 'new' is now 'old' because the login_date
just got updated right? I kinda want the 'new' user status to persist for
the whole session [or maybe even the whole day (that is, I could logout/in
and those people would still show as new) -- but I can live with just being
new for the session]
How is this sort of thing usually handled? Do I need another column that
isn't auto-updated and that just get's set upon each new 'login' session?
Unlike a "message board" or web based "email" system, I CAN'T flag each user
as viewed or something like that. This feature is for a "network" type
scenario where a user can see new users added to their network of friends
since the last time they logged in. Or so the Administrators can see all new
users. This type of thing.
And what is the optimal SQL incantation to deal with just the date portions
and ignore the hh:mm:ss part?
ÐÆ5ÏÐ
DATE() - or maybe DATE_FORMAT() if you want a specific format
--
life is a game... so have fun.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
