# Generate multiple records without querying a table

• Feb 24, 2011, 06:31
tomalex0
Generate multiple records without querying a table
Its known that

Code:

`SELECT 1 as head`
will display single row with head as fieldname and 1 as value

Is there any way to generate multiple rows based on some range

For example i want to generate dates of particular month.
so it will be some 30 records generated without querying a particular table.
• Feb 24, 2011, 06:46
r937
you could do a range quite easily by using a numbers table

but why did you say "without querying a table"?
• Feb 24, 2011, 07:35
tomalex0
I could use a number table, but was just trying my luck if it can be done without that.

I came across a post here
http://stackoverflow.com/questions/1...ween-two-dates

Code:

```select a.Date from (     select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date     from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a     cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b     cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c ) a where a.Date between '2010-01-20' and '2010-01-24'```
.

Trying to figure out how it works :).
• Feb 24, 2011, 08:09
r937
Quote:

Originally Posted by tomalex0
Trying to figure out how it works :).

meanwhile, you could also just create a numbers table that is large enough to cover the largest date range that you want to handle...
Code:

```CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY ); INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),...```
then that query simplifies to --
Code:

```SELECT DATE('2011-02-01') -- start date         + INTERVAL n DAY AS thedate   from numbers  WHERE DATE('2011-02-01') + INTERVAL n DAY     < DATE('2011-03-01') -- end date plus one```
• Feb 25, 2011, 06:32
tomalex0
Hi that too worked, thanks
• Feb 25, 2011, 09:17
r937
you understand the "last day plus one" combined with "less than" (instead of "less than or equal")?

it's so you don't have to bother figuring out feb 29

:)
• Mar 1, 2011, 04:32
tomalex0
Hi Sorry for the late reply

Yea got it :).
So it will be always better to end with Next month's firstDay. So we will never gonna miss 29 feb. Is that you were telling right?

One more question not regarding this
Code:

`SELECT DATE_FORMAT(LAST_DAY(NOW()),"%Y-%m-%d %H:%i:%s")`
If you look into it, the time will be 00:00:00, is there anyway to get it as 23:59:59.
Usually i used to concatenate.
• Mar 1, 2011, 07:08
r937
Quote:

Originally Posted by tomalex0
If you look into it, the time will be 00:00:00

no, it works just fine, the time is the same time as NOW() but the day is the last day of february

but you do ~not~ want to use LAST_DAY in any range test like the one i showed you
• Mar 1, 2011, 07:18
tomalex0
I was just asking in general, if it is possible to get 23:59:59 with date using mysql function.

It will not be needed in the query that you provided.
• Mar 1, 2011, 07:26
r937
Quote:

Originally Posted by tomalex0
I was just asking in general, if it is possible to get 23:59:59 with date using mysql function.

i do not see why you would want to do that, and i strongly encourage you not to do that ...

... but if you insist, you can try this --

DATE_FORMAT(
LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 SECOND
, '%Y-%m-%d %H:%i:%s')
• Mar 1, 2011, 20:54
tomalex0
Thanks for that, i probably won't try it then as you said.
• Mar 2, 2011, 08:49
transio
I like the union-all method of creating "temporary tables".

The following should work:
Code:

```select x.Date from (     select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date     from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a     cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b     cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c ) x where x.Date between '2010-01-20' and '2010-01-24'```
• Mar 2, 2011, 08:52
transio
Quote:

Originally Posted by r937
i do not see why you would want to do that, and i strongly encourage you not to do that ...

... but if you insist, you can try this --

DATE_FORMAT(
LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 SECOND
, '%Y-%m-%d %H:%i:%s')

Why not just:

CONCAT(CURRENT_DATE, ' 23:59:59')?
• Mar 2, 2011, 08:55
r937
Quote:

Originally Posted by transio
Why not just:

CONCAT(CURRENT_DATE, ' 23:59:59')?

because it's very slow (converting a date into a string), and furthermore, leaves the result as a string, which, if you want to use it for anything (such as displaying it as a formatted datetime) has to first be converted into a datetime before the date formatting function converts it back into a string

ewwww :injured:

and besides, using CURRENT_DATE instead of LAST_DAY(CURENT_DATE) puts you well outside of the required last day of the month on approximately 30 out of 31 days
• Mar 2, 2011, 12:42
tomalex0
Quote:

Originally Posted by transio
I like the union-all method of creating "temporary tables".

The following should work:
Code:

```select x.Date from (     select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date     from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a     cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b     cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c ) x where x.Date between '2010-01-20' and '2010-01-24'```

I think it does have some limitation, for example if we want to find dates between 2010-01-20 and 2010-05-20, we will not get whole dates between them. There is some limit of 35 or 50 for that query. Otherwise we have to rewrite query a bit.
• Mar 2, 2011, 15:03
r937
Quote:

Originally Posted by tomalex0
There is some limit of 35 or 50 for that query. Otherwise we have to rewrite query a bit.

let's use the numbers table, you can easily have more than 50 rows...
• Mar 2, 2011, 19:29
tomalex0
Yes thats right