Dear all,
Data is indexed from a CSV file.
I am trying to calculate the amount of seconds between a couple of timestamps.
The dates are in %d %m %Y %H:%M:%S %Z
format and I am trying to do it this way:
...x | eval compliance_time=strptime("first_date", "%d %m %Y %H:%M:%S %Z")- strptime("second_date", "%d %m %Y %H:%M:%S %Z") | eval compliance_time=compliance_time/60 | eval compliance=if(compliance_time<900, "Compliant", "Non-compliant") | timechart avg(compliance_time) |gauge avg(compliance_time) 1 5 10 15
I've checked current answers already, but unfortunately, I might be missing something.
first_date="21 10 2015 11:16:16 UTC"
second_date="21 10 2015 11:18:46 UTC"
but compliance_time is empty.
Let me know if you need more details.
Thank you,
What you need is to enclose field name within single quotes for field names containing spaces. This is required for eval and where commands. Try something like this
...x | eval compliance_time=strptime('first_date', "%d %m %Y %H:%M:%S %Z")- strptime('second_date', "%d %m %Y %H:%M:%S %Z") | eval compliance_time=compliance_time/60 | eval compliance=if(compliance_time<900, "Compliant", "Non-compliant") | timechart avg(compliance_time) |gauge avg(compliance_time) 1 5 10 15
What you need is to enclose field name within single quotes for field names containing spaces. This is required for eval and where commands. Try something like this
...x | eval compliance_time=strptime('first_date', "%d %m %Y %H:%M:%S %Z")- strptime('second_date', "%d %m %Y %H:%M:%S %Z") | eval compliance_time=compliance_time/60 | eval compliance=if(compliance_time<900, "Compliant", "Non-compliant") | timechart avg(compliance_time) |gauge avg(compliance_time) 1 5 10 15
Hello Rich, Somesoni,
Yes, you're both right.
Renaming and single quotes would have worked.
I came across the solution yesterday after initial Rich feedback regarding removing double quotes but wasn't able to post the answer due the "rookie user" limitation to 2 posts per day.
I consider this close, sorry to bother you, thanks again.
please pick one of the answers to accept--i know it's hard when they're both right! 🙂
Remove the quotation marks from the first arguments to your strptime
commands.
eval compliance_time=strptime(first_date, "%d %m %Y %H:%M:%S %Z")- strptime(second_date, "%d %m %Y %H:%M:%S %Z")
Hi Rich,
I need the quotations marks as the name of the field has an space, is Created At, which I have to quote it as "Created At".
Thank you,
I suspect strptime doesn't handle quoted field names well. Instead, it is trying to parse the literal string "first date" and not getting a time in the given format. An alternative is to rename fields before parsing them.
... | rename "first date" as first_date, "second date" as second_date | eval compliance_time=strptime(first_date, "%d %m %Y %H:%M:%S %Z")- strptime(second_date, "%d %m %Y %H:%M:%S %Z") | rename first_date as "first date", second_date as "second date" | ...