Issue validation

Grafana
,
1

I’ve been struggling with an issue for quite some time now.

Our datasource is connected to Grafana. To validate it against our current system, I extracted a sample with two columns: ‘itemcode’ and ‘pickup_date’ without any specific conditions. Upon opening this data sample in Excel, I found that the count matches perfectly with our system, which was great news.

However, when I proceeded to create a time series visualization and counted it within Grafana, I noticed a discrepancy in the date count compared to what Excel showed me. Despite spending the entire week attempting to validate this issue, I haven’t been able to find a resolution.

2

What version of grafana do you have and what datasource and can you please share your query and what filter are you using

3

Grafana v9.5.13 , DMZ server ,

this is the query "
SELECT
COUNT(*) AS shipment_count,
CAST([PICKup_Date] AS DATE) AS pickup_date
FROM
[DBMasterUni].[dbo].[C3PROJECT]
GROUP BY
CAST([PICKup_Date] AS DATE); "

and this is the result " i highlight date 04/11/2023 to just validate "

Screenshot (166)
Screenshot (166)971×296 21 KB

and if i just extract as this as cvs file "
SELECT
ItemCode , PICKup_Date
FROM
[DBMasterUni].[dbo].[C3PROJECT]

"

for date ’ 04/11/2023 ’

Screenshot (167)
Screenshot (167)1583×597 30.9 KB

4

I’ve tried all the methods that I thought would have an effect. I attempted counting with just the PICKup_Date without casting it, with the cast, and with CONVERT , but none of these matched the count in Excel

5

what filter are you using in excel? show us the filter choice please

6

i just use the filter to see the count number on date 04/11/2023

7

it seems the issue with the date it’s self , but i couldn’t fix it

1 Like
8

Your query removes the time part

CAST([PICKup_Date] AS DATE)

Your excel does not. You are comparing apples and oranges. Ttry to remove the time part in excel

9

the same issue :frowning:

10

What time range are you using in grafana time picker?

And are you using ms sql or mysel?

What do you see when you click on query inspector

11

What time range are you using in grafana time picker?
the time range that i’m using is the filter ’ today so far, or the last 24h ’ i try to apply different range and i try to change the time zone nothing happen also
Browser Time is Antarctica , and the time range

And are you using ms sql or mysel?
ms sql

What do you mean in your last question ?

1 Like
12

For testing please add a where clause with

Where pickup_date between 
'04/11/2023' and  '04/12/2023'
13

the same thing , nothing change

1 Like
14

it’s so weird and i couldn’t understand the script is right but it give different format