1
select to_char(
         (to_date(20160218, 'yyyymmdd') + (level - 1) / 24),
         'yyyymmddhh24'
       ) dt
from dual
connect by level <= 24

This SQL can run in Oracle but MySQL does not support these hierarchical functions (connect by level).

How can it be translated to MySQL?

MT0
  • 143,790
  • 11
  • 59
  • 117
julyhao
  • 11
  • 2
  • This is generating the fixed values '2016021800' to '2016021823'. Is this really what you need? No variable date, like sysdate or a date column to be used? – Thorsten Kettner Feb 18 '16 at 12:43
  • Possible duplicate of [How to do the Recursive SELECT query in MySQL?](http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql) – MT0 Feb 18 '16 at 12:48
  • Or a better duplicate: [How do I make a row generator in MySQL?](http://stackoverflow.com/questions/701444/how-do-i-make-a-row-generator-in-mysql) – MT0 Feb 18 '16 at 13:04

1 Answers1

0

You don't necessarily need a recursive query in order to generate 24 values. You can use UNION ALL instead:

select '2016021800' as dt
union all
select '2016021801' as dt
union all
...
union all
select '2016021823' as dt;

The same is also possible with a variable date, such as the current date:

select date_format(curdate(), '%Y%m%d') || '01'
union all
select date_format(curdate(), '%Y%m%d') || '02'
union all
...
union all
select date_format(curdate(), '%Y%m%d') || '23';
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73