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,
    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?

  • What is the result of the query that you posted? What is wrong with the results you are getting?
    – Dave
    Apr 30, 2018 at 17:52
  • When do you enter new rows, when the price changes or say every hour? Apr 30, 2018 at 18:00
  • 3
    Have you tried adding a filter like so: and time(item_price.date_time) between 08:00 and 20:00? Apr 30, 2018 at 18:45
  • Dave: the problem was that I couldn't select the price data for the datetimes specified (2014-01-01 and 2014-03-31 as well as between 08:00:00 and 20:00:00 for each day within the specified dates.
    – aoe207
    May 1, 2018 at 10:03
  • 1
    @BertrandLeroy: this worked for me as shown by ypercube. Now I can select the price data for the datetimes specified (i.e. 2014-01-01 and 2014-03-31 and for each day within the period, I only wanted data between 08:00:00 and 20:00:00).
    – aoe207
    May 1, 2018 at 10:10

1 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):

          -- 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:

    DATE(ip.date_time) AS mydate, 
    TIME(ip.date_time) AS mytime, 
    station AS s
    INNER JOIN item_price AS ip 
    ON st.id = ip.station
          -- 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'
    ip.date_time ;
  • Thank you very much. This was exactly what I needed. It selects all price data between 08:00:00 and 20:00:00 for each day. Thanks once again.
    – aoe207
    May 1, 2018 at 9:57
  • Thanks @ypercubeᵀᴹ - ah yes, I see now! Rewriting my own humble answer correctly just ends up being a copy of yours! Answer deleted!
    – Vérace
    May 1, 2018 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.