CODEWITHSUNDEEP
mysql
Virik
Virik

Reputation: 407

MySQL One row for each hour of the day

I'm trying to get one row for each hour of the day, which I intent to left join certain data to.

So far I have only found this solution, but I'm pretty sure it's easier ways to do this..?

SElECT addtime('2016-01-22 00:00:00', t0.i) myHour
FROM (
    SELECT '00:00:00' i
    UNION SELECT '01:00:00'
    UNION SELECT '02:00:00'
    UNION SELECT '03:00:00'
    UNION SELECT '04:00:00'
    UNION SELECT '05:00:00'
    UNION SELECT '06:00:00'
    UNION SELECT '07:00:00'
    UNION SELECT '08:00:00'
    UNION SELECT '09:00:00'
    UNION SELECT '10:00:00'
    UNION SELECT '11:00:00'
    UNION SELECT '12:00:00'
    UNION SELECT '13:00:00'
    UNION SELECT '14:00:00'
    UNION SELECT '15:00:00'
    UNION SELECT '16:00:00'
    UNION SELECT '17:00:00'
    UNION SELECT '18:00:00'
    UNION SELECT '19:00:00'
    UNION SELECT '20:00:00'
    UNION SELECT '21:00:00'
    UNION SELECT '22:00:00'
    UNION SELECT '23:00:00'
) t0

Upvotes: 3

Views: 950

Answers (2)

phobia82
phobia82

Reputation: 1257

This is a trick I learned from SO some time ago, the only con is that you should refer a table with more than 24 rows, that should be easy because you want to use it in a join anyway

select
   addtime('2016-01-22 00:00:00', concat( @ num: =@ num + 1, ':00:00')) date 
from
   table,
   (
      select
         @ num: =- 1
   )
   num limit 24

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

this is only a little bit smaler:

SELECT '2016-01-22 00:00:00' + INTERVAL (d0*10+d1) hour  AS mydate
FROM (
SELECT 0 AS d0 UNION SELECT 1 UNION SELECT 2
) AS t1
cross JOIN (
SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS t2
WHERE (d0*10+d1) < 24
order by d0,d1;

but!!, if you can use MariaDB you can use the sequence Engine and do it like this:

 SELECT '2016-01-22 00:00:00' + INTERVAL seq HOUR as mydate from seq_0_to_23;

mydate
2016-01-22 00:00:00
2016-01-22 01:00:00
2016-01-22 02:00:00
2016-01-22 03:00:00
2016-01-22 04:00:00
2016-01-22 05:00:00
2016-01-22 06:00:00
2016-01-22 07:00:00
2016-01-22 08:00:00
2016-01-22 09:00:00
2016-01-22 10:00:00
2016-01-22 11:00:00
2016-01-22 12:00:00
2016-01-22 13:00:00
2016-01-22 14:00:00
2016-01-22 15:00:00
2016-01-22 16:00:00
2016-01-22 17:00:00
2016-01-22 18:00:00
2016-01-22 19:00:00
2016-01-22 20:00:00
2016-01-22 21:00:00
2016-01-22 22:00:00
2016-01-22 23:00:00

Upvotes: 3

Related Questions