SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 35 of 35
  1. #26
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, let's build your query up in stages

    please test this --
    Code:
    SELECT min_date + INTERVAL n*15 MINUTE AS timeslice
      FROM ( SELECT MIN(date_track) AS min_date
                  , MAX(date_track) AS max_date
               FROM tracking ) AS m
    CROSS
      JOIN numbers
     WHERE min_date + INTERVAL n*15 MINUTE <= max_date
    let me know if this gives the intervals you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  2. #27
    SitePoint Member
    Join Date
    Jun 2011
    Location
    France
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's all ok, it gives me allright a 15 minutes interval between the oldest 2011-06-20 20:01:01 and the last 2011-06-21 18:39:14 =>( interval 2011-06-21 18:31:01)
    and i could even change MAX(date_track) with NOW() if i need it.

    So it's all for the best

    i'm just wondering why this request doesn't looks like the one you suggested for westcoastsql in his question because, to me it was the same need, hence my intrusion in this post ...
    Code:
    i know this thread is old, but i have a question for r937. That sql query is very good, but what if there is gaps longer then 15mins in the data?
    
    i need the following to be shown:
    
    date mycount
    2009-01-13 17:00:00 5
    2009-01-13 17:15:00 3
    2009-01-13 17:30:00 0 <- this doesnt show
    2009-01-13 17:45:00 8
    Anyway, i'll stick to my request (er ....yours ) and looking forward to see it giving the count, because if i dumbly add a count(*) in the select, it's not the right right thing to do...

  3. #28
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, so that query gives you the intervals

    you are correct that you can vary the contents of the "m" subquery, like using NOW() instead of MAX(date_track)

    in fact you don't even need the tracking table, you can use constants like this --
    Code:
    SELECT min_date + INTERVAL n*15 MINUTE AS timeslice
      FROM ( SELECT DATE('2011-06-01') AS min_date
                  , DATE('2011-06-30') AS max_date ) AS m
    CROSS
      JOIN numbers
     WHERE min_date + INTERVAL n*15 MINUTE <= max_date
    in any case, we are now ready to do the LEFT OUTER JOIN --
    Code:
    SELECT d.timeslice
         , COUNT(t.id_track)
      FROM ( SELECT min_date + INTERVAL n*15 MINUTE AS timeslice
               FROM ( SELECT DATE('2011-06-01') AS min_date
                           , DATE('2011-06-30') AS max_date ) AS m
             CROSS
               JOIN numbers
              WHERE min_date + INTERVAL n*15 MINUTE <= max_date
           ) AS d
    LEFT OUTER
      JOIN tracking AS t
        ON t.date_track BETWEEN d.timeslice
                            AND d.timeslice + INTERVAL 15 MINUTE
    GROUP
        BY d.timeslice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #29
    SitePoint Member
    Join Date
    Jun 2011
    Location
    France
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    o my ! You just made my day
    This is exactly what i expected. My main question is now answered, and i thank you a thousand times for it.

    But, just for my enlightment, could you explain me or rather show me a way to find an explanation about the n* in INTERVAL n*15 part in the query :

    i've found tons of links about INTERVAL in mysql (used in DATE OR DATETIME)
    INTERVAL 15 MINUTES
    But where does this n comes from ? is it a standard variable name (or could it be "inc", "step"...) ?
    You don't have to explain to me, but if only you could tell me what to google to find answer about it.

    Anyway, thanks again and again

  5. #30
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by olivier89 View Post
    But where does this n comes from ?
    from the numbers table, remember?
    Code:
    CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY );
    INSERT INTO numbers ( n ) VALUES 
     (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),... ;
    Code:
    SELECT min_date + INTERVAL n*15 MINUTE AS timeslice
      FROM ( SELECT MIN(date_track) AS min_date
                  , MAX(date_track) AS max_date
               FROM tracking ) AS m
    CROSS
      JOIN numbers
     WHERE min_date + INTERVAL n*15 MINUTE <= max_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #31
    SitePoint Member
    Join Date
    Jun 2011
    Location
    France
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oooh just forget this silly question

    but i have another one which i hope is not silly :

    Each list (with an id_liste_track) has some 'date_track' values in 'tracking' table as i gave an example of.
    i'd like to add a WHERE clause to restrict the search to an 'id_liste_track'.
    I naively added it to your select, but it returned then only the 'timeslice' associated with non null count. => it seems it "broke" the left outer join effect.

    Code:
    SELECT d.timeslice
         , COUNT(t.id_track)
      FROM ( SELECT min_date + INTERVAL n*15 MINUTE AS timeslice
               FROM ( SELECT DATE('2011-06-01') AS min_date
                           , DATE('2011-06-30') AS max_date ) AS m
             CROSS
               JOIN numbers
              WHERE min_date + INTERVAL n*15 MINUTE <= max_date
           ) AS d
    LEFT OUTER
      JOIN tracking AS t
        ON t.date_track BETWEEN d.timeslice
                            AND d.timeslice + INTERVAL 15 MINUTE
    WHERE t.id_liste_track = 6 
    GROUP
        BY d.timeslice

  7. #32
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's actually a good way to describe it -- the WHERE clause "broke" the left outer join

    change the word WHERE to AND and watch what happens

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

  8. #33
    SitePoint Member
    Join Date
    Jun 2011
    Location
    France
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, it works now. Why should i doubt you

    SQL is pretty confusing, or maybe i'm such a newbie i can't figure this myself : i use WHERE to restrict a request, and i use AND to add a restriction to something (a WHERE for instance). So it means here, i should use a AND :

    - because of the outer join makes the WHERE useless or irrelevant ?
    - because there's already a AND (AND d.timeslice + INTERVAL 15 MINUTE ) ?

    Anyway, i keep asking cause you're a great teacher, and because i like to understand what i'm taught. (Maybe one day, i'll have to reuse this in another context...)

  9. #34
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by olivier89 View Post
    ... and i use AND to add a restriction to something
    that is correct

    you can add a restriction in three places -- the WHERE clause, the HAVING clause, and the ON clause of the join

    that's what it requires here -- if you want to count date_track values for only a specific id_liste_track, this is a join condition, so it goes into the ON clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #35
    SitePoint Member
    Join Date
    Jun 2011
    Location
    France
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, for the rest of my last needs concerning this request (another join) i won't overload this topic, i'll take the time to search for myself or i'll eventually open another discussion. Thnak you for everything.


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
  •