Hi all.
I have a table with a this records:
<!-- Table user_activity -->
<user_activity>
<id>1</id>
<event>LOGIN</event>
<username>user@domain.com</username>
<domain>domain.com</domain>
<remote_address>ip_address</remote_address>
<date>2008-05-23 20:19:36</date>
<comments></comments>
</user_activity>
I am trying to query the DB to get the total results of activity PER DAY. This is my query (which obviously doesn't work):
SELECT date, COUNT(*) FROM `user_activity` WHERE `event` LIKE "LOGIN" AND `date` LIKE "2008-05%" GROUP BY date
Since the date field has also a hour timestamp, I get thousands of results with count 1.
Is there any way to group the results only by the first 10 characters (and omit the time of day)?
Thanks