I would like to create a calendar SQL table like this one but for some years. (I’m using mysql 5.7.28) Date is DD-MM-YYYY
Is it possible?
Advertisement
Answer
If you are running MySQL 8.0, you can use a recursive query:
with recursive cte as (
select '2019-01-01 00:00:00' dt
union all
select dt + interval 1 hour from cte where dt < '2020-01-01' - interval 1 hour
)
select
row_number() over(order by dt) id,
date(dt) day,
time(dt) start_hour,
time(dt + interval 1 hour) end_hour
from cte
The recursive cte generates a list of datetimes between the given boundaries (here, that’s year 2019), with a 1 hour increment. Then, the outer query produces the expected result, by extracting the day and hour parts.
You can adjust the boudaries and the increment as per your exact requirements.
Side note: I would suggest to retain the full datetime in the calendar table as well; there are many situation where having a proper datetime value is more convenient than separated dates and times.
Demo on DB Fiddle for the first day only:
id | day | start_hour | end_hour -: | :--------- | :-------------- | :-------------- 1 | 2019-01-01 | 00:00:00.000000 | 01:00:00.000000 2 | 2019-01-01 | 01:00:00.000000 | 02:00:00.000000 3 | 2019-01-01 | 02:00:00.000000 | 03:00:00.000000 4 | 2019-01-01 | 03:00:00.000000 | 04:00:00.000000 5 | 2019-01-01 | 04:00:00.000000 | 05:00:00.000000 6 | 2019-01-01 | 05:00:00.000000 | 06:00:00.000000 7 | 2019-01-01 | 06:00:00.000000 | 07:00:00.000000 8 | 2019-01-01 | 07:00:00.000000 | 08:00:00.000000 9 | 2019-01-01 | 08:00:00.000000 | 09:00:00.000000 10 | 2019-01-01 | 09:00:00.000000 | 10:00:00.000000 11 | 2019-01-01 | 10:00:00.000000 | 11:00:00.000000 12 | 2019-01-01 | 11:00:00.000000 | 12:00:00.000000 13 | 2019-01-01 | 12:00:00.000000 | 13:00:00.000000 14 | 2019-01-01 | 13:00:00.000000 | 14:00:00.000000 15 | 2019-01-01 | 14:00:00.000000 | 15:00:00.000000 16 | 2019-01-01 | 15:00:00.000000 | 16:00:00.000000 17 | 2019-01-01 | 16:00:00.000000 | 17:00:00.000000 18 | 2019-01-01 | 17:00:00.000000 | 18:00:00.000000 19 | 2019-01-01 | 18:00:00.000000 | 19:00:00.000000 20 | 2019-01-01 | 19:00:00.000000 | 20:00:00.000000 21 | 2019-01-01 | 20:00:00.000000 | 21:00:00.000000 22 | 2019-01-01 | 21:00:00.000000 | 22:00:00.000000 23 | 2019-01-01 | 22:00:00.000000 | 23:00:00.000000 24 | 2019-01-01 | 23:00:00.000000 | 00:00:00.000000
In earlier versions, you would typically create a large table of numbers by cross-joining subqueries, and use the number range to increment the initial date. row_number() can be emulated with a MySQL variable:
select
@id:=@id + 1 id,
date(dt) day,
time(dt) start_hour,
time(dt + interval 1 hour) end_hour,
0 prenoted
from (
select '2019-01-01' + interval d0.n + 10 * d1.n + 100 * d2.n + 1000 * d3.n hour dt
from
(
select 0 n union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
) d0
cross join (
select 0 n union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
) d1
cross join (
select 0 n union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
) d2
cross join (
select 0 n union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
) d3
where '2019-01-01' + interval d0.n + 10 * d1.n + 100 * d2.n + 1000 * d3.n hour < '2020-01-01'
) t
cross join (select @id := 0 id) i
order by dt
The above query gives you a maximum span of 10 000 hours (wich represent a little more than 416 days); you can add another cross join and update the arithmetic to handle up to 100 000 hours (and so on).
Demo on DB Fiddle for the first 24 hours.