0

For example, given 1339372800, which is Mon, 11 Jun 2012 00:00:00 GMT, I would like to get the next X hours including 1339372800. So for the next 3 hours, the result would be

+------------+
| hours      |
+------------+
| 1339372800 |
| 1339376400 |
| 1339380000 |
+------------+

I think I need to create a loop but I don't know how to proceed from that. Thanks.

luis
  • 23
  • 6
  • 3
    Why do this in MySQL? It's often much easier in one's application code... – eggyal Jun 11 '12 at 09:54
  • Because I need to left join it with a view that gets the count of rows per hour. If there are no entries for that particular hour I would like the entry to be 0. – luis Jun 11 '12 at 10:04
  • But why not just draw that inference within your application when an hour is not present in the resultset? – eggyal Jun 11 '12 at 10:05
  • I guess that's possible, but I'd like to know a MySQL solution as well. – luis Jun 11 '12 at 10:18
  • Well, as I said, it's often much easier in one's application code... the MySQL solution is essentially that which @hangy proposed in [his answer](http://stackoverflow.com/a/10977935/623041). – eggyal Jun 11 '12 at 10:23

2 Answers2

0

You could use a numbers table as Creating a "Numbers Table" in mysql that contains all hours for a considerate amount of time, and query that:

SELECT hour
FROM hours
WHERE hour >= 1339372800
ORDER BY hour ASC
LIMIT 3

That makes the query really easy, and the table should not be too big, either. (Less than 90k rows for 10 years.)

Community
  • 1
  • 1
hangy
  • 10,765
  • 6
  • 43
  • 63
  • Hmm, that could work. But I guess I'm looking for a more elegant solution (no offense). If I can't find any other that works I'll go with yours. – luis Jun 11 '12 at 10:08
0

Try:

SELECT * 
FROM table
WHERE 
hours between 1339372800 and DATE_SUB(1339372800, INTERVAL 3 HOUR)
J A
  • 1,776
  • 1
  • 12
  • 13