Generate multiple records without querying a table

[tomalex0]

Its known that

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.

[r937]

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

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

[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

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 .

[r937]

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

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

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

[tomalex0]

Hi that too worked, thanks

[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

[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

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.

[r937]

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

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

[r937]

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

[tomalex0]

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

[transio]

I like the union-all method of creating "temporary tables".

The following should work:

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'

[transio]

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

[r937]

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

[tomalex0]

I like the union-all method of creating "temporary tables".

The following should work:

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.

[r937]

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

[tomalex0]

Yes thats right