How to convert timestamp and then group by date?

[preethi1]

What I want is (for example) if my data came in like this:

Time                     |      Val
2022-05-12 20:50:00      |      1  
2022-05-12 20:55:00      |      2
2022-05-13 20:50:00      |      4
2022-05-13 20:55:00      |      10

I want it to be transformed to look like this, such that the it is grouped by Time (ignoring the time part of the timestamp), and summed on Val:

Time            |      Val
2022-05-12      |      3
2022-05-13      |      13

I have the following data table:

I added a data override, which successfully gets rid of the HH:MM:SS from the timestamp, and tried a ‘group by’, but it does not group any of the data.

I also tried converting the timestamp via a transform, which did not work:

How can I accomplish the the timestamp reformatting and the grouping?

[yosiasz]

not sure what your data source is so plugged it into infinity csv inline and using this

[preethi1]

This is using a prometheus data source. Glad that my commands weren’t wrong, but even the very first step of Convert field type isn’t working for me

I even tried renaming Time to time, in case that was causing some issue, and it did not work.

[yosiasz]

please provide sample data as json or whatever data type it is? cause I do not have Prometheus installed but can emulate it

[expoenergy]

Change your date format from YYYY-MM-DD to DD and then group by time and it should sum the totals for each day.

Use the bar chart

[preethi1]

I tried reformatting the date and it did not work.
I need a table, not a bar chart.

[mattabrams]

Can you use the data tab inside the Grafana Inspector panel to share your data?

Navigate the Grafana Inspector panel

[muditjain24]

im facing the same issue, any update on this thread pls?

[yosiasz]

Welcome

What is the specific issue you are facing and please provide sample data. What’s your data source?

[muditjain24]

Need to aggregate record count day wise

Data source - Azure DevOps
Column - Start Time

Not grouping data by date

Expected result

Date Count
2023-04-12 20
2023-04-13 45

Sample data :

Id,BuildNumber,KeepForever,Priority,Quality,Reason,Result,RetainedByRelease,StartTime,FinishTime,QueueTime,Status
157673,20230419.1,FALSE,normal,,manual,failed,FALSE,1.68E+12,1.68E+12,1.68E+12,completed
157665,20230419.2,FALSE,normal,,manual,failed,FALSE,1.68E+12,1.68E+12,1.68E+12,completed
157662,20230419.7,FALSE,normal,,batchedCI,failed,FALSE,1.68E+12,1.68E+12,1.68E+12,completed
157653,20230419.1,FALSE,normal,,manual,failed,FALSE,1.68E+12,1.68E+12,1.68E+12,completed
157654,20230419.2,FALSE,normal,,pullRequest,failed,FALSE,1.68E+12,1.68E+12,1.68E+12,completed
157649,20230419.4,FALSE,normal,,pullRequest,failed,FALSE,1.68E+12,1.68E+12,1.68E+12,completed
157648,20230419.3,FALSE,normal,,batchedCI,failed,FALSE,1.68E+12,1.68E+12,1.68E+12,completed
157645,20230419.1,FALSE,normal,,individualCI,failed,FALSE,1.68E+12,1.68E+12,1.68E+12,completed
157639,20230419.2,FALSE,normal,,manual,failed,FALSE,1.68E+12,1.68E+12,1.68E+12,completed
157636,20230419.1,FALSE,normal,,individualCI,failed,FALSE,1.68E+12,1.68E+12,1.68E+12,completed

[yosiasz]

it could be that that specific data source might not implement the group by feature though available in grafana.

can azure dev ops data be gotten other than using azure dev ops plugin such as Infinity plugin? Using csv sample data you provided (unless it is returning a json data)

[muditjain24]

I dont see this option,using grafana v9.0.3

[yosiasz]

@yosiasz

can azure dev ops data be gotten other than using azure dev ops plugin such as Infinity plugin?

because of above

[zage-coder]

Found any solution for this scenario @muditjain24