How to subtract dates in the format (%d %m %Y %H:%M:%S %Z) to find the difference?

[jcorominas]

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,

[somesoni2 · Accepted solution]

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

[jcorominas]

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.

[piebob]

please pick one of the answers to accept--i know it's hard when they're both right! 🙂

[richgalloway]

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")

[jcorominas]

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,

[richgalloway]

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" | ...