counting day of the week

mosaic

hi,
i need help in creating the following querry:
i have a table which keeps track of the time a button is pushed. the table has a time column and a button number column. i would like to know how many times a button was pushed in any day of the week(without comparing it to any specific day), in any week of the year and in any month of the year. your help is greatly appreciated.

djurnoce

Try something like this:

SELECT COUNT(date) FROM pavlovs_table
WHERE DAYNAME(date) = "Sunday";

mosaic

thanks daniel,
but i'm not looking for any particular day of the week or the month. i would like to generate a report that will show daily, weekly and monthly activities for each button. for instance, if in the last 6 months button1 was activated 10,000 times and button2 was activated 5000 times; the report should show the activities of each button for each day (about 180 days), for each week (about 26 weeks) and for 6 months.
it seems to me that i may have to use more than one select statement to achive this.

djurnoce

try this...

SELECT SUM(case when DAYNAME(date)="Sunday" then 1 else 0 end) as sundays,
SUM(case when date="Monday" then 1 else 0 end) as mondays,
...etc...
FROM pavlovs_table
WHERE date<=(FROM_DAYS( TO_DAYS(CURDATE()) - 60));

That will do it for days (not so sure about the WHERE statement, my idea was to convert todays date into number of days from year zero, then subtract 60 days, then convert that number of days back to an actual YYYY-MM-DD style date for comparison to the date field). I'm sure you can build on this example to get the info for the other types of info you want.

tomhath

You could probably do it by messing around with unix timestamps. Group by the timestamps rounded down to midnight. It's too early on a Saturday morning for me to try and write the code...