0

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

2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by trashed
1

MySQL has a DATE function to extract only the date from a DATETIME field. You should be able to use a query such as the following:

select DATE(date), count(*) from user_activity where event LIKE "LOGIN" group by DATE(date);

I hope that helps.

Votes + Comments
helpful
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.