【MariaDB】指定の日付と0〜23までの時間帯が一式揃ったカラムを簡単に作成するSQL【SELECT】

 WITH dates AS (
  SELECT generate_series(
    '2023-01-01'::date,
    '2023-01-31'::date,
    '1 day'
  ) as date
), hours AS (
  SELECT generate_series(
    0,
    23
  ) as hour
)
SELECT 
  dates.date, 
  hours.hour
FROM 
  dates
CROSS JOIN 
  hours
ORDER BY 
  dates.date, 
  hours.hour;

WITH RECURSIVE `date_list` (`date`) AS (
    SELECT '2023-01-01'
  UNION ALL
    SELECT DATE(`date` + INTERVAL 1 DAY)
    FROM `date_list`
    WHERE `date` < '2023-01-31' 
 ), `hour_list` (`hour`) AS (
    SELECT 0 as `hour`
  UNION ALL
    SELECT `hour` + 1
  FROM `hour_list`
    WHERE `hour` < 23
)
SELECT 
  `date_list`.`date`, 
  `hour_list`.`hour`
FROM 
  `date_list`
CROSS JOIN 
  `hour_list`
ORDER BY 
  `date_list`.`date`, 
  `hour_list`.`hour`;

WITH RECURSIVE `date_list` (`date`) AS (
    SELECT '2023-01-01'
  UNION ALL
    SELECT DATE(`date` + INTERVAL 1 DAY)
    FROM `date_list`
    WHERE `date` < '2023-01-31' 
 ), `hour_list` (`hour`) AS (
  SELECT LPAD(0, 2, '0') as `hour`
  UNION ALL
      SELECT LPAD(`hour` + 1, 2, '0')
  FROM `hour_list`
      WHERE `hour` < '23'
)
SELECT 
  `date_list`.`date`, 
  `hour_list`.`hour`
FROM 
  `date_list`
CROSS JOIN 
  `hour_list`
ORDER BY 
  `date_list`.`date`, 
  `hour_list`.`hour`;