# Some mysql date data= 0000-00-00 is problem **URL:** \ **Category:** Elasticsearch\ **Created:** [May 6, 2016, 1:51pm UTC](https://discuss.elastic.co/t/some-mysql-date-data-0000-00-00-is-problem/49413 "2016-05-06T13:51:48Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\ **Post date:** [May 6, 2016, 1:51pm UTC](https://discuss.elastic.co/t/some-mysql-date-data-0000-00-00-is-problem/49413/1 "2016-05-06T13:51:48Z")
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? ---
**Author:** ![nik9000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nik9000/32/44947_2.png) [@nik9000](https://discuss.elastic.co/u/nik9000)\ **Post date:** [May 6, 2016, 2:04pm UTC](https://discuss.elastic.co/t/some-mysql-date-data-0000-00-00-is-problem/49413/2 "2016-05-06T14:04:54Z")
> [@Hilal](#): > > What can I do? Don't set the date at all if it is 0000-00-00? Just skip adding it to $params. ---
**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\ **Post date:** [May 6, 2016, 2:11pm UTC](https://discuss.elastic.co/t/some-mysql-date-data-0000-00-00-is-problem/49413/3 "2016-05-06T14:11:31Z")
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. ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [May 7, 2016, 9:25am UTC](https://discuss.elastic.co/t/some-mysql-date-data-0000-00-00-is-problem/49413/4 "2016-05-07T09:25:45Z")
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 ```auto SELECT IF(last_date='0000-00-00 00:00:00',NULL,last_date) FROM TABLE ``` ---
**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\ **Post date:** [May 9, 2016, 8:06am UTC](https://discuss.elastic.co/t/some-mysql-date-data-0000-00-00-is-problem/49413/5 "2016-05-09T08:06:41Z")
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)`? ---
**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\ **Post date:** [May 9, 2016, 12:13pm UTC](https://discuss.elastic.co/t/some-mysql-date-data-0000-00-00-is-problem/49413/6 "2016-05-09T12:13:50Z")
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` ---
**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\ **Post date:** [May 11, 2016, 11:01am UTC](https://discuss.elastic.co/t/some-mysql-date-data-0000-00-00-is-problem/49413/7 "2016-05-11T11:01:23Z")
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 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 10:52pm UTC](https://discuss.elastic.co/t/some-mysql-date-data-0000-00-00-is-problem/49413/8 "2017-07-05T22:52:30Z")