Date format incorrect results


(Hilal) #1

hi,

I want to do date filter and search.

My mapping :

$myTypeMapping['properties']['mydate']['type']='date';
$myTypeMapping['properties']['mydate']['index']='analyzed';
$myTypeMapping['properties']['mydate']['format']='YYYY-MM-dd HH:mm:ss';

My filter:

unset($filter_mydate);
if(isset($_GET['mydate_gte'])){
$filter_mydate['range']['mydate']['gte']=$_GET['mydate_gte'];
$filter_mydate['range']['mydate']['lte']=$_GET['mydate_lte'];
}

if(is_array($filter_mydate)){
$searchParams['body']['query']=$filter_mydate;
}

My search :

http://10.0.2.15:8080/filter.php?mydategte=2015-01-0100:00:00&mydatelte=2016-12-0113:00:59

unfortunately my results incorrect. How it can be Date format ?


(David Pilato) #2

Sounds like super similar to what you asked at Elasticsearch Range Filter

Can you reproduce your problem with a full curl script?


(Hilal) #3

yes It dosen't work true.
I realized My date results is true but my time results false


(Hilal) #4

please help me.

I want to do date data filter and search:
My date data:
2017-07-03 14:00:00

my search 1:

http://10.0.2.15:8080/filtreleme.php?date_try&date_gte=2017/01/01 00:00:00&date_lte=2018/01/01 23:59:59

my result 1: 0 (result is false. Actually it should be 2017-07-03 14:00:00)

My search 2:

http://10.0.2.15:8080/filtreleme.php?date_try&date_gte=2016/01/01 00:00:00&date_lte=2017/01/01 23:59:59

my result 2: 2017-07-03 14:00:00 ( It is false Actually it should be 0)
So It give accurate results when I type the date one year earlier.

My date mapping :

$myTypeMapping['properties']['date']['type']='date';
$myTypeMapping['properties']['date']['index']='not_analyzed';
$myTypeMapping['properties']['date']['format']='yyyy/MM/dd HH:mm:ss';

My filter:

unset($filter_date);
if(isset($_GET['date_try'])){
$filter_date['range']['date']['gte']=$_GET['date_gte'];
$filter_date['range']['date']['lte']=$_GET['date_lte'];
}
if(is_array($filter_date)){
$searchParams['body']['query']['filtered']['filter']=$filter_date;
}


(Hilal) #5

also when I just write year ıt works true. But when I write year with month, day and hour It works false.

I think date format is wrong. I couldn't date format. I didn't understand date format documents in elasticsearch website.


(David Pilato) #6

May be an issue with your PHP code?

Any chance you could test and write a pure script code (using curl or SENSE)?


(David Pilato) #7

I meant that 2017/01/01 23:59:59 is may be translated to 2017/01/01%2023:59:59?


(Hilal) #8

Thank you.

I tried but It is still the same.
I only year when I questioned, It works true
It dosen't work true when I questioned year with the day

I have to use php and elasticsearch


(Hilal) #9

I can do query and filter between years but I can not between years with month, day, hour,minute and second
Why is that? PLease help me.

Should I use strtotime() function for a solution?


(David Pilato) #10

I told you that you should try first with an example without using any client. So you make sure that:

  1. you understand how elasticsearch works
  2. where your error is coming from

So here is a full example which works:

DELETE index
PUT index
PUT index/doc/_mapping
{
  "properties": {
    "date": {
      "type": "date",
      "format": "yyyy-MM-dd HH:mm:ss"
    }
  }
}
PUT index/doc/1
{
  "date": "2016-12-01 13:00:59"
}
GET index/_search
{
  "query": {
    "range": {
      "date": {
        "gte": "2016-12-01 10:00:00",
        "lte": "2016-12-01 14:00:00"
      }
    }
  }
}

Passing date field throw rest
Mixing Filters
(Hilal) #11

Thank you. I did it. But it didn't correct. The problem still continues I think the problem is related to php.
I have to use php.


(David Pilato) #12

What is your mapping? I mean what is the result of GET _mapping? Don't copy any PHP code here as we can't trust this is exactly what elasticsearch is getting.

Are you using the official PHP client? If so may be @polyfractal can help.

I think the problem is related to php.

Yes. I do think the same.

So fix the problem on your end and make sure you are sending expected dates to elasticsearch.

Note that by default, date format is like 2016-12-01T14:00:00.


(Hilal) #13

I am using official PHP client. I corrected the date format but my problem continues. It dosen't return results format year/month/day/hours/minute/second.

The following query works correctly:
http://10.0.2.15:8080/filter.php?date_try&date_gte=2016&date_lte=2017

However the following query does not work correctly:

http://10.0.2.15:8080/filter.php?date_try&date_gte=2016-01-01T00:00:00&date_lte=2017-01-01T23:59:59

result: 2017-07-03 14:00:00 (this is wrong)

You know my mapping:

$myTypeMapping['properties']['date']['type']='date';
$myTypeMapping['properties']['date']['index']='not_analyzed';
$myTypeMapping['properties']['date']['format']='yyyy-MM-dd"T"HH:mm:ss';

(David Pilato) #14

If you need help, can you please answer to the questions we are asking?

We can't help without knowing exactly what you are doing.

Simple remark: a search engine does not behave as a database search.


(Hilal) #15

OK.

we are publishing thousands tender on the website every day. We are using MySQL and PHP in now. However it is not enough for us. So we decided to use Elasticsearch. We finished the indexing job.We need to question the date of all tenders. For example, customers must find tenders on the website that they want to the date range.

Is it not possible at this elasticsearch technology?


(David Pilato) #16

I gave you a fully working example.


(Hilal) #17

thank you for your example. I tried with sense editor .
My error:

{
"error": {
"root_cause": [
{
"type": "merge_mapping_exception",
"reason": "Merge failed with failures {[mapper [ihale_tarihi] of different type, current_type [string], merged_type [date]]}"
}
],
"type": "merge_mapping_exception",
"reason": "Merge failed with failures {[mapper [ihale_tarihi] of different type, current_type [string], merged_type [date]]}"
},
"status": 400
}


(David Pilato) #18

You probably did not execute the full script I gave.

DELETE index

is important here.


(Hilal) #19

Of course I deleted. But it dosen't work.
I am not sure but I think problem is indexing. We can not index mysql data. We would not be able to transfer data within the elastic. We continue working on the problem.


(David Pilato) #20

Can you test the script I gave please? No more. No less.

Then if it works, it proves that elasticsearch works well for such a case. (Which is the case on my laptop).
It would mean that your injection process is wrong.

Again, it's impossible to help if you don't give all the information about what you are doing and how.