Google bigquery show all day count even if zero

I am using the below query to get the day wise count

SELECT date(timestampInterval), count(*) FROM `dataset.table`
WHERE DATE(timestampInterval) >= "2023-03-06"
group by 1
order by 1 desc

However if there is a date for which the count is zero, the results simply will not have it.
Is there a way to modify this query to include that as well.

You need use first to generate the dates. For example:

WITH date_range AS (
  SELECT DATE_ADD("2023-03-06", INTERVAL day DAY) AS date
  FROM UNNEST(GENERATE_ARRAY(0, DATE_DIFF(CURRENT_DATE(), DATE("2023-03-06"), DAY))) AS day
)
SELECT date_range.date
FROM date_range

will gives you all days from the start date to today. Then join this CTE to your original table using LEFT JOIN and count the records the records again.

As @gotqn mentioned, you need to generate dates filling up the whole period first. And then you can union them with the existing table to caluate a count for each day during the period without missing day.

-- sample data
WITH `dataset.table` AS (
  SELECT '2023-03-06' timestampInterval UNION ALL
  SELECT '2023-03-11' timestampInterval UNION ALL
  SELECT '2023-03-13' timestampInterval
)
-- query starts here
SELECT date, COUNT(cnt) cnt FROM (
  SELECT DATE(timestampInterval) date, 1 cnt 
    FROM `dataset.table` WHERE timestampInterval >= '2023-03-06'
   UNION ALL
  SELECT date, null FROM (
    SELECT MIN(timestampInterval) min, MAX(timestampInterval) max 
      FROM `dataset.table` WHERE timestampInterval >= '2023-03-06'
  ),UNNEST(GENERATE_DATE_ARRAY(DATE(min), DATE(max))) date
) GROUP BY 1 ORDER BY 1;

-- query result
+------------+-----+
|    date    | cnt |
+------------+-----+
| 2023-03-06 |   1 |
| 2023-03-07 |   0 |
| 2023-03-08 |   0 |
| 2023-03-09 |   0 |
| 2023-03-10 |   0 |
| 2023-03-11 |   1 |
| 2023-03-12 |   0 |
| 2023-03-13 |   1 |
+------------+-----+