I would be very grateful if someone can help me here: in my database, I have prices recorded at a particular time in a day over years. So for each vendor or station, there can be several price data points within a day. The data is stored in a timestamp format so I can see which specific time within a specific day that the price was recorded.
My problem: I want to be able to select the price data for a period e.g. between 2014-01-01 and 2014-03-31 and at the same time select all the data within a specific time period e.g., between 08:00:00 and 20:00:00 for each day. I attempted it with this:
SELECT station.id, DATE(item_price.date_time) as mydate, TIME(item_price.date_time) as mytime, item_price.price FROM station INNER JOIN item_price ON station.id=item_price.station WHERE date_time >='2014-01-01' AND date_time < '2014-03-31' AND date_time >= '08:00:00' AND date_time < '20:00:00' ORDER BY item_price.date_time;
But, I'm unable to obtain the price data between '08:00:00' and '20:00:00' for each day in the specified period. Can someone please help me out?