Whenever we’re in the middle of a cold snap, as we are right now, I’m tempted to see how the current snap compares to those in the past. The one we’re in right now isn’t all that bad: sixteen days in a row where the minimum temperature is colder than −20°F. In some years, such a threshold wouldn’t even qualify as the definition of a “cold snap,” but right now, it feels like one.
Getting the length of consecutive things in a database isn’t simple. What we’ll do is get a list of all the days where the minimum daily temperature was warmer than −20°F. Then go through each record and count the number of days between the current row and the next one. Most of these will be one, but when the number of days is greater than one, that means there’s one or more observations in between the “warm” days where the minimum temperature was colder than −20°F (or there was missing data).
For example, given this set of dates and temperatures from earlier this year:
Once we select for rows where the temperature is above −20°F we get this:
Now we can grab the start and end of the period (January 2nd + one day and January 8th - one day) and get the length of the cold snap. You can see why missing data would be a problem, since it would create a gap that isn’t necessarily due to cold temperatures.
I couldn't figure out how to get the time periods and check them for validity all in one step, so I wrote a simple function that counts the days with valid data between two dates, then used this function in the real query. Only periods with non-null data on each day during the cold snap were included.
CREATE FUNCTION valid_n(date, date) RETURNS bigint AS 'SELECT count(*) FROM ghcnd_pivot WHERE station_name = ''FAIRBANKS INTL AP'' AND dte BETWEEN $1 AND $2 AND tmin_c IS NOT NULL' LANGUAGE SQL RETURNS NULL ON NULL INPUT;
Here we go:
SELECT rank() OVER (ORDER BY days DESC) AS rank, start, "end", days FROM ( SELECT start + interval '1 day' AS start, "end" - interval '1 day' AS end, interv - 1 AS days, valid_n(date(start + interval '1 day'), date("end" - interval '1 day')) as valid_n FROM ( SELECT dte AS start, lead(dte) OVER (ORDER BY dte) AS end, lead(dte) OVER (ORDER BY dte) - dte AS interv FROM ( SELECT dte FROM ghcnd_pivot WHERE station_name = 'FAIRBANKS INTL AP' AND tmin_c > f_to_c(-20) ) AS foo ) AS bar WHERE interv >= 17 ) AS f WHERE days = valid_n ORDER BY days DESC;
And the top 10:
There have been seven cold snaps that lasted 16 days (including the one we’re currently in), tied for 45th place.
Keep in mind that defining days where the daily minimum is −20°F or colder is a pretty generous definition of a cold snap. If we require the minimum temperatures be below −40° the lengths are considerably shorter:
I think it’s also interesting that only three (marked with a grey background) of the top ten cold snaps defined at −20°F appear in those that have a −40° threshold.