Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?

N404KR

Registration

OBR LEASING
PO BOX 725
BRISTOW, VA

-- For how many minutes in each hour did we see N404KR?
orbital=# select a.registration, date_trunc('hour', r.timestamp) as day_and_hour, count(distinct date_trunc('minute', r.timestamp)) as num_minutes_seen from reports as r, aircraft as a where r.icao = a.modes and r.icao = 'A4BC6E' group by day_and_hour, r.icao, a.registration order by day_and_hour asc;
registration | day_and_hour | num_minutes_seen
--------------+------------------------+------------------
N404KR | 2015-03-03 07:00:00-08 | 7
N404KR | 2015-03-03 12:00:00-08 | 6
N404KR | 2015-03-03 15:00:00-08 | 4
N404KR | 2015-03-03 20:00:00-08 | 9
N404KR | 2015-03-04 08:00:00-08 | 15
N404KR | 2015-03-04 09:00:00-08 | 59
N404KR | 2015-03-04 10:00:00-08 | 35
N404KR | 2015-03-05 06:00:00-08 | 4
N404KR | 2015-03-05 11:00:00-08 | 7
N404KR | 2015-03-07 13:00:00-08 | 24
N404KR | 2015-03-07 14:00:00-08 | 28
N404KR | 2015-03-07 15:00:00-08 | 40
N404KR | 2015-03-07 16:00:00-08 | 50
N404KR | 2015-03-07 17:00:00-08 | 34
N404KR | 2015-03-09 16:00:00-07 | 2
N404KR | 2015-03-09 17:00:00-07 | 12
N404KR | 2015-03-09 20:00:00-07 | 14
N404KR | 2015-03-09 21:00:00-07 | 1
N404KR | 2015-03-10 17:00:00-07 | 14
N404KR | 2015-03-10 20:00:00-07 | 12
N404KR | 2015-03-15 18:00:00-07 | 6
N404KR | 2015-03-15 19:00:00-07 | 52
N404KR | 2015-03-15 20:00:00-07 | 3
N404KR | 2015-03-17 19:00:00-07 | 6
N404KR | 2015-03-18 00:00:00-07 | 5
N404KR | 2015-03-19 08:00:00-07 | 5
N404KR | 2015-03-19 12:00:00-07 | 10
N404KR | 2015-03-19 13:00:00-07 | 6
N404KR | 2015-03-19 15:00:00-07 | 14
N404KR | 2015-03-19 19:00:00-07 | 13
N404KR | 2015-03-23 08:00:00-07 | 3
N404KR | 2015-03-23 09:00:00-07 | 58
N404KR | 2015-03-23 10:00:00-07 | 52
N404KR | 2015-03-23 11:00:00-07 | 56
N404KR | 2015-03-23 12:00:00-07 | 58
N404KR | 2015-03-23 13:00:00-07 | 4
N404KR | 2015-03-23 15:00:00-07 | 7
N404KR | 2015-03-23 20:00:00-07 | 8
N404KR | 2015-03-25 16:00:00-07 | 33
N404KR | 2015-03-25 17:00:00-07 | 8
N404KR | 2015-03-26 08:00:00-07 | 7
N404KR | 2015-03-26 13:00:00-07 | 7
N404KR | 2015-03-27 09:00:00-07 | 18
N404KR | 2015-03-27 10:00:00-07 | 20
N404KR | 2015-03-27 11:00:00-07 | 26
N404KR | 2015-03-27 12:00:00-07 | 27
N404KR | 2015-04-01 08:00:00-07 | 5
N404KR | 2015-04-01 13:00:00-07 | 19
N404KR | 2015-04-02 10:00:00-07 | 1
N404KR | 2015-04-02 14:00:00-07 | 13
N404KR | 2015-04-13 10:00:00-07 | 32
N404KR | 2015-04-13 11:00:00-07 | 59
N404KR | 2015-04-13 12:00:00-07 | 60
N404KR | 2015-04-13 13:00:00-07 | 58
N404KR | 2015-04-13 14:00:00-07 | 9
N404KR | 2015-04-14 10:00:00-07 | 42
N404KR | 2015-04-14 11:00:00-07 | 60
N404KR | 2015-04-14 12:00:00-07 | 60
N404KR | 2015-04-14 13:00:00-07 | 60
N404KR | 2015-04-14 14:00:00-07 | 1
N404KR | 2015-04-20 09:00:00-07 | 24
N404KR | 2015-04-20 10:00:00-07 | 37
N404KR | 2015-04-20 11:00:00-07 | 40
N404KR | 2015-04-20 12:00:00-07 | 60
N404KR | 2015-04-20 13:00:00-07 | 6
N404KR | 2015-04-23 06:00:00-07 | 5
N404KR | 2015-04-23 07:00:00-07 | 1
N404KR | 2015-04-23 10:00:00-07 | 6
N404KR | 2015-04-27 07:00:00-07 | 3
N404KR | 2015-04-27 12:00:00-07 | 3
N404KR | 2015-04-29 09:00:00-07 | 10
N404KR | 2015-04-29 14:00:00-07 | 13
N404KR | 2015-04-30 15:00:00-07 | 5
N404KR | 2015-05-02 15:00:00-07 | 2
N404KR | 2015-05-02 18:00:00-07 | 9
N404KR | 2015-05-02 19:00:00-07 | 7
N404KR | 2015-05-03 11:00:00-07 | 11
N404KR | 2015-05-03 15:00:00-07 | 15
N404KR | 2015-05-03 17:00:00-07 | 10
N404KR | 2015-05-03 21:00:00-07 | 15
N404KR | 2015-05-04 17:00:00-07 | 1
N404KR | 2015-05-04 22:00:00-07 | 5
N404KR | 2015-05-27 14:00:00-07 | 28
N404KR | 2015-05-27 15:00:00-07 | 27
N404KR | 2015-05-27 16:00:00-07 | 41
N404KR | 2015-05-27 17:00:00-07 | 44
N404KR | 2015-05-27 18:00:00-07 | 8
N404KR | 2015-07-10 14:00:00-07 | 2
N404KR | 2015-07-27 20:00:00-07 | 3
N404KR | 2015-07-30 15:00:00-07 | 6
N404KR | 2015-07-31 12:00:00-07 | 7
N404KR | 2015-08-04 12:00:00-07 | 1
N404KR | 2015-08-04 15:00:00-07 | 8
N404KR | 2015-08-23 13:00:00-07 | 2
N404KR | 2015-08-23 19:00:00-07 | 1
N404KR | 2015-08-26 09:00:00-07 | 28
N404KR | 2015-08-26 10:00:00-07 | 34
N404KR | 2015-08-26 11:00:00-07 | 45
N404KR | 2015-08-26 12:00:00-07 | 21
N404KR | 2015-08-31 16:00:00-07 | 6
N404KR | 2015-08-31 17:00:00-07 | 6
N404KR | 2015-09-01 11:00:00-07 | 38
N404KR | 2015-09-01 12:00:00-07 | 60
N404KR | 2015-09-01 13:00:00-07 | 25
N404KR | 2015-09-03 17:00:00-07 | 1
N404KR | 2015-09-03 20:00:00-07 | 7
N404KR | 2015-09-08 07:00:00-07 | 1
N404KR | 2015-09-08 08:00:00-07 | 3
N404KR | 2015-09-08 09:00:00-07 | 9
N404KR | 2015-09-08 10:00:00-07 | 14
N404KR | 2015-09-08 11:00:00-07 | 11
N404KR | 2015-09-14 08:00:00-07 | 15
N404KR | 2015-09-14 09:00:00-07 | 9
N404KR | 2015-09-14 10:00:00-07 | 9
N404KR | 2015-09-17 08:00:00-07 | 3
N404KR | 2015-09-17 12:00:00-07 | 1
(116 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment