SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help w/ complex mysql query

    Hey guys, I have what seems like an easy problem to solve, but for the life of me, I can't come up with a successful (and effecient) query. If anyone could help, I'd appreciate it.

    ok, I have a DB table that collects ping info every 5 minutes that looks like this:
    Code:
    DATE        TIME      src  dest    latency
    -------------------------------------------
    2001-12-01  00:00:00  1    2       20
    2001-12-01  00:05:00  1    2       21
    ...
    2001-12-03  14:55:00  1    2       19
    You get the idea, pretty straightforward. Because of other uses of this info, the date and time had to be separated (this is a HUGE table and we needed to avoid a LIKE statement get query dates for reports). So what I want to do is be able to say "give me all the rows from 2001-12-01 @ 02:00 to 2001-12-05 @ 03:00." This SHOULD return all dates from 12-01@2am - 12-05@3am, but instead it returns from 2am to 3am only for each date. I've tried every way I can think of, with no success (except for concat, which takes FAR too long).

    this doesn't work:
    SELECT latency from table where src='1' and dest='2' and (date >= '2001-12-01' and time >= '02:00') and (date <= '2001-12-05' and time <= '03:00')

    Any ideas?
    Thanks,
    --Viral
    A computer without Windows is like a chocolate cake without mustard.

  2. #2
    purple monkey dishwasher scoates's Avatar
    Join Date
    Nov 2001
    Location
    Montreal
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    could you maybe convert your date and time fields using UNIX_TIMESTAMP, and then add them together to come up with a start and end time?

    then you could compare them that way.

    S

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2000
    Location
    Yeppoon, Australia
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you've got you're AND logic mixed up a bit. This should get you what you want:
    Code:
    SELECT latency 
    FROM table 
    WHERE src='1' and dest='2' 
    AND date BETWEEN '2001-12-01' AND '2001-12-05'
    AND time BETWEEN '02:00' AND '03:00'
    BETWEEN is just the same as using the >= comparisons you were using but gives cleaner code. Alternatively you could just replace the BETWEEN's with the equivalent >= comparisons adn parenthesis.

    If that's not it we can try, try again
    Knowledge is knowing that a tomatoe is a fruit; wisdom is not putting it in a fruit salad.

  4. #4
    purple monkey dishwasher scoates's Avatar
    Join Date
    Nov 2001
    Location
    Montreal
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think that would work.
    I haven't tested this, but that seems to be only matchine between those dates AND those times..

    which means that date]2001-12-03 time]04:00 would match the first half of your where clause but not the second.

    Am I wrong?

    if MySQL supported subqueries, you could do this:

    SELECT latency
    FROM table
    WHERE src='1' AND dest='2'
    AND time BETWEEN '02:00' AND '03:00'
    AND date IN (SELECT id FROM table WHERE date BETWEEN '2001-12-01' AND '2001-12-05');
    (you DO have an id [primary key] field, right?)
    but that's moot.

    S

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Location
    Romania
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you use a PHP script to query, I think this would work:

    PHP Code:

    <?php
    //your initial and final values go here

    $start_date=...
    $stop_date=...
    $start_time=...
    $stop_time=...

    //build the query

    $query1="select latency from table where src='1' and dst='2' and date>='$start_date'and date<='$stop_date'";

    if (
    date=='$start_date'
       
    $query2="and time>='$start_time'";
    if (
    date=='$stop_date')
       
    $query2="and time<='$stop_time'";

    $query=$query1.$query2;

    ?>
    I didn't try the script, but I hope it helps.
    Last edited by sleepless; Dec 13, 2001 at 12:24.

  6. #6
    SitePoint Zealot
    Join Date
    Jun 2000
    Location
    Yeppoon, Australia
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, it becomes clear. I understand what you're asking now - timestamps would be perfect

    I don't think this would work (nor would what I posted before) - I think it would return the same as the previous query?? (the subquery would have to return a date for it to be executed without error)

    SELECT latency
    FROM table
    WHERE src='1' AND dest='2'
    AND time BETWEEN '02:00' AND '03:00'
    AND date IN (SELECT id FROM table WHERE date BETWEEN '2001-12-01' AND '2001-12-05');
    OK, try this. If it doesn't work read through the 3 queries below that lead to it and they'll hopefully lead to another idea.

    SELECT latency
    FROM table
    WHERE src='1' AND dest='2'
    AND (time >= '02:00' AND date = '2001-12-01')
    OR (date BETWEEN '2001-12-02' AND '2001-12-04')
    OR (time <= '03:00' AND date = '2001-12-05')

    You gotta love Boolean logic

    ______________________________________________

    If the above works, ignore this:

    I think you may have to resort to several queries (3) to get what you want if you don't want to use CONCAT or similar thing - anything you use would need to CONCAT the two together first as far as I can understand.

    1. First get all in the remaining part of the first day

    SELECT latency
    FROM table
    WHERE src='1' AND dest='2'
    AND time >= '02:00' AND date = '2001-12-01'


    2. Then get all the data for the full days (dates incremented because BETWEEN is inclusive)

    SELECT latency
    FROM table
    WHERE src='1' AND dest='2'
    AND date BETWEEN '2001-12-02' AND '2001-12-04'


    3. Third is just the opposite of the first - get remaining part of last day

    SELECT latency
    FROM table
    WHERE src='1' AND dest='2'
    AND time <= '03:00' AND date = '2001-12-05'
    Last edited by mr_than; Dec 13, 2001 at 12:22.
    Knowledge is knowing that a tomatoe is a fruit; wisdom is not putting it in a fruit salad.

  7. #7
    purple monkey dishwasher scoates's Avatar
    Join Date
    Nov 2001
    Location
    Montreal
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    my query should have read:
    Code:
    ...
    AND id IN (SELECT id FROM table WHERE date 
    ...
    not date IN

    S

  8. #8
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Viola!

    Thanks for your input guys! Using a little bit of help from everyone, I finally came up with the correct query:
    Code:
    SELECT date, timestamp, dest, lat from TABLE 
    WHERE source = '1' and (dest = '2')
    AND ((date = '2001-12-01' and timestamp >= '02:00') 
    OR (date > '2001-12-01' and date < '2001-12-05') 
    OR (date = '2001-12-05' and timestamp <= '03:00')) 
    GROUP BY date, timestamp, dest ORDER BY dest
    Whew!

    --Viral
    A computer without Windows is like a chocolate cake without mustard.

  9. #9
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A much better way would have been to store the date + time as a unix timestamp as suggested, it would result in a much more efficient query.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  10. #10
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree. Unfortunately this was impossible because of other applications that make use of this data. The original version of this table DID have a timestamp, but had to be broken into date and time because of it's main job. This is an incredibly huge table (100s of GB) that is used, among other things, to populate many other tables with aggregate (SUMs, COUNTs, AVGs) information. Without going into great detail, suffice it to say that breaking the timestamp into 2 fields was absolutely mandatory for speed purposes. (Both on hand DBAs concurred). Now in hindsight it's true that we probably should have actually made three fields: date, time & timestamp, but we didn't think of that at the time because it's only recently been used in a way that requires both fields be queried.

    Anyway, thanks for your input guys. The final query works in a flash and I'm generating charts and graphs on the fly from the database, much to the enjoyment of my boss. Dynamic images rock!

    --Viral
    A computer without Windows is like a chocolate cake without mustard.


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
  •