A MySQL Bug? A Programmer Bug?

I have been trying to teach myself data analysis. I have been keeping some weather data in a MySQL database so I can take it out, munge it and graph it. The above example is done with the Perl module Chart::Clicker, which I am sure will take a large role in my current job. Especially if it works fast and graphs correctly.

On that topic, take a look at it. The red line represents the day's highs, the green line, the day's lows, and the purlple line the day's average temperature. Here's the code that determines what the numbers should be.

my $sql ;
$sql .= 'SELECT  ' .
        'cast( avg( temp_f ) as unsigned  ) , ' .
        'min(temp_f) , ' .
        'max(temp_f) , ' .
        'DATE(time)    ' .
        'from weather_data where zip = "47909"  ' .
        'and DATEDIFF( CURDATE() , DATE(time) ) < 7 ' .
        'group by DATE(time)' ;

And here's the schema for this code.

CREATE TABLE weather_data  (
    id          int(10) not null auto_increment primary key ,
    time        TIMESTAMP not null ,
    zip         varchar(5) ,
    city        varchar(255) ,
    temp_c      varchar(255) ,
    temp_f      varchar(255) ,
    humidity    varchar(255) ,
    wind        varchar(255) ,
    conditions  varchar(255)
    ) ;

I'll admit that it's pretty lazy. If the current conditions stretches beyond 20 characters, that's some complex weather. The main problem, I believe, is that temperature data is being saved as a string rather than a signed integer. Why it mostly shows up as error on that one day, I don't know. Here's all the temperature data for that day. (Thank you, Google Weather API.)

time	temp_f	temp_c
2009-12-10 00:00:02	28	-2
2009-12-10 00:10:02	28	-2
2009-12-10 00:30:03	28	-2
2009-12-10 00:40:02	28	-2
2009-12-10 00:50:02	28	-2
2009-12-10 01:00:02	28	-2
2009-12-10 01:10:02	28	-2
2009-12-10 01:30:02	16	-9
2009-12-10 01:40:02	16	-9
2009-12-10 02:00:03	16	-9
2009-12-10 02:10:02	16	-9
2009-12-10 02:20:02	16	-9
2009-12-10 02:40:02	14	-10
2009-12-10 02:50:02	15	-9
2009-12-10 03:00:02	15	-9
2009-12-10 03:10:02	15	-9
2009-12-10 03:20:02	15	-9
2009-12-10 03:30:03	14	-10
2009-12-10 03:40:01	14	-10
2009-12-10 03:50:01	15	-9
2009-12-10 04:00:03	15	-9
2009-12-10 04:10:02	14	-10
2009-12-10 04:20:02	14	-10
2009-12-10 04:30:02	12	-11
2009-12-10 04:40:02	12	-11
2009-12-10 05:00:02	13	-11
2009-12-10 05:10:02	12	-11
2009-12-10 05:20:02	12	-11
2009-12-10 05:30:03	12	-11
2009-12-10 05:40:02	12	-11
2009-12-10 05:50:02	12	-11
2009-12-10 06:00:03	12	-11
2009-12-10 06:10:02	11	-12
2009-12-10 06:20:02	11	-12
2009-12-10 06:30:02	11	-12
2009-12-10 06:40:01	11	-12
2009-12-10 07:00:03	11	-12
2009-12-10 07:10:02	10	-12
2009-12-10 07:20:02	10	-12
2009-12-10 07:30:02	10	-12
2009-12-10 07:40:04	10	-12
2009-12-10 07:50:02	10	-12
2009-12-10 08:00:09	9	-13
2009-12-10 08:10:05	9	-13
2009-12-10 08:20:06	9	-13
2009-12-10 08:30:05	9	-13
2009-12-10 08:40:06	9	-13
2009-12-10 08:50:05	9	-13
2009-12-10 09:00:06	8	-13
2009-12-10 09:10:08	8	-13
2009-12-10 09:20:08	8	-13
2009-12-10 09:30:08	8	-13
2009-12-10 09:40:13	8	-13
2009-12-10 09:50:07	9	-13
2009-12-10 10:00:07	10	-12
2009-12-10 10:10:07	10	-12
2009-12-10 10:30:07	10	-12
2009-12-10 10:40:10	10	-12
2009-12-10 10:50:07	12	-11
2009-12-10 11:00:07	12	-11
2009-12-10 11:10:05	12	-11
2009-12-10 11:20:08	12	-11
2009-12-10 11:40:08	12	-11
2009-12-10 12:00:09	14	-10
2009-12-10 12:30:07	14	-10
2009-12-10 12:50:07	14	-10
2009-12-10 13:00:09	14	-10
2009-12-10 13:10:06	15	-9
2009-12-10 13:20:08	15	-9
2009-12-10 13:30:06	15	-9
2009-12-10 13:50:07	17	-8
2009-12-10 14:00:09	17	-8
2009-12-10 14:10:07	17	-8
2009-12-10 14:20:08	17	-8
2009-12-10 14:40:09	18	-8
2009-12-10 14:50:08	17	-8
2009-12-10 15:20:08	18	-8
2009-12-10 15:30:15	18	-8
2009-12-10 15:40:08	18	-8
2009-12-10 15:50:11	19	-7
2009-12-10 16:00:11	19	-7
2009-12-10 16:10:08	19	-7
2009-12-10 16:20:08	19	-7
2009-12-10 16:30:07	19	-7
2009-12-10 16:40:10	19	-7
2009-12-10 16:50:08	19	-7
2009-12-10 17:00:03	19	-7
2009-12-10 17:10:03	19	-7
2009-12-10 17:30:02	19	-7
2009-12-10 17:40:02	18	-8
2009-12-10 17:50:02	18	-8
2009-12-10 18:00:02	18	-8
2009-12-10 18:10:02	17	-8
2009-12-10 18:20:02	17	-8
2009-12-10 18:30:02	17	-8
2009-12-10 18:40:02	16	-9
2009-12-10 18:50:01	16	-9
2009-12-10 19:00:02	17	-8
2009-12-10 19:10:02	17	-8
2009-12-10 19:20:02	17	-8
2009-12-10 19:40:02	17	-8
2009-12-10 19:50:02	17	-8
2009-12-10 20:00:02	17	-8
2009-12-10 20:20:02	17	-8
2009-12-10 20:30:02	17	-8
2009-12-10 20:40:02	17	-8
2009-12-10 21:00:03	17	-8
2009-12-10 21:10:01	17	-8
2009-12-10 21:20:02	18	-8
2009-12-10 21:30:02	18	-8
2009-12-10 21:40:02	18	-8
2009-12-10 22:00:02	17	-8
2009-12-10 22:10:02	17	-8
2009-12-10 22:20:02	18	-8
2009-12-10 22:30:02	18	-8
2009-12-10 22:40:02	18	-8
2009-12-10 22:50:01	18	-8
2009-12-10 23:00:02	18	-8
2009-12-10 23:10:01	18	-8
2009-12-10 23:20:02	18	-8
2009-12-10 23:30:02	18	-8
2009-12-10 23:40:02	18	-8
2009-12-10 23:50:02	18	-8

I could show you the temp_c graph, but that's just beyond the pale wrong, which makes sense as the temperature has been bouncing between positive and negative the whole time.

Ultimately, I got used to Perl's ability to effortlessly bounce between strings and numbers, and here I'm bitten. I can only use that data in a meaningful way if I pull it all and manipulate it in Perl, which cuts off some labor-saving tools. Let that be a lesson to you, and to me.