Calculate Days from current date custom date

[kpavan]

Hi All,

Need help on below query to calculate ticket age from ticket creation date with current date. Please help me to fix this query. From below query am not getting the ticket_age its giving me the blank field, but both current date and ticket creation date's are getting converted correctly. But not sure where is missing. Please help me on this.

base search
|eval Tday=now()
|eval Today=strftime(now(), "%Y-%d-%m")
|eval CREATION_DATE=strftime(strptime(TICKET_CREATION_DATE, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d")
|eval TICKET_AGE=(Today - CREATION_DATE)
|table Request_Status CREATION_DATE TICKET_AGE

Thanks!
Pavan

[niketn]

@kpavan, if you want to use string time you need to conver the fields to YYYYmmdd not YYYY-mm-dd otherwise it will be treated as string not number. You can try out the following query. In your query there is also a mistake with the two string date formats being different i.e. %Y-%m-%d and %Y-%d-%m

| makeresults
| eval TICKET_CREATION_DATE="2017-10-11 10:20:30"
| eval Today=strftime(now(), "%Y%m%d")
| eval CREATION_DATE=strftime(strptime(TICKET_CREATION_DATE, "%Y-%m-%d %H:%M:%S"),"%Y%m%d")
| eval TICKET_AGE=(Today - CREATION_DATE)

However, you are better off using strptime to convert to epoch time. You can use fieldformat to display epoch time as string time.

[gcusello · Accepted solution]

Hi kpavan,
to calculate a difference between two dates, you have to convert them in epochtime using strptime:

base search
|eval CREATION_DATE=strptime(TICKET_CREATION_DATE, "%Y-%m-%d %H:%M:%S"), TICKET_AGE=(now()-CREATION_DATE)
|table Request_Status CREATION_DATE TICKET_AGE

Bye.
Giuseppe

[kpavan]

Thanks Giuseppe Its working!