This is an automated archive.
The original was posted on /r/mysql by /u/WK5H on 2023-08-05 20:28:44+00:00.
I have just recently started to learn sql, and I started a personal project to help me learn a bit.I found a way to export weather data from the local weather stations in csv format.Here’s where I’m getting tripped up.
For simplicity reasons, all my table types are varchar(255), which is the only way I can get a successful import from the csv file.
I can do a “select * from weather_data where TMAX > 99;” and it returns the rows where the high temp of the day is greater than 99.
But if I want to find the highest recorded temperature from the TMAX column of weather_data with “select MAX(TMAX) from weather_data;”, it only returns the value of 99.
Not sure if this helps or not, but there is a lot of data in the CSV file where there wasn’t a recorded temp for that day, so there was an empty value recored. In face, there are a lot of values imported from the csv with no reported value due to no data recorded for that day.
For instance, here’s a line from the csv file
“USC00414841”,“KNAPP 2 SW, TX US”,“1934-07-13”,“0.00”,“0.0”,“0.0”,
my columns are:
| STATION | varchar(255) | YES | | NULL | |
| NAME | varchar(255) | YES | | NULL | |
| DATE | varchar(255) | YES | | NULL | |
| AWND | varchar(255) | YES | | NULL | |
| DAPR | varchar(255) | YES | | NULL | |
| DASF | varchar(255) | YES | | NULL | |
| MDPR | varchar(255) | YES | | NULL | |
| MDSF | varchar(255) | YES | | NULL | |
| PRCP | varchar(255) | YES | | NULL | |
| SNOW | varchar(255) | YES | | NULL | |
| SNWD | varchar(255) | YES | | NULL | |
| TMAX | varchar(255) | YES | | NULL | |
| TMIN | varchar(255) | YES | | NULL | |
| TOBS | varchar(255) | YES | | NULL | |
| WDF2 | varchar(255) | YES | | NULL | |
| WDF5 | varchar(255) | YES | | NULL | |
| WSF2 | varchar(255) | YES | | NULL | |
| WSF5 | varchar(255) | YES | | NULL | |
| WT01 | varchar(255) | YES | | NULL | |
| WT02 | varchar(255) | YES | | NULL | |
| WT03 | varchar(255) | YES | | NULL | |
| WT04 | varchar(255) | YES | | NULL | |
| WT05 | varchar(255) | YES | | NULL | |
| WT06 | varchar(255) | YES | | NULL | |
| WT07 | varchar(255) | YES | | NULL | |
| WT08 | varchar(255) | YES | | NULL | |
| WT09 | varchar(255) | YES | | NULL | |
| WT11 | varchar(255) | YES | | NULL | |
| WT14 | varchar(255) | YES | | NULL | |
| WT16 | varchar(255) | YES | | NULL | |
| WT18 | varchar(255) | YES | | NULL | |
I appreciate any and all help - thanks!