MYSQL group by day, week and month with php timestamp

Question

Possible Duplicate:
MySQL Query GROUP BY day / month / year

I have php timestamps (e.g. 1307362819) stored in a column in my database. I want to group count(*) in days, weeks and months the data.

For example I want to find out how many entries there are per day, per week, and per month etc.

How can this be achieved?

MONTH( `timeStamp` ) returns NULL – Jake Dec 16 '12 at 19:51 — Jake, Dec 16 '12 at 19:51

score 4 · Answer 1 · answered Dec 16 '12 at 20:08

You can subtract day, week and month value from a timestamp, and group the subtracted values.

grouping by day value:

select count(*) from table group by from_unixtime(timeStampColumn, '%Y%m%d')

grouping by week value:

select count(*) from table group by from_unixtime(timeStampColumn, '%Y%m%u')

grouping by monthvalue:

select count(*) from table group by from_unixtime(timeStampColumn, '%Y%m')

For more information, have a look at this page: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

score 0 · Answer 2 · answered Dec 16 '12 at 19:48

0

Why don't you use these: <>

SELECT COUNT(*) FROM yourTable WHERE yourTimestampField > someTimestampAWeekPast;
SELECT COUNT(*) FROM yourTable WHERE yourTimestampField > someTimestampADayPast;

Use the mysql date/time functions http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html to calculate the desired date in the past. Use UNIX_TIMESTAMP if you stored them as int or string or whatever.

answered Dec 16 '12 at 19:48

m02ph3u5

3,022
7
38
51

i want to for example do: dec 16 X posts, dec 15 x posts (and do this for the past 15 days no matter what today is), also i want to do the same with weeks and months to get a picture of how many posts are made. – Jake Dec 16 '12 at 19:50
MONTH( `timeStamp` ) returns NULL – Jake Dec 16 '12 at 19:50

echo_Me · Answer 3 · 2012-12-16T20:23:27.643

0

here a good exemple for you

to group by week

EDIT:

try this

          select
id_user,
year(time) as AYear, week(time) as AWeek, day(time) as Aday ,
count(week(time)) as TotalPerWeek , count(day(time)) as TotalPerDay ,
count(year(time)) as TotalPerYear
from yourtable
where id_user = 16             //// the user u want to check
group by id_user, AYear, AWeek , Aday
order by AYear, AWeek , Aday

edited Dec 16 '12 at 20:23

answered Dec 16 '12 at 20:07

echo_Me

37,078
5
58
78

score -3 · Answer 4 · answered Dec 16 '12 at 20:04

-3

SELECT MONTH( FROM_UNIXTIME(  `timeStamp` ) ) , COUNT(  `id` ) 
FROM  `discusComments` 
GROUP BY MONTH( FROM_UNIXTIME(  `timeStamp` ) )

answered Dec 16 '12 at 20:04

Jake

3,326
7
39
59

Explain **why** that could solve the problem. – fuxia Dec 16 '12 at 20:30

MYSQL group by day, week and month with php timestamp

4 Answers4