SQL query statement issue

[jdkhong]

Hi I was wondering how to query the correct format for drive.start_field and drive.start_time?

For example:
WHERE drive.start_field = "X"
AND drive.start.time = "Y"

What would the X and Y be? Thanks!

[BurntSushi]

The start_field column has type field_pos. You can see what the type looks like by running \d field_pos in psql:

nfldb=# \d field_pos
 Composite type "public.field_pos"
 Column |     Type     | Modifiers 
--------+--------------+-----------
 pos    | field_offset |

To access the sub-fields, you need to put parentheses around the column name. For example, to get the results of all drives that started at or after the opponent's two yard line:

select result from drive where (start_field).pos > 48;
...

You can see more details on the representation in the documentation of FieldPosition.

Similarly for start_time, which has type game_time:

nfldb=# \d game_time
Composite type "public.game_time"
 Column  |    Type    | Modifiers 
---------+------------+-----------
 phase   | game_phase | 
 elapsed | game_clock |

This has two sub-fields, and they are documented somewhat on the Clock type (you may want to click "show source" to get a full understanding).

For example, to get all plays that occurred in OT2:

nfldb=# select description from play where time >= ('OT2'::game_phase, 0::game_clock) and time <= ('OT2'::game_phase, 900::game_clock);
                                description                                
---------------------------------------------------------------------------
 (15:00) R.Rice up the middle to DEN 32 for 2 yards (D.Williams).
 (14:34) R.Rice up the middle to DEN 31 for 1 yard (J.Bannan).
 (14:07) R.Rice left guard to DEN 29 for 2 yards (J.Bannan).
 (13:24) J.Tucker 47 yard field goal is GOOD, Center-M.Cox, Holder-S.Koch.