Grouping by UNIX timestamps

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Grouping by UNIX timestamps

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Grouping by UNIX timestamps

Post by VladSun »

Remove the DISTINCT part and group by date:
[sql]GROUP BY DATE(FROM_UNIXTIME(`date`))[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: Grouping by UNIX timestamps

Post by mikemike »

yeah sorted it now thanks. GROUP BY FROM_UNIXTIME(`date`, '%d / %m %y')
Post Reply