Only date portion of $timeFilter required

Grafana MSSQL
1

Hi,

I am using MSSQL as a data source. All is fine.

However, I am trying to use: $_timeFilter for my Time Series of data - but I only need the date portion (as I am not getting all my data set back - due to the time element starting at the current time).

So;

$__timeFilter(created)

Gives me as a generated SQL:

created BETWEEN ‘2020-03-24T15:33:18Z’ AND ‘2020-03-31T14:33:18Z’

But I want it to say:

created BETWEEN ‘2020-03-24’ AND ‘2020-03-31’

How do I achieve this?

Many Thanks.

2

I think you could do this using the MSSQL convert function.

3

Thanks - that would be fine…

However, I need to use it with the $_timeFilter so the Time Series in the output works…
If I just use convert - then my Time Series doesnt seem to work…

4

You will need to use the $__timeFrom() and $__timeTo() macros instead of $__timefilter and build your own BETWEEN clause.

$__timeFilter() will generate something like BETWEEN ‘2017-04-21T05:01:17Z’ AND ‘2017-04-21T05:06:17Z’ which is not a datetime that you can pass into the convert function.

5

Thanks - will have a play with that.

6

Hi, I tried both methods and the result is the same.

With timeFilter or timeFrom/timeTo the query inspector returns the same date and time ‘2024-12-17T03:00:00Z’ and ‘2024-12-18T02:59:59Z’ when I select 2024-12-17 00:00 to 2024-12-17 23:59 actually.

Regards.-

7

timefield BETWEEN ‘${__from:date:YYYY.MM.DD}’ and ‘${__to:date:YYYY.MM.DD}’

8

What timezones are you in and is your time column contain itc datetime?

9

Thank you very much for your answer @yosiasz and @joachimschiewek.

I am in UTC-03 as well as the dates and the server, what I find strange is that when everything is configured in that time zone it applies the variations in the dates as if the server was by default in UTC+00.

In my case I solved it by applying the UTC+00 time zone to the date ranges in the time filter and another important fact is that when I was working on a view in mssql the date column was formatted as varchar(16) which also generated a conflict.

The solution to my case:
_ UTC+00 in time filter
_ Datetime format in database column

Thanks to everyone for commenting, have a great end of the year.