CREATE SCHEMA IF NOT EXISTS cow;
USE cow;
CREATE TABLE IF NOT EXISTS data(cowid INT, r_date DATE, r_time TIME, northing FLOAT(20,3), easting FLOAT(20,3));
CREATE TABLE IF NOT EXISTS time(r_date DATE, sunrise TIME, sunset TIME);

# load data
#load data local infile '/Users/chu/Documents/ranch/2007Data.csv' into table data FIELDS TERMINATED BY ',' ignore 1 lines ;
#load data local infile '/Users/chu/Documents/ranch/2007Time.csv' into table time FIELDS TERMINATED BY ',' ignore 1 lines ;

# pre-day data
#select data.cowid, data.r_date, data.r_time, data.northing, data.easting  from time, data where time.r_date=data.r_date and data.r_time<time.sunrise INTO OUTFILE '/Users/chu/Dropbox/pre.csv';
# day data
#select data.cowid, data.r_date, data.r_time, data.northing, data.easting  from time, data where time.r_date=data.r_date and data.r_time>=time.sunrise and data.r_time<time.sunset INTO OUTFILE '/Users/chu/Dropbox/day.csv';
# post-day data
# select data.cowid, data.r_date, data.r_time, data.northing, data.easting  from time, data where time.r_date=data.r_date and data.r_time>=time.sunset INTO OUTFILE '/Users/chu/Dropbox/post.csv';