MySQL: Select last timestamp stored at some hour, for each day, in a range of 7 days

Question

I have a table in MySQL declared like this:

+------------+------------+------+-----+-------------------+-------+
| Field      | Type       | Null | Key | Default           | Extra |
+------------+------------+------+-----+-------------------+-------+
| ts         | timestamp  | NO   |     | CURRENT_TIMESTAMP |       |
| id_usuario | int(11)    | YES  |     | NULL              |       |
| temp       | float(3,1) | YES  |     | NULL              |       |
+------------+------------+------+-----+-------------------+-------+

And lets say this is the data stored in it:

+---------------------+------------+------+
| ts                  | id_usuario | temp |
+---------------------+------------+------+
| 2015-02-12 11:00:00 |          2 | 20.0 |
| 2015-02-15 10:00:00 |          2 | 21.0 |
| 2015-02-17 20:00:00 |          2 | 20.0 |
| 2015-02-18 11:00:00 |          1 | 23.5 |
| 2015-02-18 11:30:00 |          1 | 24.0 |
| 2015-02-19 12:00:00 |          1 | 23.5 |
| 2015-02-19 12:30:00 |          1 | 22.0 |
| 2015-02-19 15:00:00 |          1 | 23.5 |
| 2015-02-19 15:50:00 |          1 | 21.5 |
| 2015-02-20 14:00:00 |          2 | 27.0 |
| 2015-02-20 14:00:00 |          1 | 26.0 |
| 2015-02-20 21:54:33 |          2 | 22.0 |
+---------------------+------------+------+

And now... imagine you travel in time a day before, and you want to get the last input in the table. Then you travel a day before to do the same thing, and so on for the last 7 days... that's it! And then came up with a table like this one: (Note: i didn't travel in time!)

if "select now()" returns '2015-02-20 13:00:00', this procedure would return:

+---------------------+------------+------+
| ts                  | id_usuario | temp | <-- day 12 is out of range
+---------------------+------------+------+  but was the last input before
| 2015-02-12 11:00:00 |          2 | 20.0 |  day 14 at 13hs. So its ok!
| 2015-02-15 10:00:00 |          2 | 21.0 | <-- day 17,16,15 before 13hs
| 2015-02-18 11:30:00 |          1 | 24.0 | <-- day 18 before 13hs
| 2015-02-19 12:30:00 |          1 | 22.0 | <-- day 19 before 13hs
| 2015-02-19 15:50:00 |          1 | 21.5 | <-- day 20 before 13hs
+---------------------+------------+------+

So the question is... how can I do it?

No, I understood it correctly. He wants to `GROUP BY FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(ts))/3600)` and then get the last row in each group. — Barmar, Feb 20 '15 at 23:32
See http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group for how to get the last row in each group. — Barmar, Feb 20 '15 at 23:32
this works... but i think its a bit... err... a workout? (select * from Temp_Usuario where ts < NOW() order by ts desc limit 1) union (select * from Temp_Usuario where ts < DATE_SUB(NOW(), INTERVAL 1 DAY) order by ts desc limit 1) union (select * from Temp_Usuario where ts < DATE_SUB(NOW(), INTERVAL 2 DAY) order by ts desc limit 1) union (select * from Temp_Usuario where ts < DATE_SUB(NOW(), INTERVAL 3 DAY) order by ts desc limit 1) union and so on, until INTERVAL 6 — mrc, Feb 20 '15 at 23:51
Are you sure that your result set is correct? `| 2015-02-19 15:50:00 | 1 | 21.5 | <-- day 20 before 13hs` <-- this is after 13 — Strawberry, Feb 21 '15 at 12:32
yes, becouse that's the last input before 13:00hs day 20. It was setted the day before (the last register). — mrc, Feb 21 '15 at 13:46

score 0 · Answer 1 · answered Feb 23 '15 at 00:19

This is what i wanted to do (and better, because i also get the info for each id_usuario) but I'm not sure about code efficiency. Note: im really new in SQL world... this is the result of a constant research on internet for about a week! So you may notice some hardcoding...

CREATE FUNCTION somefun ()
RETURNS <SOMETHING>
BEGIN
  DECLARE eot INT DEFAULT FALSE;
  DECLARE ts_start TIMESTAMP DEFAULT NOW();
  DECLARE ts_comp TIMESTAMP DEFAULT NOW();
  DECLARE ts TIMESTAMP;
  DECLARE usr_comp MEDIUMINT DEFAULT 0;
  DECLARE usr MEDIUMINT;

  DECLARE cur CURSOR FOR SELECT * FROM Temp_Usuario ORDER BY id_usuario, ts DESC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET eot = TRUE;
  OPEN cur;

  leer: REPEAT
    FETCH cur INTO ts, usr, temp;
    IF usr <> usr_comp THEN
      SET usr_comp = usr;
      SET ts_comp = ts_start;
    END IF;
    IF (ts < ts_comp) AND (ts_comp > DATE_SUB(ts_start, INTERVAL 6 DAY)) THEN      

      /* DO SOMETHING */

    END IF; 
  UNTIL eot END REPEAT leer;

  CLOSE cur;
  RETURN ( <SOMETHING> );
END;
//

Let me know if you see some way to make it better or prevent possible errors...Thanks to everyone!!

MySQL: Select last timestamp stored at some hour, for each day, in a range of 7 days

1 Answers1