(MySQL) Count rows with variable value interval

Hello,

This is my first thread on this forum, and I will try and do it well :slight_smile:

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:


+---------------+
| unixTimestamp |
+---------------+
|               |
|   1266416921  |
|   1266418721  |
|   1266420471  |
|   1266428321  |
|   1266429921  |
|   1266430821  |
|               |
+---------------+

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:


+-------+-------+
|Period | Count |
+-------+-------+
|   1   |   3   |
|   2   |   0   |
|   3   |   0   |
|   4   |   3   |
+-------+-------+

Thank you for your time :slight_smile:

Kind regards from Norway,
Marius

Is it really important, or can you just add those 0’s in your program, where display logic belongs?

If it’s really important, you need to create a table with all the period numbers, and join that table.

Dear Dan Grossman,

Thank you for you for your reply. Your help is much appreciated! Trust me :smiley:

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)

Thank you for your time.

Kind regards,
Marius

your test data is inadequate

perhaps this will get you started in the right direction…

SELECT unixTimestamp
     , FROM_UNIXTIME(unixTimestamp) AS orig_datetime
     , FLOOR(unixTimestamp/60) AS period
     , FROM_UNIXTIME(FLOOR(unixTimestamp/60)*60) AS period_datetime 
  FROM oslo
ORDER
    BY unixTimestamp

unixTimestamp   orig_datetime       period      period_datetime
1266416921   2010-02-17 09:28:41   21106948   2010-02-17 09:28:00
1266418721   2010-02-17 09:58:41   21106978   2010-02-17 09:58:00
1266420471   2010-02-17 10:27:51   21107007   2010-02-17 10:27:00
1266428321   2010-02-17 12:38:41   21107138   2010-02-17 12:38:00
1266429921   2010-02-17 13:05:21   21107165   2010-02-17 13:05:00
1266430821   2010-02-17 13:20:21   21107180   2010-02-17 13:20:00

the next step is to use a GROUP BY clause

want to try it yourself?

:slight_smile:

Dear R937 :slight_smile:

That was very impressive! :slight_smile:

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

Further help is greatly appreciated!

Thank you for your time!

Kind regards,
Marius

please explain what “it failed” means

it ran just fine when i tried it

of course, your test data was inadequate, there was only 1 count for each period, but at least the query did not fail…

Sure, my terminology is wrong.

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 :):):slight_smile:

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]

Your time is greatly appreciated!

Kind regards,
Marius Jonsson

have you tried removing it from the SELECT clause of the query?

:wink:

WOW! Hehe!

This solution has taught me a lot of things. I will bookmark it for the future!

I tried writing on the offical MySQL forum earlier this week, but you were much more helpful than they were.

Thank you for your time, and best of luck for the future :slight_smile:

Kind regards,
Marius