Generate range hours and range numbres SQL/HQL

I have a problem with a table.I currently have this empty hours table and I need to fill it automatically with a query in Hiveql. The idea is to generate:

• In the first column “key” values between 000000 and 235959
• In the second column “hours” values between 00:00:00 and 23:59:59.

Now my table is empty:

KEY              |    HOURS     |

Future table that I need will be:

KEY     |    HOURS     |

1        00:00:00 

2         00:00:01 

...            ...

235959        23:59:59

How can I do this?

Answer

set hivevar:end=23*60*60+59*60+59;--sequence end in seconds

with seq as(
select posexplode(split(space((${end})),' ')) as (i,x)
)

select concat(HH,mm,ss) as key, concat_ws(':',HH,mm,ss) as hours
from
(
select i, --seconds, you can use it also as a key
       lpad(floor(i/3600),2,'0') HH, lpad(floor(i%3600/60),2,'0') mm, lpad(floor(i%3600%60),2,'0') ss
  from seq
)s;

Result:

... 
first rows skipped
...
235954  23:59:54
235955  23:59:55
235956  23:59:56
235957  23:59:57
235958  23:59:58
235959  23:59:59
Time taken: 6.737 seconds, Fetched: 86400 row(s)