Average between 2 fields D+HH:MM:SS

[Abarny]

Hi,

I try to realize an average enter 2 fields which appear in the form of D+HH:MM:SS so i converted with dur2sec. But the result is 0 i don't understand why. Can you help me to find why ? Thanks you.

| convert dur2sec(AAAA)
| convert dur2sec(BBB)
|stats sum(AAA) as C sum(BBB) as D dc(E) as F
| eval temps=D-C | eval moyen= temps/F
| fields moyen

[woodcock · Accepted solution]

Try this:

| eval duration=strptime(BBB,"%Y-%m-%d %H:%M:%S") - strptime(AAA,"%Y-%m-%d %H:%M:%S")
|stats savg(duration) AS moyen

[richgalloway]

What is field E? Counting a field that doesn't exist will make F=0

[Abarny]

This field exist, E is an unique identifier on 1 event

[niketn]

Have you validated BBB and AAAA values are different or not?
Can you add few data samples?

[Abarny]

Yes they are différents exemple : AAA = 2017-02-18 11:53:05
BBB = 2017-02-18 11:53:14

But no i can't add data sample ..

[somesoni2]

You said that your field values are in format D+HH:MM:SS (string formatted duration) but the sample values above shows that they are timestmap, which one is it? If it's timestamp then your convert dur2sec will fail and return 0/null. If they are timestamp, then give this a try

...your base search
| eval duration=strptime(BBB,"%Y-%m-%d %H:%M:%S")-strptime(AAA,"%Y-%m-%d %H:%M:%S")
|stats sum(duration) as duration dc(E) as F
| eval moyen= duration/F
| fields moyen

[Abarny]

Okay ! Thanks for your help !