How to query TIME

[troutct]

I lost Keenan Allen from my fantasy team this week so I want to see the other SD WRs targets BY QUARTER to see who got the most action after Allen went out. I see quarter is an element of the TIME field on the play table but I don't know how to refer to the subelements of it..how can I change the query below to return the QUARTER instead of a field value like (Q1,304) which is what is returned now (which I assume means it occured in 1st quarter..3 minutes and 4 seconds into the game ?) . I've done some googling and tried (time).game_phase but get an error column "game_phase" not found in data type game_time

PS: I'm an oracle db guy with background in SQL and none in POSTGRESQL nor Python hence my attempt at doing it this way !

select pp.team,
player.full_name,
time,
sum (ap.receiving_tar) Targets,
sum(pp.receiving_rec) as Receptions
from
player,
play p,
agg_play ap,
play_player pp,
game g
where 1=1
and g.season_year>=2016
and g.season_type='Regular'
and g.gsis_id = ap.gsis_id
and g.gsis_id=p.gsis_id
and p.play_id=ap.play_id
and p.drive_id=ap.drive_id
and p.play_id=pp.play_id
and p.gsis_id=pp.gsis_id
and pp.player_id=Player.player_id
and ap.receiving_tar >0
and player.position in ('WR')
and pp.team='SD'
group by pp.team
,player.full_name,
time
order by full_name desc

[ochawkeye]

Raw SQL stuff is completely Greek to me, but the quarter can be accessed from the time.phase.

Not sure how well this stuff translates from Python to SQL, but here's what it looks like in Python.

import nfldb

db = nfldb.connect()
q = nfldb.Query(db)
q.game(season_year=2016, week=1, season_type='Regular', team='SD')
q.play(receiving_tar__ge=1)
for play in q.as_plays():
    print play
    print play.time
    print play.time.phase, play.time.minutes, play.time.seconds
    print '-'*225

(KC, OWN 25, Q1, 1 and 10) (15:00) (Shotgun) A.Smith pass short middle to J.Maclin to KC 30 for 5 yards (J.Addae).
Q1 00:00
Q1 0 0
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(KC, OWN 30, Q1, 2 and 5) (14:23) (Shotgun) A.Smith pass short left to J.Maclin pushed ob at KC 37 for 7 yards (J.Verrett).
Q1 14:23
Q1 14 23
.
.
.

[troutct]

BINGO..time.phase worked ...thank you !

Now..any idea how I can execute a sql statement to SHOW what those subfields are that I can use in SQL queries ? I might run into this with other time fields, field position, etc...ie. any other composite data fields.

[iliketowel]

Just so you know "304" refers to 304 seconds into the quarter, not 3:04. so Q1,304 counting towards 900 would be 5 minutes, 4 seconds into the quarter.

[troutct]

Yes..thank you for the clarification iliketowel...5 min 4 secs into the 2nd quarter..

Also..clarification on what worked for me..it was (time).phase

(time needs parentheses around it otherwise it thinks time is a tablename.)

so..added and (time).phase in ('Q2','Q3') to my query to find out who was targeted most in 2nd half.

Thanks guys !

[robertromeo5]

I converted the time using a query like this below. If you cut and paste the query below into a text editor it should appear better.

But you can play around with it to see how I had to use case statements and left/right functions to trim off all of the stuff except the numerical number in the subquery, and then added 15 if it was the 2nd quarter, 30 if 3rd, 45 if 4th.

I just used a random game in the where clause to display the functionality

---

select --*
gsis_id
, pos_team
, play_id
, time as old_time
, CASE WHEN fu.quarter = 'Q1' then CAST(fu.time2 as float)/60
WHEN fu.quarter = 'Q2' then ((CAST(fu.time2 as float)/60) + 15)
WHEN fu.quarter = 'Q3' then ((CAST(fu.time2 as float)/60) + 30)
WHEN fu.quarter = 'Q4' then ((CAST(fu.time2 as float)/60) + 45)
END AS time
, CASE when (CAST(fu.yardline_mod as float) < 0) then 'OWN ' || (50 + CAST(fu.yardline_mod as float))
when (CAST(fu.yardline_mod as float) > 0) then 'OPP ' || (50 - CAST(fu.yardline_mod as float))
END AS yardline
from
(select gsis_id
, drive_id
, play_id
, CAST(time as varchar) as time
, right(left(CAST(time as varchar), 3),2) as quarter
, length(CAST(yardline as varchar)) as length
, case when length(CAST(time as varchar)) = 8 then left(right(CAST(time as varchar), 4), 3)
when length(CAST(time as varchar)) = 7 then left(right(CAST(time as varchar), 3), 2)
when length(CAST(time as varchar)) = 6 then left(right(CAST(time as varchar), 2), 1)
end as time2
, pos_team
, case when length(CAST(yardline as varchar)) = 5 then left(right(CAST(yardline as varchar), 4), 3)
when length(CAST(yardline as varchar)) = 4 then left(right(CAST(yardline as varchar), 3), 2)
when length(CAST(yardline as varchar)) = 3 then left(right(CAST(yardline as varchar), 2), 1)
end as yardline_mod
, yardline
, down
, yards_to_go
, description
from play
where gsis_id = '2009081350'
order by play_id asc)fu

[robertromeo5]

This should work even better and should be exactly what you're looking for:

select *
, CASE WHEN fu.quarter = 'Q1' then CAST(fu.time2 as float)/60
WHEN fu.quarter = 'Q2' then ((CAST(fu.time2 as float)/60) + 15)
WHEN fu.quarter = 'Q3' then ((CAST(fu.time2 as float)/60) + 30)
WHEN fu.quarter = 'Q4' then ((CAST(fu.time2 as float)/60) + 45)
END AS time_mod
from(
select pp.team as team ,
player.full_name as name ,
time,
sum (ap.receiving_tar) Targets,
sum(pp.receiving_rec) as Receptions,
case when length(CAST(time as varchar)) = 8 then left(right(CAST(time as varchar), 4), 3)
when length(CAST(time as varchar)) = 7 then left(right(CAST(time as varchar), 3), 2)
when length(CAST(time as varchar)) = 6 then left(right(CAST(time as varchar), 2), 1)
end as time2,
right(left(CAST(time as varchar), 3),2) as quarter
from
player,
play p,
agg_play ap,
play_player pp,
game g
where 1=1
and g.season_year>=2016
and g.season_type='Regular'
and g.gsis_id = ap.gsis_id
and g.gsis_id=p.gsis_id
and p.play_id=ap.play_id
and p.drive_id=ap.drive_id
and p.play_id=pp.play_id
and p.gsis_id=pp.gsis_id
and pp.player_id=Player.player_id
and ap.receiving_tar >0
and player.position in ('WR')
and pp.team='SD'
group by pp.team
,player.full_name,
time
order by full_name desc
)fu
where fu.quarter IN ('Q3')