(bigquery) how number of hours event is happening within multiple dates

Question

So my data looks like this:

DATE TEMPERATURE
2012-01-13 23:15:00 UTC 0
2012-01-14 01:35:00 UTC 5
2012-01-14 02:15:00 UTC 6
2012-01-14 03:15:00 UTC 8
2012-01-14 04:15:00 UTC 0
2012-01-14 04:55:00 UTC 0
2012-01-14 05:15:00 UTC -2
2012-01-14 05:35:00 UTC 0

I am trying to calculate the amount of time a zip code temperature will drop to 0 or below on any given day. On the 13th, it only happens for a very short amount of time so we don't really care. I want to know how to calculate the number of minutes this happens on the 14th, since it looks like a significantly (and consistently) cold day.

I want the query to add two more columns.

The first column added would be the time difference between the rows on a given date. So row 3- row 2=40 mins and row 4-row3=60 mins.

The second column would total the amount of minutes for a whole day the minutes the temperature has dropped to 0 or below. Here row 2-4 would be ignored. From row 5-8, total time that the temperature was 0 or below would be about 90 mins

It should end up looking like this:

DATE TEMPERATURE MINUTES_DIFFERENCE TOTAL_MINUTES
2012-01-13 23:15:00 UTC 0 0 0
2012-01-14 01:35:00 UTC 5 140 0
2012-01-14 02:15:00 UTC 6 40 0
2012-01-14 03:15:00 UTC 8 60 0
2012-01-14 04:15:00 UTC 0 60 60
2012-01-14 04:55:00 UTC 0 30 90
2012-01-14 05:15:00 UTC-2 20 110
2012-01-14 05:35:00 UTC 0 20 130

Answer 1

Use below

select *, 
  sum(minutes_difference) over(order by date) total_minutes
from (
  select *, 
    ifnull(timestamp_diff(timestamp(date), lag(timestamp(date)) over(order by date), minute), 0) as minutes_difference
  from your_table
)            

if applied to sample data in your question - output is

Update to answer updated question

select * except(new_grp, grp), 
  sum(if(temperature > 0, 0, minutes_difference)) over(partition by grp order by date) total_minutes
from (
  select *, countif(new_grp) over(order by date) as grp
  from (
    select *, 
      ifnull(timestamp_diff(timestamp(date), lag(timestamp(date)) over(order by date), minute), 0) as minutes_difference,
      ifnull(((temperature <= 0) and (lag(temperature) over(order by date) > 0)) or 
      ((temperature > 0) and (lag(temperature) over(order by date) <= 0)), true) as new_grp
    from your_table
  )
)

with output