Hi
i’m trying to create a query that returns the number of times a day occurs in my datetime field.
If it was just names or somethng i’d just do a distinct query but because the dates are stored as eg 2010-11-23 12:01:22 i can’t as it would just return everything.
So somehow i need to chop the date down to day and count how many times it occurs.
i was thinking that if i put it into a while look and just chop the string and then count the amount of times each day occurs i could then output the values for each unique day.
Iam not sure where to start on the while loop though i can do the chopping but i don’t know how to count each and then store that number against a a when it changes and then output it as an array.
any help or pointers would be appreciated
since the CHOP function is too generic (not to mention fictional), may i suggest you use the mysql DATE function on your datetime column, assign it an alias, and then use the alias in the GROUP BY clause
The MySQL SUBSTR() function can be used to “chop” up the value as a string. But you’ll probably kill database performance because the entire table will have to be scanned (i.e. no index will be used).
A better approach, since you probably want the ‘time’ part of the field for referencing elsewhere, is to create a new field in the table that is just a ‘date’ field. Then, use a “UPDATE tablename SET newfield = oldfield” statement to clone the datetime field value to the date-only field. Then, set an index on the date-only field. After that you should be able to use GROUP BY and COUNT() as normal on the new field.
