$__dateFilter macro excludes same day data

[a-szegel]

What happened: WHERE $__dateFilter(timestamp) AND produces athena query logic WHERE timestamp BETWEEN date '2023-08-08' AND date '2023-09-08' for time interval NOW and NOW -30D. 2023-09-08 is today's date, and corresponds to HH:MM:SS of 00:00:00. This means that queries are only searching data from 2023-09-07 23:59:59.999999 (the day before), and will never include data made on the same day. Therefore, I never see same day data on the dashboards.

What you expected to happen:

The Amazon Athena Service supports arbitrary timestamps in the future (today's date + N days is acceptable). When I run an Athena query and use the $__dateFilter macro, I would expect the query to generate todays date + 1 day. If today is 09/08/2023, I would expect the query to generate: WHERE timestamp BETWEEN date '2023-08-08' AND date '2023-09-09'.

Environment:

• Grafana version: 9.4
• Plugin version: 2.8.0
• OS Grafana is installed on: Amazon Managed Grafana

[a-szegel]

I understand that we round NOW to the day to enable QRR queries. I like this idea, and think that we should round to the next day, and not the previous day.

[a-szegel]

I think that this is the change that we want, but I don't have a test environment setup currently (so it is untested).

└─╼ git diff
diff --git a/pkg/athena/macros.go b/pkg/athena/macros.go
index bb87db12..56d835dc 100644
--- a/pkg/athena/macros.go
+++ b/pkg/athena/macros.go
@@ -145,7 +145,7 @@ func macroDateFilter(query *sqlds.Query, args []string) (string, error) {
        var (
                column = args[0]
                from   = query.TimeRange.From.UTC().Format("2006-01-02")
-               to     = query.TimeRange.To.UTC().Format("2006-01-02")
+               to     = query.TimeRange.To.AddDate(0, 0, 1).UTC().Format("2006-01-02")
        )
 
        return fmt.Sprintf("%s BETWEEN date '%s' AND date '%s'", column, from, to), nil

[sidd328]

Hello,

I was wondering if there were any updates on whether this issue will be worked on?

AMG customers are hoping for a fix.

Thank you

[lankath]

Hi, are there any updates on progress for this issue? Amazon Managed Grafana customers are looking to get this fixed. Thanks!

[anuragarora]

Is this issue being worked on ?

[jessermejia]

@sarahzinger are there concerns with changing the behavior? it looks like this was going to be prioritized then re-backlogged

[sarahzinger]

Hey folks, I'll bring this back to the team to see if we can prioritize a fix for this over the next few weeks.

I suppose some concerns about implementing this as is are that would technically be a breaking change? But perhaps everyone wants this breaking change?

Another alternative could be that we create a new macro with the functionality that has been requested here, and document the existing one to explain this functionality limitation.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had activity in the last year. It will be closed in 30 days if no further activity occurs. Please feel free to leave a comment if you believe the issue is still relevant. Thank you for your contributions!

[github-actions[bot]]

This issue has been automatically closed because it has not had any further activity in the last 30 days. Thank you for your contributions!