search

cpsievert / pitchRx

Tools for scraping MLB Gameday data and Visualizing PITCHf/x
http://cpsievert.github.io/pitchRx/
Other
124 stars 33 forks source link

date field not actually generated / calculated in MySQL (5.6.24 OS X) #31

Closed bdjohnson79 closed 9 years ago

bdjohnson79 commented 9 years ago

I'm just getting started and finished downloading the last few years worth of data. I came across an issue with generating the date field in the atbat table. Basically, the SUBSTR command as defined in scrape.R isn't returning data.

SELECT *, SUBSTR(gameday_link, 15, -10) as date from atbat

returns an empty datestamp column.

SELECT *, SUBSTR(gameday_link, 5, 10) as date from atbat

returns a date as intended. It looks like according to the mysql documentation for version 5 that the length parameter should not be negative - the docs say that a negative value will return an empty string. https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

I was able to fix it manually at the DB level, so it's not a showstopper bug.

Versions of addins: dplyr 0.4.2 pitchRx 1.8 (loaded from github) DBI 0.3.1

cpsievert commented 9 years ago

Ahh, thanks for the note. I have a feeling that first SUBSTR command is correct for SQLite (what I tend to use), but I'll have to add special handling for MySQL..

cpsievert commented 9 years ago

Fixed via 837059c