MYSQL Query for Selecting Distinct DATETIME

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

I have a login log that logs when a user successfully enters the system. On success it stores the current datetime in a MySQL DATETIME field Y-m-d H:i:s.

My problem is now I want to pull the unique days a user logged in. Obviously, I can't just pull today's date because they may have several logins for each day.

Code: [ Select ]
SELECT DISTINCT COUNT(DATE(logintime)) FROM login_log WHERE userkey = '123'


I'm not sure why this doesn't work. It seems to count all the records for user 123.

Code: [ Select ]
SELECT DISTINCT DATE(logintime) FROM alogin_log WHERE userkey = '123'


This, however, does work and only shows the distinct days that the user logged in. For instance, user 123 logged in 3 times:

2013-04-05 09:00:00
2013-04-05 11:44:14
2013-04-05 13:30:17

With the second query this shows up 1 time as it should for 2013-04-05, but with the COUNT() parameter it counts this as 3 and gives me the full record occurrence count.

I know I can simply just call the second query and mysql_num_rows it and be done but I would like to know why the MySQL DATE() does not seem to work with COUNT().
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • ScottG
  • Proficient
  • Proficient
  • ScottG
  • Posts: 477

Post 3+ Months Ago

What about something like

MYSQL Code: [ Select ]
SELECT COUNT(userkey)  
FROM login_log
WHERE userkey = '123'
GROUP BY DAY(logintime)
 
  1. SELECT COUNT(userkey)  
  2. FROM login_log
  3. WHERE userkey = '123'
  4. GROUP BY DAY(logintime)
  5.  


If this works for you and you want the day as well your could do something like
MYSQL Code: [ Select ]
SELECT DAY(logintime) AS the_day, COUNT(userkey) AS number
FROM login_log
WHERE userkey = '123'
GROUP BY DAY(logintime)
 
  1. SELECT DAY(logintime) AS the_day, COUNT(userkey) AS number
  2. FROM login_log
  3. WHERE userkey = '123'
  4. GROUP BY DAY(logintime)
  5.  
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

That gives me total number of logins per day. I need the result count. So, here's some example data for user 123

Code: [ Select ]

2013-04-05 09:00:00
2013-04-05 11:44:14
2013-04-05 13:30:17
2013-04-08 07:40:42
2013-04-08 09:03:20
2013-04-08 12:33:15
2013-04-08 14:40:22
2013-04-09 08:22:12
2013-04-09 09:02:10
2013-04-09 11:57:36
2013-04-09 13:45:19
2013-04-09 14:34:09
  1. 2013-04-05 09:00:00
  2. 2013-04-05 11:44:14
  3. 2013-04-05 13:30:17
  4. 2013-04-08 07:40:42
  5. 2013-04-08 09:03:20
  6. 2013-04-08 12:33:15
  7. 2013-04-08 14:40:22
  8. 2013-04-09 08:22:12
  9. 2013-04-09 09:02:10
  10. 2013-04-09 11:57:36
  11. 2013-04-09 13:45:19
  12. 2013-04-09 14:34:09


I need this to return one value of 3. I will find use for your query especially returning the day and number of logins. But here's what I'm getting with your queries.

Code: [ Select ]

COUNT(userkey)
3
4
5
  1. COUNT(userkey)
  2. 3
  3. 4
  4. 5


I just need a count of the distinct days which this user logged in on the 5th, 8th, and 9th. Essentially this a total days worked. Really your queries would work as well with just a mysql_num_rows to sum up the records returned which I've already done, but I'm not finding on the MySQL site where DISTINCT COUNT does not work with the MySQL DATE function. Based on the site it should work. Also, if I can get a query to pull what I need then I don't have to run a mysql_query then a mysql_num_rows. The mysql_query would spit out what I need. Hey, it's one less line of code. :D Thanks for your reply.
  • ScottG
  • Proficient
  • Proficient
  • ScottG
  • Posts: 477

Post 3+ Months Ago

OK so based on your result set and the what you have stated would this work for you

MYSQL Code: [ Select ]
SELECT COUNT(DISTINCT DAY(logintime)) AS number
FROM login_log
WHERE userkey = '123';
 
  1. SELECT COUNT(DISTINCT DAY(logintime)) AS number
  2. FROM login_log
  3. WHERE userkey = '123';
  4.  
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Yes, that works. It's my original query but obviously I have to push the result out as "AS number".

Although, I changed DAY() to DATE() to get the specific dates. Day returns 1-31 which could duplicate.

Thanks for your help.
  • ScottG
  • Proficient
  • Proficient
  • ScottG
  • Posts: 477

Post 3+ Months Ago

No problem. The only thing different between this query and yours is the count wraps the DISTINCT DATE(logintime) instead of the DISTINCT COUNT(DATE(logintime))

also using the AS number just gives is a nice column name, you could change number to hippopotamus if you wanted and it would still work. if your didn't use AS number the column name would be COUNT(DISTINCT DATE(logintime))
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Ahh I see. Yeah, I didn't catch that. I'm so used to saying SELECT DISTINCT but it makes sense COUNT should wrap DISTINCT DATE().

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 156 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.