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