I have documents with timestamp of following format: 2022-11-17T17:16:26.397Z

I try to get all documents on each day between two dates, and on each day between, lets say 11:05 and 15:05.

This is my querry:

"query": {
            "bool": {
                "filter": {
                    "bool": {
                        "must": [
                            {
                                "range": {
                                    "timestamp": {
                                        "gte": "2022-11-01",
                                        "lte": "2022-11-30"
                                    }
                                }
                            }, {
                                "script": {
                                    "script": {
                                        "source": "doc.timestamp.getHourOfDay() >= params.min && doc.timestamp.getHourOfDay() <= params.max",
                                        "params": {
                                            "min": 11,
                                            "max": 15
                                        }
                                    }
                                }
                            }

                        ]
                    }
                }
            }
        }
    }

Edit: The Error does not occure qhen the "script" part is removed

This raises following error and i do not know how to solve it :

RequestError(400, 'search_phase_execution_exception', 'runtime error')

Any help for solving the error is appreciated!

CodePudding user response：

query = {
    "query": {
        "bool": {
            "filter": [
                {
                    "range": {
                        "timestamp": {
                            "gte": "2022-11-01",
                            "lte": "2022-11-30"
                        }
                    }
                },
                {
                    "script": {
                        "script": {
                            "lang": "painless",
                            "source": """
          def targetDate = doc['timestamp'].value;
          def targetMinute = targetDate.getMinute();
          if(targetDate.getMinute() < 10)
          {
            targetMinute = "0"   targetDate.getMinute();
          }
          def timeFrom = LocalTime.parse(params.timeFrom);
          def timeTo = LocalTime.parse(params.timeTo);
          def target = LocalTime.parse(targetDate.getHour().toString()
            ":"  targetMinute);
          if(target.isBefore(timeTo) && target.isAfter(timeFrom)) {
            return true;
          }
          """,
                            "params": {
                                "timeFrom": "10:30",
                                "timeTo": "13:13"
                            }
                        }
                    }
                }
            ]
        }
    }
}

resp = es.search(index="test-sql-index", body=query)

The index looks like this `

doc_type: properties
{
    "author": {
        "type": "text",
        "fields": {
            "keyword": {
                "type": "keyword",
                "ignore_above": 256
            }
        }
    },
    "cell_id": {
        "type": "text",
        "fields": {
            "keyword": {
                "type": "keyword",
                "ignore_above": 256
            }
        }
    },
    "query": {
        "type": "text",
        "fields": {
            "keyword": {
                "type": "keyword",
                "ignore_above": 256
            }
        }
    },
    "result_rows": {
        "type": "long"
    },
    "returncode": {
        "type": "text",
        "fields": {
            "keyword": {
                "type": "keyword",
                "ignore_above": 256
            }
        }
    },
    "session": {
        "type": "text",
        "fields": {
            "keyword": {
                "type": "keyword",
                "ignore_above": 256
            }
        }
    },
    "text": {
        "type": "text",
        "fields": {
            "keyword": {
                "type": "keyword",
                "ignore_above": 256
            }
        }
    },
    "timestamp": {
        "type": "date"
    }
}

`

Running the query without the script:`

query = {
        "query": {
            "bool": {
                "filter": [
                    {
                        "range": {
                            "timestamp": {
                                "gte": "2022-11-01",
                                "lte": "2022-11-30"
                            }
                        }
                    },
                    
                ]
            }
        }
    }

returns the documents between the two dates, they look like this:`

{
    "took": 3,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 10000,
            "relation": "gte"
        },
        "max_score": 0.0,
        "hits": [
            {
                "_index": "test-sql-index",
                "_type": "_doc",
                "_id": "REA-T4QB_VJcUuQVgLKF",
                "_score": 0.0,
                "_source": {
                    "session": "eaf2b4ed-a512-4cf2-8eb5-37a111109792",
                    "query": "select MatrNr\nfrom vorlesungen",
                    "returncode": "(cx_Oracle.DatabaseError) ORA-00904: \"MATRNR\": invalid identifier\n[SQL: select MatrNr\nfrom vorlesungen]\n(Background on this error at: https://sqlalche.me/e/14/4xp6)",
                    "result_rows": "",
                    "cell_id": "select MatrNr\nfrom vorlesungen\n",
                    "timestamp": "2022-11-07T00:20:07.041430"
                }
            },

CodePudding user response：

My suggestion:

POST bar/_doc
{
  "date":"2022-11-14T11:12:46"
}

Python code

  query = {
        "query": {
            "bool": {
                "filter": [
                    {
                        "range": {
                            "date": {
                                "gte": "2022-11-01",
                                "lte": "2022-11-30"
                            }
                        }
                    },
                    {
                        "script": {
                            "script": {
                                "lang": "painless",
                                "source": """
              def targetDate = doc['date'].value;
              def targetMinute = targetDate.getMinute();
              if(targetDate.getMinute() < 10)
              {
                targetMinute = "0"   targetDate.getMinute();
              }
              def timeFrom = LocalTime.parse(params.timeFrom);
              def timeTo = LocalTime.parse(params.timeTo);
              def target = LocalTime.parse(targetDate.getHour().toString()
                ":"  targetMinute);
              if(target.isBefore(timeTo) && target.isAfter(timeFrom)) {
                return true;
              }
              """,
                                "params": {
                                    "timeFrom": "10:30",
                                    "timeTo": "15:13"
                                }
                            }
                        }
                    }
                ]
            }
        }
    }

    result = get_client_es().search(index="bar", body=query)
    print(result)