kegbot_db - sql queries.sql

# Purpose:
# SELECT: All DRINKS of a specific Keg
# OUTPUT: Drink ID, Day of Week, Date and time (localtime), 
# SORTED BY: Volume_ml

select id, substr('SunMonTueWedThuFriSat', 1 + 3*strftime('%w', datetime(time, "localtime")), 3), datetime(time, "localtime"), volume_ml from core_drink where keg_id=61 ORDER BY volume_ml;

# Purpose:
# SELECT: All DRINKS of a specific Keg ONLY if weekday (Mon-Fri)
# OUTPUT: Drink ID, Day of Week, Date and time (localtime)

select id, substr('SunMonTueWedThuFriSat', 1 + 3*strftime('%w', datetime(time, "localtime")), 3) as dow, datetime(time, "localtime"), volume_ml from core_drink where keg_id=61 AND (dow = "Mon" or dow = "Tue" or dow = "Wed" or dow = "Thu" or dow = "Fri");