Hi All,
I have a csv file, as following:
`
FINISH_DATE START_DATE
30/09/09 19:51:16 30/09/09 19:49:03
01/10/09 16:58:44 01/10/09 16:55:48
02/10/09 16:54:08 02/10/09 16:53:17
`
I want to find the difference between the two timestamps, and display it in third column in "%d/%m/%Y %H:%M:%S" format.
for e.g. the difference for the first row should be like 30/09/09 00:2:13
I have tried the following query, but it didnt helped.
convert timeformat="%d/%m/%y %H:%M:%S" mktime(START_DATE) as mktime(FINISH_DATE) as finish_date_epoch | eval diff_date_epoch=start_date_epoch -finish_date_epoch|eval DIFF_DATE=strftime(diff_date_epoch,"%H:%M:%S")
What I am doing wrong?
Please Help...!!!
Try this:
convert timeformat="%d/%m/%y %H:%M:%S" mktime(START_DATE) as start_date_epoch
mktime(FINISH_DATE) as finish_date_epoch
| eval DIFF_DATE=tostring(start_date_epoch -finish_date_epoch,"duration")
Also note that the duration will not contain a month/day/year, because that doesn't make any sense for a difference. The difference is not a date.
Is this actually missing ? You should have start_date_epoch where I've put MISSING:
convert timeformat="%d/%m/%y %H:%M:%S" mktime(START_DATE) as **MISSING**
mktime(FINISH_DATE) as finish_date_epoch
| eval diff_date_epoch=start_date_epoch -finish_date_epoch
| eval DIFF_DATE=strftime(diff_date_epoch,"%H:%M:%S")
I'm assuming thats just a typo in your post...
Maybe you can break apart your search and see which part fails, e.g.
convert timeformat="%d/%m/%y %H:%M:%S" mktime(START_DATE) as start_date_epoch
mktime(FINISH_DATE) as finish_date_epoch
| table start_date_epoch, finish_date,epoch
Then progressively add the evals. Then you might have a better idea of where its breaking...