How to create TIME SERIES for every single row in MYSQL table?-CodePudding

I have a table with a list of users:

USER_TABLE
userid     username
1      paul
2      david
3      mary

I need to create a time series (timestamps in 10 minute intervals) for each user, with the following result:

USER_TIMESERIES_TABLE

userid timestamp
1      2022-10-15 00:00:00
1      2022-10-15 00:10:00
1      2022-10-15 00:20:00
1      2022-10-15 00:30:00
1      2022-10-15 00:40:00
...
2      2022-10-15 00:00:00
2      2022-10-15 00:10:00
2      2022-10-15 00:20:00
2      2022-10-15 00:30:00
2      2022-10-15 00:40:00
...
3      2022-10-15 00:00:00
3      2022-10-15 00:10:00
3      2022-10-15 00:20:00
3      2022-10-15 00:30:00
3      2022-10-15 00:40:00
...

Ideally, all these can be done without the need of temporary tables.

So far, I've managed to to create a time series select:

with recursive times as (
      select date_format( "2022-10-11 09:00:00","%Y-%m-%d %H:%i:%s") as time
      
      union all
      
      select date_format(time   interval 1 minute,"%Y-%m-%d %H:%i:%s") as time_interval
              from times
              where time < date("2022-10-15 00:00:00")
     )

Which gives:

TIMESERIES_SELECT

time
2022-10-15 00:00:00
2022-10-15 00:10:00
2022-10-15 00:20:00
2022-10-15 00:30:00
2022-10-15 00:40:00
...

But haven't found a way to map each user row to the full time series.

Appreciate any help!

Thanks

CodePudding user response：

with recursive times as (
   ...your query that generates the time series...
)
select userid, time
from user_table
cross join times;