Why is _time storing incorrect value?

[angelinealex]

I use the below saved search and scheduled it and enabled the summary index.

| dbxquery connection=connectionname query="select startdatetime, enddatetime from tablename where enddatetime > ? AND enddatetime < ?"
 [| makeresults 
    | appendcols 
        [| inputlookup kvstorelookup 
           ....
           ....
          | eval starttime="2018-07-19 23:00:01"
          | eval endtime="2018-07-19 23:15:00"
          | eval daterange=starttime.",".endtime
          | rename daterange as params
          | fields params]] 
| eval _time=enddatetime
|fields _time, startdatetime, enddatetime
|table _time, startdatetime, enddatetime

When I run the query manually it shows _time as exactly same as enddatetime value.
But when it runs through scheduler _time is always stored as "2018-07-19 00:59:59"

I am not sure why this is happening. Can someone please help?

[angelinealex · Accepted solution]

Fixed the issue. Missed to convert the time to epoch before storing in _time.

Below line fixed the issue.
| eval _time=strptime(enddatetime,"%Y-%m-%d %H:%M:%S.%1N")

[manish_singh_77]

Don't use _time in your query and then try to compare the searches...Let me know if it works for you...

[angelinealex]

I tried, it didnt work. Still _time value stored as "2018-07-19 00:59:59"

[jcoates]

I think you need to do an input instead to control time, http://docs.splunk.com/Documentation/DBX/3.1.3/DeployDBX/Createandmanagedatabaseinputs

Have you tried naming the time column to _time in your SQL?

[angelinealex]

you mean enddatetime as _time in select query? If so, yes I did. But it didnt work.

I also tried removing _time field from this saved search. But still the _time value stored as "2018-07-19 00:59:59"