Professional Web Applications Themes

Query using integer date string - MySQL

I'm a somewhat educated newbie in advanced MySQL queries. I'm attempting to create a variety of "top 10" lists related to a database of meteorology information. This database records sensor data every five minutes, and has been doing so since 2000. For the sake of this post, let's assume that my database has only two fields: recdate -- bigint(12) -- and temp -- float(7,3). The recdate format is YYYYMMDDhhmm. Creating a query to find the maximum temperature since the inception of the database proved pretty easy: SELECT recdate, temp FROM wxtable WHERE recdate BETWEEN 200001010000 AND 200702042355 ORDER BY temp ...

  1. #1

    Default Query using integer date string

    I'm a somewhat educated newbie in advanced MySQL queries.

    I'm attempting to create a variety of "top 10" lists related to a
    database of meteorology information. This database records sensor data
    every five minutes, and has been doing so since 2000. For the sake of
    this post, let's assume that my database has only two fields: recdate
    -- bigint(12) -- and temp -- float(7,3). The recdate format is
    YYYYMMDDhhmm.

    Creating a query to find the maximum temperature since the inception
    of the database proved pretty easy:

    SELECT recdate, temp FROM wxtable WHERE recdate BETWEEN 200001010000
    AND 200702042355 ORDER BY temp DESC LIMIT 1

    Result:
    200608011630 96.8

    But when creating a TOP 10 list of highest daily temperatures over the
    period was more problematic:

    SELECT temp FROM wxtable WHERE recdate BETWEEN 200001010000 AND
    200702042355 ORDER BY temp DESC LIMIT 10

    Result:
    200608011630 96.8
    200608011720 96.8
    200608011625 96.6
    200608011635 96.6
    200608011650 96.6
    200608011655 96.6
    200608011700 96.6
    200608011715 96.6
    200607161455 96.6
    200607161450 96.6


    Because the weather data is recorded every five minutes, most of my
    results came from the readings around the time of the all-time max
    reading above. So I did get the top 10 readings, but not limited to
    one per day over the specified range.

    My question is:
    Based on the database's use of a 12-digit integer for recording the
    date, how can I limit my results to display only one maximum
    temperature for each 24-hour period and therefore get a true top 10
    list of daily temperatures.

    Thanks...

    PWX Guest

  2. #2

    Default Re: Query using integer date string

    PWX wrote: 

    Use MAX() and GROUP BY utilising only the date portion of recdate.


    Paul Guest

  3. #3

    Default Re: Query using integer date string

    On Feb 4, 3:34 pm, "Paul Lautman" <com> wrote: 










    >
    > Use MAX() and GROUP BY utilising only the date portion of recdate.[/ref]

    Thanks for the suggestion. Here's what I came up with. I got
    unpredictable results with this new query:

    SELECT MAX(temp), recdate FROM wxtable GROUP BY SUBSTR(recdate,1,8)
    ORDER BY temp DESC LIMIT 10

    Result:
    96.8 200608010000
    94.9 200608020000
    85.9 200608030000
    91.6 200507250000
    90.3 200307040000
    94.3 200506250000
    87.4 200506280000
    94.5 200207020000
    92.9 200607170000
    86.7 200208020000

    I ran this query a second time without LIMIT, and while I did appear
    to get one unique high temp for each day, the sort was haphazard. The
    one at the top is indeed the hottest. But the others in the top 10
    shown here are not the hottest. So I think I'm closing in but
    something about my sort isn't solid. Anything pop out any anyone?

    PWX Guest

  4. #4

    Default Re: Query using integer date string

    On Feb 5, 12:37 pm, "PWX" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Thanks for the suggestion. Here's what I came up with. I got
    > unpredictable results with this new query:
    >
    > SELECT MAX(temp), recdate FROM wxtable GROUP BY SUBSTR(recdate,1,8)
    > ORDER BY temp DESC LIMIT 10
    >
    > Result:
    > 96.8 200608010000
    > 94.9 200608020000
    > 85.9 200608030000
    > 91.6 200507250000
    > 90.3 200307040000
    > 94.3 200506250000
    > 87.4 200506280000
    > 94.5 200207020000
    > 92.9 200607170000
    > 86.7 200208020000
    >
    > I ran this query a second time without LIMIT, and while I did appear
    > to get one unique high temp for each day, the sort was haphazard. The
    > one at the top is indeed the hottest. But the others in the top 10
    > shown here are not the hottest. So I think I'm closing in but
    > something about my sort isn't solid. Anything pop out any anyone?[/ref]

    Hate to answer my own post, but I'm still needing some guidance.
    Strawberry over in comp.databases told me to use the Strawberry query,
    which got me close:

    SELECT wx.recdate, wx.temp
    FROM wxtable wx,(
    SELECT max( temp ) as maxtemp, recdate
    FROM wxtable
    GROUP BY recdate
    ) maxresults
    WHERE wx.recdate = maxresults.recdate
    AND wx.temp = maxresults.maxtemp
    ORDER BY temp DESC LIMIT 10

    Result:
    200608011630 96.8
    200608011720 96.8
    200607161450 96.6
    200607161455 96.6
    200608011625 96.6
    200608011635 96.6
    200608011650 96.6
    200608011655 96.6
    200608011700 96.6
    200608011715 96.6

    This is almost identical to my first post. But I got some results and
    I have more confidence that these are exactly in order. So, if I try
    to use SUBSTR with GROUP BY to find the high temperature that is
    unique to each 24-hour period....

    SELECT wx.recdate, wx.temp
    FROM wxtable wx,(
    SELECT max( temp ) as maxtemp, recdate
    FROM wxtable
    GROUP BY SUBSTR(recdate,1,8)
    ) maxresults
    WHERE wx.recdate = maxresults.recdate
    AND wx.temp = maxresults.maxtemp
    ORDER BY temp DESC LIMIT 10

    The unintended result is that I get the highest temperatures at
    MIDNIGHT (0000) in the database:

    200608030000 85.9
    200207090000 82.2
    200607020000 82
    200509140000 78.8
    200407140000 77.7
    200204190000 76.7
    200408280000 76.3
    200208230000 75.1
    200407230000 74.9
    200608270000 74.7

    Ideas?

    PWX Guest

  5. #5

    Default Re: Query using integer date string

    On Feb 5, 5:37 pm, "PWX" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Thanks for the suggestion. Here's what I came up with. I got
    > unpredictable results with this new query:
    >
    > SELECT MAX(temp), recdate FROM wxtable GROUP BY SUBSTR(recdate,1,8)
    > ORDER BY temp DESC LIMIT 10
    >
    > Result:
    > 96.8 200608010000
    > 94.9 200608020000
    > 85.9 200608030000
    > 91.6 200507250000
    > 90.3 200307040000
    > 94.3 200506250000
    > 87.4 200506280000
    > 94.5 200207020000
    > 92.9 200607170000
    > 86.7 200208020000
    >
    > I ran this query a second time without LIMIT, and while I did appear
    > to get one unique high temp for each day, the sort was haphazard. The
    > one at the top is indeed the hottest. But the others in the top 10
    > shown here are not the hottest. So I think I'm closing in but
    > something about my sort isn't solid. Anything pop out any anyone?- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    OK, first of all the "Strawberry query" uses LEFT JOINs not
    subqueries. Dunno how you ended up with that.

    Next, your sort problem is because you are sorting on `temp` rather
    than teh MAX(temp) that is actually in the output column.

    Try altering your query to this:
    SELECT MAX(temp), recdate FROM wxtable GROUP BY SUBSTR(recdate,1,8)
    ORDER BY 1 DESC LIMIT 10

    Or if you prefer, do this:

    SELECT MAX(temp) max_temp, recdate FROM wxtable GROUP BY
    SUBSTR(recdate,1,8)
    ORDER BY max_temp DESC LIMIT 10

    Captain Guest

  6. #6

    Default Re: Query using integer date string

    On Feb 6, 5:41 am, "Captain Paralytic" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]





    >
    > OK, first of all the "Strawberry query" uses LEFT JOINs not
    > subqueries. Dunno how you ended up with that.
    >
    > Next, your sort problem is because you are sorting on `temp` rather
    > than teh MAX(temp) that is actually in the output column.
    >
    > Try altering your query to this:
    > SELECT MAX(temp), recdate FROM wxtable GROUP BY SUBSTR(recdate,1,8)
    > ORDER BY 1 DESC LIMIT 10
    >
    > Or if you prefer, do this:
    >
    > SELECT MAX(temp) max_temp, recdate FROM wxtable GROUP BY
    > SUBSTR(recdate,1,8)
    > ORDER BY max_temp DESC LIMIT 10[/ref]

    Thanks, Captain. I think we'll call that other thing I put together
    the Cranberry Query. Yours worked perfectly....

    PWX Guest

Similar Threads

  1. string to integer
    By Mark Fenbers in forum Macromedia Flash Data Integration
    Replies: 10
    Last Post: May 23rd, 08:08 AM
  2. Integer to Hex string?
    By Steve in forum Mac Programming
    Replies: 6
    Last Post: December 7th, 07:24 AM
  3. check string rep's integer
    By sinister in forum PHP Development
    Replies: 7
    Last Post: November 24th, 09:27 AM
  4. ado xtra - integer variable in sql-string
    By janosch webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 2
    Last Post: October 14th, 10:18 PM
  5. php array problem string to integer
    By Sébastien in forum PHP Development
    Replies: 0
    Last Post: August 3rd, 06:17 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139