SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you could do a range quite easily by using a numbers table

    but why did you say "without querying a table"?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tomalex0 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi that too worked, thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tomalex0 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tomalex0 View Post
    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')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that, i probably won't try it then as you said.

  12. #12
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #13
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by transio View Post
    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. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tomalex0 View Post
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes thats right


Bookmarks

Posting Permissions

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