White Christmas

HadCET

The HadCET data is "the longest available instrumental record of temperature in the world", currently available from the UK Met Office. It provides the daily mean temperature for the centre of England since 1772.

From that table you can see that the mean daily temperature for 1st January 2001 was recorded as 40 meaning 4.0°C, the temperature for 2nd January 2001 has 77 which translates to 7.7°C

The February temperatures are in the m2 column, notice that -999 has been entered for invalid cells such as 31 Feb 2001.

+------+----+------+------+------+------+------+------+------+------+------+------+------+------+
| yr   | dy | m1   | m2   | m3   | m4   | m5   | m6   | m7   | m8   | m9   | m10  | m11  | m12  |
+------+----+------+------+------+------+------+------+------+------+------+------+------+------+
| 2001 |  1 |   40 |   11 |    9 |  112 |   87 |  131 |  185 |  180 |  150 |  151 |   91 |   98 |
| 2001 |  2 |   77 |   32 |   -4 |  114 |   99 |  126 |  207 |  163 |  168 |  143 |   99 |   29 |
| 2001 |  3 |   52 |   66 |   -3 |   84 |  105 |  100 |  209 |  166 |  153 |  142 |   87 |   55 |
| 2001 |  4 |   50 |   57 |    5 |   69 |   72 |  130 |  219 |  151 |  134 |  139 |   90 |   73 |
| 2001 |  5 |   47 |   68 |   12 |   71 |   82 |  136 |  217 |  161 |  139 |  159 |   75 |   82 |
| 2001 |  6 |   43 |   83 |   57 |   90 |   76 |  135 |  201 |  173 |  141 |  142 |   95 |   46 |
| 2001 |  7 |   49 |   85 |   86 |   69 |   90 |  110 |  185 |  161 |  153 |  128 |  112 |   62 |
| 2001 |  8 |   51 |   34 |   87 |   71 |   93 |  102 |  163 |  155 |  147 |  127 |   50 |   40 |
| 2001 |  9 |   22 |   21 |   90 |  101 |  102 |  101 |  157 |  141 |  121 |  132 |   33 |   16 |
| 2001 | 10 |   23 |   51 |  102 |   96 |  147 |  110 |  160 |  152 |  119 |  125 |   44 |   19 |
| 2001 | 11 |   29 |   81 |  101 |   72 |  172 |  106 |  141 |  152 |  142 |  150 |   78 |   20 |
| 2001 | 12 |   40 |   79 |   60 |   74 |  168 |  138 |  144 |  171 |  142 |  153 |  101 |   46 |
| 2001 | 13 |   31 |   40 |   57 |   57 |  165 |  148 |  143 |  195 |  124 |  160 |   53 |   47 |
| 2001 | 14 |   24 |   35 |   64 |   75 |  118 |  144 |  132 |  205 |  124 |  155 |   40 |   15 |
| 2001 | 15 |   18 |   44 |   53 |   91 |  120 |  153 |  127 |  210 |  129 |  154 |   43 |   13 |
| 2001 | 16 |  -12 |   46 |   54 |   73 |  128 |  138 |  133 |  176 |  125 |  117 |   55 |   23 |
| 2001 | 17 |   -9 |   21 |   26 |   52 |   89 |  123 |  143 |  157 |  109 |  138 |   83 |   20 |
| 2001 | 18 |   -5 |    3 |   28 |   44 |  101 |  128 |  149 |  167 |  124 |  147 |   73 |   38 |
| 2001 | 19 |   -8 |   26 |   20 |   54 |  119 |  160 |  137 |  181 |  129 |  113 |   63 |   48 |
| 2001 | 20 |   -5 |   36 |   22 |   54 |  130 |  156 |  146 |  169 |  138 |  135 |   67 |   26 |
| 2001 | 21 |   13 |   62 |   26 |   52 |  129 |  140 |  153 |  159 |  116 |  104 |   96 |   41 |
| 2001 | 22 |   45 |   73 |   60 |   64 |  131 |  139 |  169 |  179 |  119 |  118 |   91 |   12 |
| 2001 | 23 |   69 |   49 |   77 |   84 |  150 |  160 |  170 |  179 |  103 |  137 |   48 |   18 |
| 2001 | 24 |   69 |   27 |   68 |   68 |  157 |  173 |  155 |  191 |  127 |  130 |   68 |   34 |
| 2001 | 25 |   48 |   14 |   49 |   85 |  137 |  191 |  181 |  193 |  123 |  124 |  115 |   45 |
| 2001 | 26 |   47 |   27 |   38 |   90 |  157 |  219 |  184 |  171 |  112 |  124 |   51 |   27 |
| 2001 | 27 |   40 |   27 |   50 |   84 |  160 |  187 |  194 |  152 |  145 |  122 |   48 |   40 |
| 2001 | 28 |   11 |   23 |   70 |   99 |  176 |  170 |  206 |  143 |  166 |  116 |   76 |   43 |
| 2001 | 29 |   20 | -999 |   66 |   76 |  160 |  177 |  230 |  149 |  168 |  106 |   96 |   18 |
| 2001 | 30 |   25 | -999 |   83 |   80 |  149 |  170 |  201 |  154 |  141 |  140 |  124 |   15 |
| 2001 | 31 |   40 | -999 |  100 | -999 |  130 | -999 |  192 |  146 | -999 |  101 | -999 |   -4 |
| 2002 |  1 |  -18 |   86 |   45 |  113 |   93 |  143 |  131 |  161 |  126 |  145 |  114 |   87 |
| 2002 |  2 |  -18 |  109 |   31 |  100 |   84 |  162 |  135 |  156 |  140 |  156 |  114 |   83 |
+------+----+------+------+------+------+------+------+------+------+------+------+------+------+

