CODEWITHSUNDEEP
mysql
Prachi g
Prachi g

Reputation: 839

Querying MYSQL for last 7 days of data for a particular hour

I have a field called startTime which is of a type DATETIME. I have some data for every hour(1 row for every hour). I want to fetch last 7 days of a particular hour (for example: 01:00:00). So basically, query should return 7 rows only.

How can I do it in MySQL?

select traffic from incomingTraffic WHERE ...

Sample data:

+---------------------+---------------------+-----------+----------+
| startTime           | endTime             | component | traffic  |
+---------------------+---------------------+-----------+----------+
| 2015-05-01 00:00:00 | 2015-05-01 01:00:00 | rest      | 29090345 |
| 2015-05-01 01:00:00 | 2015-05-01 02:00:00 | rest      | 32224087 |
| 2015-05-01 02:00:00 | 2015-05-01 03:00:00 | rest      | 35165799 |
| 2015-05-01 03:00:00 | 2015-05-01 04:00:00 | rest      | 36903464 |
| 2015-05-01 04:00:00 | 2015-05-01 05:00:00 | rest      | 40394130 |
| 2015-05-01 05:00:00 | 2015-05-01 06:00:00 | rest      | 44874862 |
| 2015-05-01 06:00:00 | 2015-05-01 07:00:00 | rest      | 49988600 |
| 2015-05-01 07:00:00 | 2015-05-01 08:00:00 | rest      | 52240544 |
| 2015-05-01 08:00:00 | 2015-05-01 09:00:00 | rest      | 54517705 |
| 2015-05-01 09:00:00 | 2015-05-01 10:00:00 | rest      | 55277967 |
| 2015-05-01 10:00:00 | 2015-05-01 11:00:00 | rest      | 55285309 |
| 2015-05-01 11:00:00 | 2015-05-01 12:00:00 | rest      | 55572614 |
| 2015-05-01 12:00:00 | 2015-05-01 13:00:00 | rest      | 54507826 |
| 2015-05-01 13:00:00 | 2015-05-01 14:00:00 | rest      | 52430349 |
| 2015-05-01 14:00:00 | 2015-05-01 15:00:00 | rest      | 47084995 |
| 2015-05-01 15:00:00 | 2015-05-01 16:00:00 | rest      | 41671868 |
| 2015-05-01 16:00:00 | 2015-05-01 17:00:00 | rest      | 37492018 |
| 2015-05-01 17:00:00 | 2015-05-01 18:00:00 | rest      | 34391610 |
| 2015-05-01 18:00:00 | 2015-05-01 19:00:00 | rest      | 33814871 |
| 2015-05-01 19:00:00 | 2015-05-01 20:00:00 | rest      | 32466192 |
| 2015-05-01 20:00:00 | 2015-05-01 21:00:00 | rest      | 28703534 |
| 2015-05-01 21:00:00 | 2015-05-01 22:00:00 | rest      | 25564237 |
| 2015-05-01 22:00:00 | 2015-05-01 23:00:00 | rest      | 23209539 |
| 2015-05-01 23:00:00 | 2015-05-02 00:00:00 | rest      | 24853381 |
| 2015-05-02 00:00:00 | 2015-05-02 01:00:00 | rest      |    24090 |
| 2015-05-02 00:00:00 | 2015-05-02 01:00:00 | rest      | 26848485 |
| 2015-05-02 01:00:00 | 2015-05-02 02:00:00 | rest      | 28507177 |
| 2015-05-02 02:00:00 | 2015-05-02 03:00:00 | rest      | 29970856 |
| 2015-05-02 03:00:00 | 2015-05-02 04:00:00 | rest      | 30607213 |
| 2015-05-02 04:00:00 | 2015-05-02 05:00:00 | rest      | 34005454 |
| 2015-05-02 05:00:00 | 2015-05-02 06:00:00 | rest      | 38241748 |
| 2015-05-02 06:00:00 | 2015-05-02 07:00:00 | rest      | 42154837 |
| 2015-05-02 07:00:00 | 2015-05-02 08:00:00 | rest      | 44181502 |
| 2015-05-02 08:00:00 | 2015-05-02 09:00:00 | rest      | 45787319 |
| 2015-05-02 09:00:00 | 2015-05-02 10:00:00 | rest      | 47283634 |
| 2015-05-02 10:00:00 | 2015-05-02 11:00:00 | rest      | 47328065 |
| 2015-05-02 11:00:00 | 2015-05-02 12:00:00 | rest      | 47370131 |
| 2015-05-02 12:00:00 | 2015-05-02 13:00:00 | rest      | 46298305 |
| 2015-05-02 13:00:00 | 2015-05-02 14:00:00 | rest      | 43894081 |
| 2015-05-02 14:00:00 | 2015-05-02 15:00:00 | rest      | 40307217 |
| 2015-05-02 15:00:00 | 2015-05-02 16:00:00 | rest      | 35481808 |
| 2015-05-02 16:00:00 | 2015-05-02 17:00:00 | rest      | 32316694 |
| 2015-05-02 17:00:00 | 2015-05-02 18:00:00 | rest      | 30426755 |
| 2015-05-02 18:00:00 | 2015-05-02 19:00:00 | rest      | 29809260 |
| 2015-05-02 19:00:00 | 2015-05-02 20:00:00 | rest      | 28466543 |
| 2015-05-02 20:00:00 | 2015-05-02 21:00:00 | rest      | 25392149 |
| 2015-05-02 21:00:00 | 2015-05-02 22:00:00 | rest      | 21182582 |
| 2015-05-02 22:00:00 | 2015-05-02 23:00:00 | rest      | 20954845 |

Upvotes: 1

Views: 94

Answers (3)

Carsten Massmann
Carsten Massmann

Reputation: 28236

Apart from the condition that the data should be younger than 7 days add the condition that hour()=1

SELECT traffic FROM incomingTraffic 
WHERE hour(startTime)=1 AND startTime>ADDDATE(NOW(), -7);

Sorry, took me a while to collect the other bits and get them right ;-)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This problem is trickier than it appears, because it depends on how the data is being updated and on what the OP really means by "last 7 days".

If you want the last 7 days relative to the current date, then use:

WHERE hour(startTime) = 7 AND
      startTime >= date_sub(CURDATE(), interval 7 day)

Now, this might get you 7 days or 8 days, depending on how the data is updated and the current time of day. You can add ORDER BY startTime DESC LIMIT 7 to get exactly 7 days.

If you want the most recent 7 days in the table, then try this approach:

SELECT traffic
FROM incomingTraffic
WHERE hour(startTime) = 7
ORDER BY startTime DESC
LIMIT 7;

This will get the most recent 7 days in the data. However, if you have a large table, this could be inefficient. Having a WHERE clause on startTime would make the query more efficient.

Upvotes: 2

simbabque
simbabque

Reputation: 54381

You can get the 7 days with a DATE_SUB operation and match the hour with HOUR(), which gives you the hour part of a date.

SELECT startTime, traffic FROM incomingTraffic 
WHERE
  startTime > DATE_SUB('2015-05-01', INTERVAL 7 DAY) 
  AND HOUR(startTime) = 1;

Note that I don't have NOW() because your example data is in the past. Also note that you should first check for the date range and then do the hour comparisson because that is way cheaper as mysql needs to do a transformation for checking the hour.

Upvotes: 0

Related Questions