noppy — 2010-12-03T11:48:08-05:00 — #1
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
r937 — 2010-12-03T12:31:35-05:00 — #2
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
thruska — 2010-12-04T13:08:55-05:00 — #3
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.
noppy — 2010-12-07T06:28:24-05:00 — #4
thanks guys, yeah the chop function was a little vague its similar to the judo_chop function.
You were correct in that i was using SUBSTR() function as performance is not a prob as its only for a stats page a handful of people will ever see.
Think i may just go for splitting up the date into year,month,day,time as it just seems to be easier.