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

[timgodec]

I am facing the same issue with query from influxdb. Have anyone found a solution?

[nmatthews]

Same. I was able to do this in the past using the Infinity data source on Grafana Cloud. I am now using AWS-hosted grafana and an influxdb data source, and when I try to convert the date field, I dont have the same options (specifying the date format). Selecting convert date as string results in an ISO time stamp (1690239487000) but what I want is just a stringified version of the date.

Using Infinity:

Using Influxdb:

[orosolido]

For Influx users @timgodec @nmatthews the following might be something that can help you on your way to solve your problem.

You will need 2 things to achieve this

1) You need to import the "strings" package 
2) Convert the date/timestamp to a "string" with mapping

To import strings package do like so (above your query) :

To convert the 'timestamp (_time)' to a "string" add/use this line to your influx query

|> map(fn: (r) => ({ get_date: strings.substring(end: 10, start: 0, v: string(v: r._time)) , _value: r._value})) 

Note: To use_ your own preferred column name change 'get_date' or '_value' in query line above

from:

to result:

My advise to use the mapping line on the end/bottom of your query BUT before the `'yield'` command

Example query:

import "strings"

from(bucket: "EXAMPLE")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "example123")
  |> THE REST OF YOUR QUERY HERE FIRST


  |> map(fn: (r) => ({ get_date: strings.substring(end: 10, start: 0, v: string(v: r._time)) , value: r._value})) 
  |> group(columns: ["get_date"])  //This line is optional you can also use the TRANSFORM tab > [Group By]  from Grafana to group
  
  |> yield(name: "count")

Good luck!

[mbergeron1]

I found a way to do it, I had to convert the field twice, from Time to String and then String to time. It seems to work properly that way.