Consecutive Hours Below Freezing

[ai5u]

Greetings,

With our record breaking temperatures and snowfall occurring over the last 10 days and still counting here in Oklahoma and Texas (Polar Vortex), is it possible to add a new entry into the records section that will track consecutive hours/days below freezing? I was trying to help my local TV weatherman out by providing my data to him, but he had to up-me-one by finding something that wasn't included in the CumulusMX weather console records section.

No big deal if its not possible. Just a thought for grins and giggles.

Thanks

Tim - NG7P

[sfws]

Tracking cumulative hours below a selected temperature functionality has existed for over a decade already, please read the Cumulus Wiki documentation at https://cumuluswiki.org/a/Heat/cold_deg ... hill_hours section 1.3.

You can see this cumulative number reported on the example this year web template included with MX, as well as in "today.ini" log file.

You used the descriptor "consecutive", that suggests you want a figure that is reset to zero whenever temperature goes above zero, and therefore restarts the count when it next freezes???

The Chill Hours functionality has not changed all the years since it was originally implemented. There was a previous request for extra related functionality to Steve Loft, the creator of the software, but he "lost" his request database (like he lost the earliest support forum posts) before all the requests were addressed, and I can't remember what was proposed. However, please note that developer Mark recently said (in respect of another recent request) he is doing bug fixing rather than implementing new features in his current MX development.

I run a batch job that (effectively) compares the "today.ini" for successive days to extract daily incremental figure, I have a novel web page that shows those (and other derivatives) for each day in last week; and both cumulative and daily incremental figures are stored by a batch script automatically run each day of last decade by Cumulus in my custom database daily summary table schema. An alternative approach MX only approach for storing just daily cumulative figures is described in documentation at https://cumuluswiki.org/a/MX_Administra ... mary_table. Having daily incremental values allows me to see which days it was 24 hours and which days it was 0 hours, and it would be possible to report last time a particular figure was seen.

[mcrossley]

If you upload to a MySQL database then that is the sort of thing it is fairly easy to make a query for yourself.

The closest I do is consecutive frost days - longest run of days where the temperature drops below zero at any time.

But it's an interesting idea* - any up votes?

* Does not mean it would get done any time soon!

[ConligWX]

upvoted - yep I'll second the motion, though hear in NI we rarely see that many consecutive days below 0°C but its another nice stat to have all the same.

[mcrossley]

Bonus points for anyone coming up with a MySQL query for this! My first attempts time out before they return

[mcrossley]

Best I can do so far is...

Code: Select all

SELECT 
  MIN(LogDateTime) AS start,
  MAX(LogDateTime) AS end,
  MIN(state) AS state,
  TIMEDIFF(MAX(LogDateTime), MIN(LogDateTime)) AS hrs
FROM (
  SELECT 
    @r := @r + 
          (@state!=IF(h.Temp<=0,1,0) OR @state IS NULL) 
      AS state_change,
    @state := IF(h.Temp<=0,1,0) AS current,
    h.LogDateTime, IF(h.Temp<=0,1,0) AS state
  FROM (
    -- one-row virtual table
    SELECT @r:=0, @state:=NULL ) AS vars  
    CROSS JOIN Monthly AS h                   
    -- order to track state changes
    ORDER BY LogDateTime, state
  ) q
GROUP BY state_change
HAVING state = 1
ORDER BY hrs DESC;

Which takes 11 seconds(!) on 998,000+ rows.
And the answer appears to be the period 2010-12-16 16:20 to 2010-12-22 13:00 is my longest time at or below freezing, at 140 hours...

My "Top 10"...

Code: Select all

start                end                 hrs
2010-12-16 16:20:00  2010-12-22 13:00:00 140:40:00
2010-12-22 15:30:00  2010-12-26 12:50:00 93:20:00 
2018-02-27 15:55:00  2018-03-02 12:00:00 68:05:00 
2010-12-05 15:30:00  2010-12-07 12:50:00 45:20:00 
2010-11-27 15:10:00  2010-11-29 11:20:00 44:10:00 
2010-12-07 14:30:00  2010-12-09 10:40:00 44:10:00 
2010-11-30 15:40:00  2010-12-02 11:00:00 43:20:00 
2010-12-02 15:20:00  2010-12-03 22:50:00 31:30:00 
2012-02-03 15:50:00  2012-02-04 20:10:00 28:20:00 
2013-01-18 07:40:00  2013-01-19 07:40:00 24:00:00 

[PaulMy]

I had to go to my Data Summary http://www.komokaweather.com/cumulusmx/ ... hp?lang=en to see we went above freezing yesterday after 16 days consecutive below freezing. But that wasn't even the longest as 2015 was a c o l d winter with 20 consecutive days below freezing in February and only 2 days in the month above freezing.

I also had to go to my TOP10 http://komokaweather.com/mysql/top10.php (thanks Mark) to see my consecutive days with freezing, and that this winter is the most and still nearly a month to go.

Now if I could figure out how to get the snow days and depth in a nice graph!

Brrrrr
Paul