strptime calculation not working correctly with / but works with - timeformat

[youngsuh]

Hi, communities,

I am doing a calculation or eval command.  

| eval dormancy=if(last_login="(never)",round((now()-strptime(created,"%Y-%m-%d"))/86400),round((now()-strptime(last_login,"%Y-%m-%d"))/86400)) 

The above calculate dormancy number correctly but, soon as I change the following code:

| eval dormancy=if(last_login="(never)",round((now()-strptime(created,"%Y/%m/%d"))/86400),round((now()-strptime(last_login,"%Y/%m/%d"))/86400)) 

from "-" to "/" strptime doesn't calculate the dormancy days.  Is this limit of strptime or am I doing something wrong?

[dtburrows3 · Accepted solution]

It sounds like you timestamps "created" and "last_login" have the format "%Y-%m-%d" in the events.

Trying to convert them to epoch using a different format will not work. For example

If you have a situations where your events have these field in a mixture of both formats, maybe you could adjust your eval to be something more like this?

| eval
        dormancy=if(
            last_login="(never)",
                round((now()-case(match(created, "^\d{4}\-\d{2}\-\d{2}"), strptime(created,"%Y-%m-%d"), match(created, "^\d{4}\/\d{2}\/\d{2}"), strptime(created,"%Y/%m/%d")))/86400),
                round((now()-case(match(last_login, "^\d{4}\-\d{2}\-\d{2}"), strptime(last_login,"%Y-%m-%d"), match(last_login, "^\d{4}\/\d{2}\/\d{2}"), strptime(last_login,"%Y/%m/%d")))/86400)
            )

 
This seem to extract both formats properly