Timeslice/ reporting 'empty' bins as zero
first: my SQL and vertica knowledge is quite limited.
I have a table that contains data samples with a timestamp, and I am counting the number of data items e.g. on a 5min interval using the time_slice function like this:
select time_slice(to_timestamp(timestamp_utc_s),$interval), 'my_table=' || count(*) from dataset.my_table group by 1 order by 1 desc;
2022-04-11 11:10:00 | my_table=1000
2022-04-11 11:05:00 | my_table=1000
2022-04-11 11:00:00 | my_table=196
2022-04-11 09:50:00 | my_table=588
2022-04-11 09:45:00 | my_table=1000
2022-04-11 09:40:00 | my_table=1000
2022-04-11 09:35:00 | my_table=1000
This is working nicely, however when I have intervals without samples at all, those bins are not reported with a counter of 0, but they are omitted.
Like in the example above between 09:50 and 11:00 .
I am looking for some magic that would make these bins appear with a count of zero :-)