I have a table that contains a timestamp and a number for each row. This data is logged every 15mins on the quarter hour, 24 hours a day, but it does sometimes get missed or is delayed. I'd like to be able to extract the data for the end of each of the last 7 days including the most recent log today. I have two different queries that almost achieve this but not quite:

FROM WaterDepth 
WHERE (date >= DATE(NOW()) - INTERVAL 7 DAY) AND (HOUR(date)+MINUTE(date) = 68) 

This captures the last 7 days at 23:45, but misses any days that don't have an entry at precisely 23:45 and misses today.

SELECT a1.* 
FROM WaterDepth a1 
INNER JOIN (SELECT max(date) as max 
            FROM WaterDepth 
            GROUP BY date(date)) a2 ON a1.date = a2.max

This captures the last entry for each day regardless of what time that is and includes today, but I'm not sure how to limit it to the last seven days. So I guess I'm looking a blend of the two.

Thanks in advance

  • Specify your server version.
    – Akina
    Sep 18, 2019 at 11:48
  • mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper
    – pssturges
    Sep 19, 2019 at 0:09

1 Answer 1

SELECT a1.* 
FROM WaterDepth a1 
INNER JOIN ( SELECT MAX(`date`) as `max` 
             FROM WaterDepth
             WHERE `date` >= CURRENT_DATE - INTERVAL 7 DAY 
                 -- or 6 day? 7 day gives 8 different dates...
                 -- or today record not needed? if so add
                 -- AND `date` < CURRENT_DATE
             GROUP BY DATE(`date`) ) a2 ON a1.`date` = a2.`max`

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.