sun, 08-feb-2015, 14:13

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:

date tmin_f
2015‑01‑02 −15
2015‑01‑03 −20
2015‑01‑04 −26
2015‑01‑05 −30
2015‑01‑06 −30
2015‑01‑07 −26
2015‑01‑08 −17

Once we select for rows where the temperature is above −20°F we get this:

date tmin_f
2015‑01‑02 −15
2015‑01‑08 −17

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:

Top ten longest cold snaps (−20°F or colder minimum temp)
rank start end days
1 1917‑11‑26 1918‑01‑01 37
2 1909‑01‑13 1909‑02‑12 31
3 1948‑11‑17 1948‑12‑13 27
4 1925‑01‑16 1925‑02‑10 26
4 1947‑01‑12 1947‑02‑06 26
4 1943‑01‑02 1943‑01‑27 26
4 1968‑12‑26 1969‑01‑20 26
4 1979‑02‑01 1979‑02‑26 26
9 1980‑12‑06 1980‑12‑30 25
9 1930‑01‑28 1930‑02‑21 25

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:

Top ten longest cold snaps (−40° or colder minimum temp)
rank start end days
1 1964‑12‑25 1965‑01‑11 18
2 1973‑01‑12 1973‑01‑26 15
2 1961‑12‑16 1961‑12‑30 15
2 2008‑12‑28 2009‑01‑11 15
5 1950‑02‑04 1950‑02‑17 14
5 1989‑01‑18 1989‑01‑31 14
5 1979‑02‑03 1979‑02‑16 14
5 1947‑01‑23 1947‑02‑05 14
9 1909‑01‑14 1909‑01‑25 12
9 1942‑12‑15 1942‑12‑26 12
9 1932‑02‑18 1932‑02‑29 12
9 1935‑12‑02 1935‑12‑13 12
9 1951‑01‑14 1951‑01‑25 12

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.

Meta Photolog Archives