about date and time form

[crazyeva]

There are "date-time" fields other than _time in events:
...^2012/06/30 23:58:20^2012/06/30 23:58:20...
we pre extracted them as "firsttime","lasttime"
we want the results where (fisttime-lasttime)<300s, how could we approach that?
I have tried search as:
| rex field=firsttime "(?\d{2}/\d{2}/\d{2})\s(?\d{2}:\d{2}:\d{2})"
| rex field=lasttime "(?\d{2}/\d{2}/\d{2})\s(?\d{2}:\d{2}:\d{2})"
| convert dur2sec(ftime), dur2sec(ltime)
| where fdate=ldate | eval duration=ltime-ftime |where duration<300 |
but this is not impeccable.

Edit:
Hi
This solution so cool
But it seems not willing to work, I dont know what I did wrong:

sourcetype="..." | fields FirstOccurrence LastOccurrence
| eval firsttime_epoch=strptime(FirstOccurrence,"%Y/%m/%d %H:%M:%S")
| eval lasttime_epoch=strptime(LastOccurrence,"%Y/%m/%d %H:%M:%S")
| table FirstOccurrence firsttime_epoch There are values in FirstOccurrence but firsttime_epoch not
one of samples:
1/10/11 12:08:58

(just now)

[crazyeva]

Thank you again
| eval firsttime_epoch=strptime(FirstOccurrence,"%Y-%m-%d %H:%M:%S")
| eval lasttime_epoch=strptime(LastOccurrence,"%Y-%m-%d %H:%M:%S")
| eval firsttime_epoch2=strptime(FirstOccurrence,"%m/%d/%y %H:%M:%S")
| eval lasttime_epoch2=strptime(LastOccurrence,"%m/%d/%y %H:%M:%S")
| where lasttime_epoch-firsttime_epoch<300 OR lasttime_epoch2-firsttime_epoch2<300
then it works

[Ayn]

The sample you provided has another date format than what you posted in earlier events, so if that's the format you're working with you should change the strptime format string to reflect that.

[Ayn · Accepted solution]

A better way would probably be to use eval's strptime function to convert the timestamp strings to epoch values and then use these in your comparison.

... | eval firsttime_epoch=strptime(firsttime,"%Y/%m/%d %H:%M:%S")
| eval lasttime_epoch=strptime(lasttime,"%Y/%m/%d %H:%M:%S")
| where lasttime_epoch-firsttime_epoch<300

[crazyeva]

Thank you very much !
That is professional