I am collecting environmental data from my hen house and storing it locally in a table. Updates occur every minute. In addition to temperature, etc, one of the data streams I am monitoring is the state of the chicken door on the hen house, i.e. whether it is open or closed. In the table, I am using a boolean (tinyint) for the data open = 1 and closed = 0. There is also a timestamp column that defaults to CURRENT_TIMESTAMP when a new row is inserted.

Of course, this boundary will occur twice a day, open in the morning, close in the evening. The data will display on my website. I am not new to writing sql, but I've never had to deal with this type of a query before. Any suggestions on a query that would find these two points for any given day?

I am sure that I probably need a BETWEEN on there on the added column,

select id, door_is_open as state, added from coop_data where added between ('2012-01-19 00:00:00' and '2012-01-19 23:59:59') and