outages_expanded

This table was created using the output of the following SQL statement:

select
  outage,
  min(snapshots.[when]) as earliest,
  max(snapshots.[when]) as latest,
  round(cast(max(snapshots.[when]) - min(snapshots.[when]) as float) / 3600, 2) as possible_duration_hours,
  outage not in (select outage from most_recent_snapshot) as probably_ended,
  min(outage_snapshots.estCustAffected) as min_estCustAffected,
  max(outage_snapshots.estCustAffected) as max_estCustAffected,
  min(regionName.name) as region,
  min(outage_snapshots.latitude) as latitude,
  min(outage_snapshots.longitude) as longitude
from outage_snapshots
  join snapshots on snapshots.id = outage_snapshots.snapshot
  join regionName on outage_snapshots.regionName = regionName.id
group by outage;

41,487 rows

View and edit SQL

Suggested facets: probably_ended

outage earliest latest num_snapshots possible_duration_hours probably_ended min_estCustAffected max_estCustAffected region latitude longitude
629455 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 21:30:40 UTC 88 14.51 1 1 1 Fortuna 40.55785 -124.14553
629544 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 11:10:19 UTC 26 4.17 1 31 31 San Jose 37.25649 -121.94472
629993 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 15:20:55 UTC 51 8.34 1 1 1 Santa Rosa 38.50277 -122.71038
630055 October 01, 2019 - 07:00:15 UTC October 03, 2019 - 00:20:22 UTC 240 41.34 1 1 1 Rio Vista 38.12243 -121.83043
630855 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 14:01:07 UTC 43 7.01 1 4 4 Oakland 37.73454 -122.13937
631014 October 01, 2019 - 07:00:15 UTC October 03, 2019 - 17:50:30 UTC 344 58.84 1 1 1 Olivehurst 39.0775 -121.52568
631074 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 07:10:38 UTC 2 0.17 1 1 1 San Jose 37.29857 -121.97725
631133 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 14:20:29 UTC 45 7.34 1 1 1 Rio Vista 38.17765 -121.71222
631174 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 07:10:38 UTC 2 0.17 1 30 30 Oakland 37.84695 -122.21344
631354 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 13:00:54 UTC 37 6.01 1 1 1 San Luis Obispo 35.30441 -120.77233
631639 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 10:40:13 UTC 23 3.67 1 7 7 Bakersfield 35.35389 -118.95864
631794 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 08:20:12 UTC 9 1.33 1 1 1 Riverdale 36.37987 -119.92783
632115 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 07:30:19 UTC 4 0.5 1 1 1 San Mateo 37.56994 -122.31673
632117 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 08:10:17 UTC 8 1.17 1 8 8 Cambria 35.54847 -121.08542
632153 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 11:20:10 UTC 27 4.33 1 1 1 Walnut Creek 37.89203 -122.03894
632414 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 10:40:13 UTC 23 3.67 1 1 1 Napa 38.31483 -122.33439
632474 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 08:20:12 UTC 9 1.33 1 1 1 Lemoore 36.29815 -119.76184
632633 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 19:10:23 UTC 74 12.17 1 1 1 Clarksburg 38.37341 -121.54748
632634 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 11:30:14 UTC 28 4.5 1 1 1 Red Bluff 40.18335 -122.2206
633157 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 09:30:17 UTC 16 2.5 1 3 3 Salinas 36.61393 -121.56663
633233 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 07:50:31 UTC 6 0.84 1 1 1 San Jose 37.3361 -121.9288
633373 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 18:30:21 UTC 70 11.5 1 1 1 Clarksburg 38.36723 -121.54198
633394 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 13:50:20 UTC 42 6.83 1 1 1 Soledad 36.45004 -121.35438
633410 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 07:10:38 UTC 2 0.17 1 6 6 Moraga 37.83977 -122.12694
633433 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 20:00:23 UTC 79 13.0 1 1 1 Woodland 38.65738 -121.77805
633435 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 10:00:30 UTC 19 3.0 1 13 13 Firebaugh 36.81337 -120.25931
633444 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 10:20:18 UTC 21 3.33 1 5 5 Felton 37.04293 -122.09002
633451 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 09:00:21 UTC 13 2.0 1 33 33 Livermore 37.66234 -121.69662
633458 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 09:40:13 UTC 17 2.67 1 5 5 Eureka 40.78042 -124.18235
633460 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 08:40:23 UTC 11 1.67 1 1 1 Placerville 38.69685 -120.77764
633461 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 07:40:24 UTC 5 0.67 1 1 1 Pismo Beach 35.15723 -120.672
633463 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 13:40:20 UTC 41 6.67 1 296 296 San Francisco 37.75335 -122.42778
633467 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 13:50:20 UTC 42 6.83 1 48 48 San Francisco 37.76364 -122.38797
633470 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 13:40:20 UTC 41 6.67 1 97 97 San Francisco 37.75354 -122.42749
633477 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 13:40:20 UTC 41 6.67 1 100 100 San Francisco 37.75326 -122.42691
633478 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 11:10:19 UTC 26 4.17 1 1 1 Merced 37.25687 -120.43449
633479 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 13:50:20 UTC 42 6.83 1 45 45 San Francisco 37.75334 -122.42578
633480 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 12:00:17 UTC 31 5.0 1 1 1 San Leandro 37.70999 -122.17583
633481 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 17:00:36 UTC 61 10.01 1 4 4 Castro Valley 37.69717 -122.08699
633483 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 13:20:25 UTC 39 6.34 1 17 17 San Carlos 37.50476 -122.25824
633484 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 13:40:20 UTC 41 6.67 1 1 20 San Francisco 37.75475 -122.42896
633485 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 11:30:14 UTC 28 4.5 1 6 6 Valley Springs 38.1891 -120.82691
633488 October 01, 2019 - 07:00:15 UTC October 02, 2019 - 03:30:20 UTC 124 20.5 1 27 27 Kettleman City 35.99202 -119.57146
633492 October 01, 2019 - 07:00:15 UTC October 01, 2019 - 07:30:19 UTC 4 0.5 1 0 274 Auberry 37.09211 -119.55301
633503 October 01, 2019 - 07:10:38 UTC October 01, 2019 - 07:40:24 UTC 4 0.5 1 1 1 Fresno 36.76072 -119.7904
633504 October 01, 2019 - 07:20:14 UTC October 01, 2019 - 08:40:23 UTC 9 1.34 1 1 5 Merced 37.25754 -120.43418
633505 October 01, 2019 - 07:20:14 UTC October 01, 2019 - 16:40:31 UTC 57 9.34 1 1 1 Pleasanton 37.66473 -121.92102
633506 October 01, 2019 - 07:40:24 UTC October 01, 2019 - 07:50:31 UTC 2 0.17 1 1 1 Martinez 37.95652 -122.11536
633507 October 01, 2019 - 07:40:24 UTC October 01, 2019 - 07:50:31 UTC 2 0.17 1 1 1 Martinez 37.95491 -122.11647
633508 October 01, 2019 - 08:00:15 UTC October 01, 2019 - 09:40:13 UTC 11 1.67 1 1 5 Tracy 37.77151 -121.42335
633509 October 01, 2019 - 08:10:17 UTC October 01, 2019 - 08:10:17 UTC 1 0.0 1 11 11 Martinez 37.95498 -122.11657
633510 October 01, 2019 - 08:10:17 UTC October 01, 2019 - 08:10:17 UTC 1 0.0 1 21 21 Martinez 37.95855 -122.11232
633512 October 01, 2019 - 10:20:18 UTC October 01, 2019 - 11:20:10 UTC 7 1.0 1 1 1 Fresno 36.81268 -119.76425
633513 October 01, 2019 - 10:30:19 UTC October 01, 2019 - 10:40:13 UTC 2 0.17 1 504 504 Moskowite Corner 38.44526 -122.19656
633514 October 01, 2019 - 10:50:22 UTC October 01, 2019 - 11:30:14 UTC 5 0.66 1 6 8 San Francisco 37.78796 -122.38817
633516 October 01, 2019 - 11:10:19 UTC October 01, 2019 - 22:20:22 UTC 68 11.17 1 41 969 Atascadero 35.52131 -120.71256
633518 October 01, 2019 - 11:20:10 UTC October 01, 2019 - 12:10:17 UTC 6 0.84 1 1 1 Chico 39.73895 -121.85833
633519 October 01, 2019 - 11:30:14 UTC October 01, 2019 - 11:30:14 UTC 1 0.0 1 1 1 Antioch 37.99355 -121.84289
633524 October 01, 2019 - 11:40:17 UTC October 02, 2019 - 01:00:19 UTC 81 13.33 1 94 94 Campbell 37.29072 -121.95834
633526 October 01, 2019 - 11:40:17 UTC October 01, 2019 - 12:20:16 UTC 5 0.67 1 1 3 Campbell 37.29066 -121.95854
633528 October 01, 2019 - 11:40:17 UTC October 01, 2019 - 12:40:10 UTC 7 1.0 1 1 2615 Campbell 37.29 -121.93561
633529 October 01, 2019 - 12:00:17 UTC October 01, 2019 - 13:10:27 UTC 8 1.17 1 1 1 San Jose 37.33543 -121.84029
633530 October 01, 2019 - 12:10:17 UTC October 01, 2019 - 14:30:20 UTC 15 2.33 1 1 1 Friant 37.10504 -119.59209
633531 October 01, 2019 - 12:20:16 UTC October 01, 2019 - 12:20:16 UTC 1 0.0 1 1 1 Oakland 37.77165 -122.1718
633532 October 01, 2019 - 12:30:16 UTC October 01, 2019 - 15:40:17 UTC 20 3.17 1 1 1 Oakland 37.79305 -122.18292
633534 October 01, 2019 - 12:40:10 UTC October 01, 2019 - 13:10:27 UTC 4 0.5 1 11 11 San Jose 37.29078 -121.97536
633535 October 01, 2019 - 12:40:10 UTC October 01, 2019 - 13:50:20 UTC 8 1.17 1 1 1 Dixon 38.4844 -121.78781
633537 October 01, 2019 - 12:50:16 UTC October 01, 2019 - 12:50:16 UTC 1 0.0 1 1 1 Campbell 37.29243 -121.96658
633538 October 01, 2019 - 12:50:16 UTC October 01, 2019 - 17:20:17 UTC 28 4.5 1 1 1 Concord 37.95276 -121.95995
633540 October 01, 2019 - 13:00:54 UTC October 01, 2019 - 21:00:29 UTC 49 7.99 1 19 19 Paso Robles 35.58976 -120.83334
633541 October 01, 2019 - 13:00:54 UTC October 01, 2019 - 15:00:21 UTC 13 1.99 1 36 36 Berkeley 37.86079 -122.29415
633542 October 01, 2019 - 13:10:27 UTC October 02, 2019 - 03:10:19 UTC 85 14.0 1 11 14 Watsonville 36.98981 -121.73539
633543 October 01, 2019 - 13:10:27 UTC October 01, 2019 - 14:20:29 UTC 8 1.17 1 1 1 San Francisco 37.75363 -122.50554
633544 October 01, 2019 - 13:10:27 UTC October 01, 2019 - 13:30:39 UTC 3 0.34 1 1 1 San Jose 37.36572 -121.83425
633545 October 01, 2019 - 13:10:27 UTC October 01, 2019 - 21:00:29 UTC 48 7.83 1 4 4 Paso Robles 35.64097 -120.84891
633546 October 01, 2019 - 13:20:25 UTC October 02, 2019 - 04:30:16 UTC 92 15.16 1 9 20 Walnut Creek 37.87055 -122.03021
633547 October 01, 2019 - 13:30:39 UTC October 01, 2019 - 22:30:14 UTC 55 8.99 1 4 4 Burney 41.17032 -121.68866
633548 October 01, 2019 - 13:50:20 UTC October 01, 2019 - 18:30:21 UTC 29 4.67 1 1 1 Santa Maria 34.93489 -120.45556
633549 October 01, 2019 - 14:01:07 UTC October 02, 2019 - 01:00:19 UTC 67 10.99 1 1 3 Campbell 37.29066 -121.95854
633550 October 01, 2019 - 13:50:20 UTC October 02, 2019 - 01:40:17 UTC 72 11.83 1 1 1 Huron 36.13872 -120.12345
633551 October 01, 2019 - 13:50:20 UTC October 02, 2019 - 01:40:17 UTC 72 11.83 1 1 1 Huron 36.13871 -120.12345
633552 October 01, 2019 - 13:50:20 UTC October 02, 2019 - 01:40:17 UTC 72 11.83 1 1 1 Huron 36.13868 -120.12345
633554 October 01, 2019 - 14:01:07 UTC October 01, 2019 - 15:50:23 UTC 12 1.82 1 54 54 Palo Cedro 40.54673 -122.23841
633558 October 01, 2019 - 14:01:07 UTC October 01, 2019 - 15:20:55 UTC 9 1.33 1 3 8 Clayton 37.92282 -121.91097
633559 October 01, 2019 - 14:01:07 UTC October 01, 2019 - 16:10:35 UTC 14 2.16 1 1 1 San Bruno 37.61449 -122.40357
633560 October 01, 2019 - 14:20:29 UTC October 01, 2019 - 16:10:35 UTC 12 1.83 1 1 1673 Cottonwood 40.38603 -122.26401
633561 October 01, 2019 - 14:30:20 UTC October 01, 2019 - 14:30:20 UTC 1 0.0 1 1 1 Creston 35.49357 -120.52968
633563 October 01, 2019 - 14:30:20 UTC October 01, 2019 - 14:30:20 UTC 1 0.0 1 4 4 Creston 35.46705 -120.55162
633568 October 01, 2019 - 14:30:20 UTC October 01, 2019 - 18:11:02 UTC 23 3.68 1 1 1 Oakland 37.74225 -122.16565
633569 October 01, 2019 - 14:30:20 UTC October 01, 2019 - 14:30:20 UTC 1 0.0 1 2 2 Creston 35.46595 -120.55312
633570 October 01, 2019 - 14:30:20 UTC October 01, 2019 - 14:30:20 UTC 1 0.0 1 1 1 Creston 35.47852 -120.57438
633571 October 01, 2019 - 14:30:20 UTC October 01, 2019 - 17:30:21 UTC 19 3.0 1 1 8 Clovis 36.81966 -119.70132
633572 October 01, 2019 - 14:30:20 UTC October 02, 2019 - 12:10:34 UTC 122 21.67 1 1 1 San Francisco 37.72031 -122.38494
633573 October 01, 2019 - 14:30:20 UTC October 02, 2019 - 00:00:27 UTC 58 9.5 1 28 28 Guinda 38.84408 -122.20342
633574 October 01, 2019 - 14:30:20 UTC October 01, 2019 - 15:40:17 UTC 8 1.17 1 1 1 Woodland 38.72937 -121.782
633575 October 01, 2019 - 14:30:20 UTC October 01, 2019 - 20:20:33 UTC 36 5.84 1 1 113 Creston 35.49544 -120.53068
633576 October 01, 2019 - 14:30:20 UTC October 01, 2019 - 14:30:20 UTC 1 0.0 1 1 1 Templeton 35.48927 -120.56357
633577 October 01, 2019 - 14:30:20 UTC October 01, 2019 - 14:30:20 UTC 1 0.0 1 1 1 Creston 35.4698 -120.56969
633580 October 01, 2019 - 14:40:18 UTC October 01, 2019 - 17:00:36 UTC 15 2.34 1 1 1 Kerman 36.67409 -120.14191
633581 October 01, 2019 - 14:40:18 UTC October 01, 2019 - 22:10:36 UTC 46 7.5 1 1 1 Atascadero 35.51321 -120.70711

Next page

Advanced export

JSON shape: default, array, newline-delimited, object

CSV options:

CREATE TABLE outages_expanded (
  outage INT PRIMARY KEY,
  earliest INT,
  latest INT,
  num_snapshots TEXT,
  possible_duration_hours FLOAT,
  probably_ended TEXT,
  min_estCustAffected INT,
  max_estCustAffected INT,
  region TEXT,
  latitude TEXT,
  longitude TEXT
)
Powered by Datasette · Query took 452.655ms · About: About this site