Page 1 of 1

Grouping by UNIX timestamps

Posted: Fri Jun 12, 2009 5:18 pm
by mikemike
I have a table full of rows. Several rows will be entered per day. Each row that's entered is done so along with a unix timestamp stored in an int field. A lot of the rows may be identical other than the 'date' (the unix timestamp) field.

I need a way of selecting distinct rows on a per day basis. Take the snapshop of my table for example.

Code: Select all

 
row_id     |     name      |     date     
--------------------------------------
1          |      Joey    |     1234567890
2          |      Joey    |     1234567910
3          |      Joey    |     1234567950
4          |      Joey    |     1233567890
5          |      Joey    |     1233567980
6          |      Joey    |     1233567990
7          |      Joey    |     1233557890
8          |      Joey    |     1233567890
9          |      Joey    |     1233567890
10         |      Andy    |     1233568890
11         |      Andy    |     1233567890
12         |      Andy    |     1233566890
13         |      Andy    |     1233565890
14         |      Andy    |     1233564890
Let's assume rows 1-4 happened on 1st january, rows 5-7 happened 2nd January, 8-9 on 3rd january. Then rows 10-12 on 1st Jan, 13-14 on 2nd Jan.
We'd have 5 results returned. As following:

Code: Select all

 
Name      |     Day     |     Num_of_occurrences
-----------------------------------------------
Joey     |     1st Jan     |     4
Andy     |     1st Jan     |     3
Joey     |     2nd Jan     |     3
Andy     |     2nd Jan     |     2
Joey     |     3rd Jan     |     2
 
Does this make sense? I'm thinking something like:

Code: Select all

SELECT DISTINCT name,date,COUNT(*) AS Num_of_occurrences FROM TABLE GROUP BY date
But the problem is that the date's aren't the same, instead they'd be in a range of 86,400 seconds, starting from a certain point. In other words, 24 hours from midnight of every night.

It's quite a difficult problem to explain, hope someone can help.

Re: Grouping by UNIX timestamps

Posted: Sun Jun 14, 2009 6:34 am
by VladSun
Remove the DISTINCT part and group by date:
[sql]GROUP BY DATE(FROM_UNIXTIME(`date`))[/sql]

Re: Grouping by UNIX timestamps

Posted: Sun Jun 14, 2009 8:11 am
by mikemike
yeah sorted it now thanks. GROUP BY FROM_UNIXTIME(`date`, '%d / %m %y')