{{#ev:youtube|YyqgTOCl0I8}}

Days, Months and Years

The units are 10th of a degree Celcius. The columns are yr and dy for year and day of month. The next twelve columns are for January through to December.

Show the average daily temperature for August 10th 1964

SELECT m5/10 FROM hadcet
  WHERE yr=1962 AND dy=20

SELECT m8/10 FROM hadcet
  WHERE yr=1964 AND dy=10

preteen Dickens

Charles Dickens is said to be responsible for the tradition of expecting snow at Christmas Daily Telegraph. Show the temperature on Christmas day (25th December) for each year of his childhood. He was born in February 1812 - so he was 1 (more or less) in December 1812.

Show the twelve temperatures.

SELECT yr-1811 as age FROM hadcet
  WHERE yr BETWEEN 1812 and 1812+12 AND dy=25

SELECT yr-1811 as age, m12/10 as temp FROM hadcet
  WHERE yr BETWEEN 1812 and 1812+11 AND dy=25

Minimum Temperature Before Christmas

We declare a White Christmas if there was a day with an average temperature below zero between 21^st and 25^th of December.

For each age 1-12 show which years were a White Christmas. Show 'White Christmas' or 'No snow' for each age.

SELECT yr-1811 as age FROM hadcet
  WHERE yr BETWEEN 1812 and 1812+12 AND dy=25

SELECT age,
  CASE WHEN MIN(m12)<0 THEN 'White Christmas' ELSE 'No Snow' END
FROM
(SELECT yr-1811 as age,dy,m12 FROM hadcet
   WHERE yr BETWEEN 1812 and 1812+11 AND dy BETWEEN 21 AND 25) AS dpc
GROUP BY age

White Christmas Count

A person's White Christmas Count (wcc) is the number of White Christmases they were exposed to as a child (between 3 and 12 inclusive assuming they were born at the beginning of the year and were about 1 year old on their first Christmas).

Charles Dickens's wcc was 8.

List all the years and the wcc for children born in each year of the data set. Only show years where the wcc was at least 7.

SELECT yr-1811 as age FROM hadcet
  WHERE yr BETWEEN 1812 and 1812+12 AND dy=25

SELECT * FROM
  (SELECT yob,COUNT(CASE WHEN mt<0 THEN 1 END) AS wcc FROM
     (SELECT yob,yr-yob-1 as age,MIN(m12) as mt
        FROM hadcet JOIN (SELECT distinct yr as yob FROM hadcet WHERE yr BETWEEN 1700 and 2017) yrs
       WHERE yr BETWEEN yob+2 and yob+11 AND dy BETWEEN 21 AND 25
      GROUP BY yob,age) as yam
   GROUP BY yob) as ywcc
WHERE wcc>=7

+------+-----+
| yob  | wcc |
+------+-----+
| 1805 |   7 |
| 1806 |   7 |
| 1807 |   7 |
| 1808 |   8 |
| 1809 |   9 |
| 1810 |   8 |
| 1811 |   8 |
| 1812 |   8 |
| 1813 |   7 |
+------+-----+

{{#ev:youtube|Chw_PiuqGnU}}

Climate Change

Here are the average temperatures for August by decade. You decide.

SELECT ROUND(yr,-1) decade, ROUND(AVG(NULLIF(m8,-999))/10,1)
  FROM hadcet
GROUP BY ROUND(yr,-1)