This is my first thread on this forum, and I will try and do it well
I have tried to use google, the mysql forum, this forum, and experimenting, with varying degree of luck. Problem is that I am not very experienced with databases.
The problem:
I have a table with unix timestamps. The first three entries are all within the same minute, then there is a gap of two minutes with zero entries, and eventually in the fourth minute there are three more entries:
I would like to ask if somebody could please write me a MySQL query that counts the numbers rows in each minute-period and returns like this. it is important that even those minute-periods that contains zero hits are returned as zero:
Thank you for you for your reply. Your help is much appreciated! Trust me
If we skip the zeros, will it be possible for you to write a query that prints the periods with start timestamp of that period (not first stamp in a group of timestamps) instead of period numbers?
I.e:
+-------------------+-------+
|Period Start Stamp | Count |
+-------------------+-------+
| 1266416921 | 3 |
| 1266431321 | 3 |
+-------------------+-------+
// please notice that these periods are exactly 4 mins apart (14400 sec)
But we have to add a “count” column which tells us how many there are of each period, and I suppose we have to group by each period? I tried doing the above, but it failed, so I must be wrong, hehe:
SELECT unixTimestamp
, FLOOR(unixTimestamp/60) AS period
, COUNT(FLOOR(unixTimestamp/60)) as count
FROM kingoslo.new_stats
GROUP BY period -- or should this be something else? I think so. Hmmm
ORDER BY period
Actually I am wrong all together, it works just fine, the only problem was that I didnt have test data with timestamps from the same minute. When I ran it, I noticed that it only returned count: 1 per period. I automatically thought: “Ah, it didn’t work”. As SQL Consultant and Database GURU, naturally you are right :):)
Now to last question: to get rid of the the unix_timestamp column from the result, so that it only displays period and count columns?[/B]