Sources

Historical temperature data is offered for download by Environment Canada¹. It is in CSV² format and is separated by location and year. So one CSV file for each town or city and each year of data. Getting this data is easy if you are running Linux or macOS and Environment Canada provides the instructions³. Each location has a unique numerical identifier as well as a listed date range by year (i.e. 1935 to 2023). You can find this information in an inventory CSV file⁴ also provided by Environment Canada.

Not every location has the desired data you may want. Saskatoon has data from 1892 to 2012. I chose Muenster, about 110km East of Saskatoon because it had data from 1904 to 2023. To retrieve that data you would run the following from the Linux (or macOS) command line:

for year in `seq 1904 2022`;do for month in `seq 1 1`;do wget --content-disposition "https://climate.weather.gc.ca/climate_data/bulk_data_e.html?format=csv&stationID=2973&Year=${year}&Month=${month}&Day=14&timeframe=2&submit= Download+Data" ;done;done

The bolded parts are all that I had to edit in order to get the data I wanted, daily temperatures for Muenster for the period 1904 to 2022.

The CSV files from the above command provide a lot of data. If you open one in a text editor and look at the top line you will see the column headers. On this site I used the following columns: Max Temp, Year, Month, Day. Having the date separated into its three parts like that made it simple to compare specific days over several years.

Querying

In order to query this data I imported it into a database (MySQL in this case). The table that the CSV data was to go into needed to duplicate the CSV columns as so:

CREATE TABLE greenwood (
    lng DECIMAL(6,2) NULL,
    lat DECIMAL(6,2) NULL,
    stationname VARCHAR(32) NULL,
    climateid INT NULL,
    fulldate DATE NOT NULL,
    year INT NOT NULL,
    month INT NOT NULL,
    day INT NOT NULL,
    quality VARCHAR(255) NULL,
    maxtemp DECIMAL(6,2) NOT NULL,
    maxtflag CHAR NULL,
    mintemp DECIMAL(6,2) NOT NULL,
    mintflag CHAR NULL,
    meantemp DECIMAL(6,2) NULL,
    meantflag CHAR NULL,
    heatdays DECIMAL(6,2)NULL,
    heatdflag CHAR NULL,
    cooldays DECIMAL(6,2) NULL,
    cooldflag CHAR NULL,
    totalrain DECIMAL(6,2) NULL,
    totalrflag CHAR NULL,
    totalsnow DECIMAL(6,2) NULL,
    totalsflag CHAR NULL,
    totalprecip DECIMAL(6,2) NULL,
    totalpflag CHAR NULL,
    snowgrnd DECIMAL(6,2) NULL,
    snowgflag CHAR NULL,
    dirgust INT NULL,
    dirgflag CHAR NULL,
    spdgust DECIMAL(6,2) NULL,
    spdgflag CHAR NULL,
    PRIMARY KEY (fulldate)
);
                        

With the table created, data is imported from within the MySQL console with the following (repeated for every year):

LOAD DATA INFILE '/home/doug/climate/muenster/en_climate_daily_SK_4015440_1904_P1D.csv' INTO TABLE muenster FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS;

With all the data loaded you can finally query it. For the charts I queried specific years representing the hottest periods of the 20th century and the 21st century. For each year chosen, I am simply returning the temperature that was the highest for each month. That is important to note, highest temperature for the month not an average temperature. The SQL⁵ is as follows:

SELECT MAX(maxtemp) FROM $location WHERE year = $year AND month = :month AND day BETWEEN 1 AND 31

This is run once for each of the 12 months of the year. For months with less than 31 days nothing is returned from the database on those dates of course.

The table that displays below the chart shows all 365 days of a year (not a specific year) in a grid with the highest temperature and year it occurred. If there are ties then they are both shown. The SQL is as follows:

SELECT maxtemp, year FROM $location WHERE month = :monthInner AND day = :dayInner AND (maxtemp = (SELECT MAX(maxtemp) FROM $location WHERE month = :monthOuter AND day = :dayOuter))

This query is run in two loops. The outer loop is the 12 months of the year and for each month the inner loop is 1 to 31 for the days. This allows every single specific day/month to be compared for all years available. The years below 1950, which represent the warmest period of the 20th century, are highlighted in orange. The years above 2015, which represent the warmest period of the 21st century, are highlighted in red.

---