Date and time period query

Question

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?

Answer 1

Your WHERE clause has two parts that deal with date and time. The date part is OK but the time part isn't doing what you want. You need to extract only the time from the timestamp values, with TIME(date_time):

WHERE 
          -- date part
      date_time >= '2014-01-01' 
  AND date_time  < '2014-04-01'    -- minor correction
          -- time part
  AND TIME(date_time) >= '08:00:00' 
  AND TIME(date_time)  < '20:00:00'

The whole query:

SELECT 
    st.id, 
    DATE(ip.date_time) AS mydate, 
    TIME(ip.date_time) AS mytime, 
    ip.price
FROM 
    station AS s
    INNER JOIN item_price AS ip 
    ON st.id = ip.station
WHERE 
          -- date part
      ip.date_time >= '2014-01-01' 
  AND ip.date_time  < '2014-04-01'    -- minor correction
          -- time part
  AND TIME(ip.date_time) >= '08:00:00' 
  AND TIME(ip.date_time)  < '20:00:00'
ORDER BY 
    ip.date_time ;