CODEWITHSUNDEEP
mysql
matte
matte

Reputation: 1246

MySQL query returns always 171 rows when using interval

The following query should return 180 rows (as it is the total number of minutes between 2012-03-13 00:00 and 2012-03-13 03:00) but always returns 171 rows. I found out that when I use interval in a SELECT query, I can always get 171 rows at max. Here is the query:

SET @num = -1;
SELECT @num:=@num+1 AS AddInterval, DATE_ADD('2012-03-13 00:00', interval @num minute) AS MyDate FROM MyTable HAVING MyDate <= '2012-03-13 03:00' LIMIT 0, 180

Also, the following very simple query returns 171 rows too:

SELECT COUNT(DATE_ADD('2012-03-13 00:00', interval 1 minute)) FROM MyTable;

Is there any mysql configuration affects this, a limit or am I doing something wrong?

Thanks.

Upvotes: 0

Views: 41

Answers (1)

Marc B
Marc B

Reputation: 360702

You're selecting from a table - if that table only has 171 rows, then you'll only get 171 increments. SQL will not create the missing rows for you, even if you specify a limit higher than what's available.

This is true even though you're not actually selecting any actual fields from that table. You're selecting only generated values - but you're still limited to the number of rows in the underlying table.

Upvotes: 1

Related Questions