Some mysql date data= 0000-00-00 is problem

Elastic Stack Elasticsearch
1

Hi,
I want to index mysql data. My problem is date field in mysql. Some data format is 0000-00-00. I can't index them with Elasticsearch 2.3.2. I am using sense editor and Php code.

My data mapping:

PUT ihaleler/ihale/_mapping
{
  "properties": {
    "last_date" : {
      "type" : "date",
      "format": "yyyy-MM-dd"
    }
  }
}

index.php:

if(page==index)
{
$dizi=array();            
            $dizi['id']=$id*1;
            $dizi['last_date']=$ihale['last_date'];
           
            $params['body']=$dizi;
            $params['index']=$index_name;
            $params['type']=$type_name;
            $params['id']=$id;
            
            $ret=$client->index($params);
}

my error interested in date is not format that 0000-00-00 ...
What can I do?

2

Don't set the date at all if it is 0000-00-00? Just skip adding it to $params.

3

for example:
...
last_date=0000-00-00
last_date=2016-05-01
last_date=2016-01-01
last_date=0000-00-00
...

Some date data is 0000-00-00 All of them is not.

4

This is a MySQL question, it is the only database which uses corrupt timstamps like 0000-00-00 00:00:00 to mark invalid timestamps.

Use something like this to convert to a null value

SELECT IF(last_date='0000-00-00 00:00:00',NULL,last_date) FROM TABLE
5

Thank you.
I am using left join because I have to do join 3 table.

table 1: tbl_ihale -->I
table 2: tbl_ihale_detay -->D
table3 : tbl_ihale_cetvel -->C

$ihale="SELECT I.*, D.detay, C.cetvel 
    FROM admin_ihale.tbl_ihale I
    LEFT JOIN admin_ihale.tbl_ihale_detay D
    ON I.id=D.id
    LEFT JOIN admin_ihale.tbl_ihale_detay_cetvel C
    ON I.id=C.id"; 
foreach($ihaleler as $ihale){
        $id=$ihale['id'];
        if($id>0){
            $params=array();
            $dizi=array();            
            $dizi['id']=$id*1;
            $dizi['last_date']=$ihale['last_date'];

Where should I use this code (SELECT IF(last_date='0000-00-00 00:00:00',NULL,last_date) FROM TABLE)?

6

I did something in index.php

if($page=='index'){
    

    $db->orderBy('id','asc');
    $ihaleler=$db->get('tbl_ihale');
        

    $ihale="SELECT I.*, D.detay, C.cetvel, 
    IF(last_date='0000-00-00',NULL,last_date)
    FROM admin_ihale.tbl_ihale I
    LEFT JOIN admin_ihale.tbl_ihale_detay D
    ON I.id=D.id
    LEFT JOIN admin_ihale.tbl_ihale_detay_cetvel C
    ON I.id=C.id"; 
    

    foreach($ihaleler as $ihale){
        $id=$ihale['id'];
        if($id>0){
            $params=array();
            $dizi=array();            
            $dizi['id']=$id*1;
            $dizi['last_date']=$ihale['last_date'];

My error :

Fatal error: Uncaught exception 'Elasticsearch\Common\Exceptions\BadRequest400Exception' with message '{"error":{"root_cause":[{"type":"mapper_parsing_exception","reason":"failed to parse [son_sonuc_tarihi]"}],"type":"mapper_parsing_exception","reason":"failed to parse [son_sonuc_tarihi]","caused_by":{"type":"illegal_field_value_exception","reason":"Cannot parse \"0000-00-00\": Value 0 for monthOfYear must be in the range [1,12]"}},"status":400}' in /home/admin/web/localhost/public_html/proje_ihale/vendor/elasticsearch/elasticsearch/src/Elasticsearch/Connections/Connection.php:655 Stack trace: #0 /home/admin/web/localhost/public_html/proje_ihale/vendor/elasticsearch/elasticsearch/src/Elasticsearch/Connections/Connection.php(632): Elasticsearch\Connections\Connection->tryDeserializeError(Array, 'Elasticsearch\\C...') #1 /home/admin/web/localhost/public_html/proje_ihale/vendor/elasticsearch/elasticsearch/src/Elasticsearch/Connections/Connection.php(553): Elasticsearch\Connections\Connection->tryDeserialize400Error(Array) #2 in /home/admin/web/localhost/public_html/proje_ihale/vendor/elasticsearch/elasticsearch/src/Elasticsearch/Connections/Connection.php on line 657

7

I added this code:

if($page=='index'){

    $db->orderBy('id','asc');
    $ihaleler=$db->get('tbl_ihale');

    $ihale="SELECT I.*, D.detay, C.cetvel
    FROM admin_ihale.tbl_ihale I
    LEFT JOIN admin_ihale.tbl_ihale_detay D
    ON I.id=D.id
    LEFT JOIN admin_ihale.tbl_ihale_detay_cetvel C
    ON I.id=C.id";

    foreach($ihaleler as $ihale){
        $id=$ihale['id'];
        if($id>0){
            $params=array();
           
 $dizi=array();            
            $dizi['id']=$id*1;
            if($ihale['last_date'] == '0000-00-00'){
                    $ihale['last_date'] = NULL;
                }
            $dizi['last_date']=$ihale['last_date'];//null

It worked true . Thank you