Thread: Generate multiple records without querying a table

1. 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.

2. you could do a range quite easily by using a numbers table

but why did you say "without querying a table"?

3. 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 .

4. 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```

5. Hi that too worked, thanks

6. 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

7. 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.

8. 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

9. 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.

10. 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')

11. Thanks for that, i probably won't try it then as you said.

12. 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'```

13. 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')?

14. 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

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

15. 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.

16. 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...

17. Yes thats right

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•