0

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:

SELECT * 
FROM WaterDepth 
WHERE (date >= DATE(NOW()) - INTERVAL 7 DAY) AND (HOUR(date)+MINUTE(date) = 68) 
ORDER BY date ASC LIMIT 7

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 at 11:48
  • mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper – pssturges Sep 19 at 0:09
0
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`
  • Perfect. Thanks! – pssturges Sep 18 at 21:30

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.