Hey guys,
I currently create a Developer Experience Dashboard for our team. In this regards it would be great to view data in Sprints.
In our case Sprints start at every second tuesday, which means they are 2 weeks long.
I currently use these kind of queries on my Postgres:
$__timeGroup(merged_at,$__interval) AS "time",
COUNT(*) AS merged_prs
FROM
pull_requests
WHERE
$__timeFilter(merged_at)
AND merged_at IS NOT NULL
GROUP BY
time
ORDER BY
time```
I also have custom a variable "Min. Interval" ($min_interval) which I use in the Query Options (s. Image). It helps me to aggregate data based on 2 weeks (14d) as $__interval is always the same as $min_interval in my case. The only issue I have is that these 2w intervals are not aligned with my sprint (tuesday to tuesday in two weeks),
Is there a way to achieve that intervals are always based on a given starting point (e.g. first sprint of the year)? The $min_interval solution already feels hacky, maybe you can also point me into the right direction here.
Any help is appreciated :slightly_smiling_face:
Oh a nice puzzle. So I’m thinking of a couple of solutions that could be cool. Maybe use annotations that you can write every 2 weeks. have one graph at the top of the page that you can use to navigate (when you click on an annotation the page time is set to a range around that data point)
the second idea is to use the mod function in your postgres to get the date diff from a specific date. MOD() will give you the extra days you have after a specific tuesday:
– this is a good query to explain how MOD works if you’re not familiar with it
SELECT
(end_date - start_date) / 14 AS fortnight,
MOD((end_date - start_date), 14) AS remaining_days
FROM (
SELECT ‘2024-07-15’::date AS start_date, ‘2024-07-25’::date AS end_date
) AS date_diffs;
you can then use “remaining days” in your query to adjust the where clause to go back this many days. Or you can make a page variable that will use this query based on the selected time frame and return the “Time shift” value you can use in the query options
happy to give you a nice puzzle 
thanks for your advices with interesting ideas and helping me to find something that probably works for me: date_bin .
date_bin('14 days', merged_at, '2025-04-22'::timestamptz) AS time,
COUNT(*) AS merged_prs
FROM
pull_requests
WHERE
$__timeFilter(merged_at)
AND merged_at IS NOT NULL
GROUP BY
time
ORDER BY
time;```
This completely omits grafana way of handling intervals but is very customizable. In the actual dashboard I use variables for the interval and for first sprint start which gives me flexibility when calling date_bin in a lot of places. Currently i really don't see a drawback with this solutions :slightly_smiling_face:
