How to search for Entries Between DateTime field value?

[akpuvvada]

I am trying to find entries between a date-time range based on a field in the event 'Date'. It date-time value of the field is in the format - %Y-%m-%d %H:%M:%S.

I am using below query:

(index=myindex) ((strptime(Date, "%Y-%m-%d %H:%M:%S")>=strptime("2023-02-11 00:00:00", "%Y-%m-%d %H:%M:%S")) AND (strptime(Date, "%Y-%m-%d %H:%M:%S")>=strptime("2023-02-11 23:59:59", "%Y-%m-%d %H:%M:%S")))

It is giving me below Error:

Error in 'search' command: Unable to parse the search: Comparator '>=' has an invalid term on the left hand side: (Date "%Y-%m-%d %H:%M:%S"). 

Sample Data:

{
  "InterfaceNumber": "I-XXXX",
  "SystemCode": "sys",
  "Element": "JobID:bw0a104of",
  "Data": "",
  "Severity": "Error",
  "ProcessContext_ProcessId": "bw0a104of",
  "Subject": "/api/login",
  "Date": "2023-02-14 12:25:01",
  "ErrorMessages": "This is an Error",
  "ProcessContext_ProjectName": "MyApp.application",
  "HostName": "localhost.mydomain.com",
  "ProcessContext_RestartedFromCheckpoint": false,
  "ProcessContext_EngineName": "MyApp"
 }

Not sure what I am doing wrong. Any help appreciated.

--

[bowesmana · Accepted solution]

You can't do these type of criteria on the search command, as parsing data has to be done in the eval - unless you set up a calculated field that does these evals for you.

This is how you would do it

(index=myindex) 
| eval Date_value=strptime(Date, "%Y-%m-%d %H:%M:%S")
| eval min_Date=strptime("2023-02-11 00:00:00", "%Y-%m-%d %H:%M:%S")
| eval max_Date=strptime("2023-02-12 00:00:00", "%Y-%m-%d %H:%M:%S")
| where (Date_value >= min_Date AND Date_value < max_Date

Note that your second comparison said >= but I imagine you means <=.

However, I also changed the second strptime for end date to be 00:00:00 on the 12th and used less than rather than <=, as yours will miss any events that come in between 23:59:59 and 00:00:00 (1000 milliseconds gap).

Note that your data range is always searched by the _time field, so if your Date field has the same value as the _time field, then you don't need any of this, you just use earliest/latest values in the search or time picker.