1

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?

  • What is the result of the query that you posted? What is wrong with the results you are getting? – Dave Apr 30 '18 at 17:52
  • When do you enter new rows, when the price changes or say every hour? – Lennart Apr 30 '18 at 18:00
  • 3
    Have you tried adding a filter like so: and time(item_price.date_time) between 08:00 and 20:00? – Bertrand Leroy Apr 30 '18 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 '18 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 '18 at 10:10
3

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 ;
  • 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 '18 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 '18 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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