I'm trying to build a user-facing analytics dashboard and need to generate a table with a row every 10 minutes for the last 90 days. I've looked into using TUMBLE and HOP in RisingWave, but they seem to require existing data. I also considered recursive CTEs, but they aren't supported and don't fit my needs. Ideally, these 10-minute intervals would align to a fixed grid. I've attempted a workaround using a CROSS JOIN with unions to create a large number of minutes and then selecting the appropriate intervals, but it's not an elegant solution. Is there a better way to achieve this in RisingWave?
Kai
Asked on Jan 17, 2024
I think you can create a physical table and do batch insert to the table to generate the time list table. Here's an example of how you could do it:
CREATE TABLE per_minutes(minute timestamp) APPEND ONLY;
INSERT INTO per_minutes SELECT * FROM generate_series(
'2020-01-01 00:00:00'::TIMESTAMP,
'2050-01-01 00:00:00'::TIMESTAMP,
interval '1' minute
);This method uses the generate_series function to create a series of timestamps at 1-minute intervals, which you can then use to generate your desired 10-minute intervals. It's a more straightforward approach than using a CROSS JOIN with unions.