$__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.