Grouping by UNIX timestamps
Posted: Fri Jun 12, 2009 5:18 pm
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.
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:
Does this make sense? I'm thinking something like:
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.
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 | 1233564890We'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
Code: Select all
SELECT DISTINCT name,date,COUNT(*) AS Num_of_occurrences FROM TABLE GROUP BY dateIt's quite a difficult problem to explain, hope someone can help.