I have daily data, if it misses some days into my database, is there anyway to return the missings rows with all the dat aat 0. It would be very usefull for charts.
@fstn! Welcome to the Timescale forum.
I’m unclear if you’re asking about how to fill gaps in a query that uses something like time_bucket('1 day', ts) to aggregate data, or that you have raw data points that are daily but sometimes you don’t have a daily value.
To produce an aggregate (time_bucket) query that also returns rows for NULL values, you need to use time_bucket_gapfill().
In either instance, your easiest path is to use a CASE statement when the return value would be NULL but you want to return a different value in that case.
Example using raw, daily data
(assuming ts is the daily timestamp column, value is a daily value)
SELECT ts, CASE WHEN value IS NULL THEN 0 ELSE value END AS value
FROM table
WHERE ts > now() - '10 days'::interval;
Example using time_bucket_gapfill to aggregate and return rows for NULL data
(assuming ts is the raw timestamp column, value is a raw value)
SELECT time_bucket_gapfill('1 day', 'ts') AS time
, CASE WHEN sum(value) IS NULL THEN 0 ELSE sum(value) END AS daily_sum
FROM table
WHERE ts > now() - '10 days'::interval;
HTH