- const query = `WITH days AS ( ` +
- `SELECT (current_date::timestamp - (serie || ' days')::interval)::timestamptz AS day
- FROM generate_series(0, ${STATS_TIMESERIE.MAX_DAYS - 1}) serie` +
- `) ` +
- `SELECT days.day AS date, COALESCE(${selectMetrics[metric]}, 0) AS value ` +
- `FROM days ` +
- `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
- `AND date_trunc('day', "localVideoViewer"."startDate") = date_trunc('day', days.day) ` +
- `GROUP BY day ` +
- `ORDER BY day `
+ const query = `WITH "intervals" AS (
+ SELECT
+ "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
+ FROM
+ generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time")
+ )
+ SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
+ FROM
+ intervals
+ LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
+ AND "localVideoViewer"."startDate" >= "intervals"."startDate" AND "localVideoViewer"."startDate" <= "intervals"."endDate"
+ GROUP BY
+ "intervals"."startDate"
+ ORDER BY
+ "intervals"."startDate"`