Mongodb and Codeigniter Record between two dates

Question

I am working on to get records between two dates using CodeIgniter and MongoDB.Fetching record but not with correction.

My query:

$startDate=date('d-m-Y',$startDate);
$endDate=date('d-m-Y',$endDate);

$this->mongo_db->select("*");
if ($startDate != '') {
    $this->mongo_db->where_gte('created_date', $startDate);
}
if ($endDate != '') {
    $this->mongo_db->where_lte('created_date', $endDate);
}
$this->mongo_db->where('id', $id);
$results = $this->mongo_db->get('tbl_test');

Records in db like:

{ 
    "_id" : ObjectId("xxxxxxxxx..."), 
    "userId" : "4", 
    "pId" : "365", 
    "subject" : "hello", 
    "description" : "testing", 
    "status" : "0", 
    "status_description" : "", 
    "created_date" : "25-08-2018"
}

Trying to find the proper solution..

This is mostly caused due to ISODate format issue. Convert rour date to ISODate — Hari Prasad Sharma, Sep 21 '18 at 07:51
I am trying to save date $orig_date = new DateTime(); $orig_date = new MongoDB\BSON\UTCDateTime($orig_date); But time not matching, it is 6 hours back of current time ,date is ok. — Pawan Thakur, Sep 21 '18 at 08:29
Maybe the server time is different from your local time. Try setting it within PHP with like date_default_timezone_set( 'America/Los_Angeles' ); For reference: http://php.net/manual/en/function.date-default-timezone-set.php — Hari Prasad Sharma, Sep 21 '18 at 09:15
No I have already given in config file , when i am trying to save like date('Y-m-d H:i:s'). It is working fine — Pawan Thakur, Sep 21 '18 at 09:21
I am fetching data by this ObjectId.getTimestamp() and proper date time is saving in database — Pawan Thakur, Sep 21 '18 at 09:26

score 2 · Answer 1 · answered Sep 17 '18 at 15:30

2

Ideal solution would be to store date as date type instead of string and your query will work just fine.

{ 
    "_id" : ObjectId("xxxxxxxxx..."), 
    "userId" : "4", 
    "pId" : "365", 
    "subject" : "hello", 
    "description" : "testing", 
    "status" : "0", 
    "status_description" : "", 
    "created_date" : ISODate("2018-08-25T00:00:00Z") //YYYY-mm-ddTHH:MM:ssZ
}

If storing date is not an option you can use below query to convert the date string to date using $dateFromString and $expr available from 3.6 version.

{$dateFromString: { dateString: "25-08-2018",format: "%d-%m-%Y"}}

Mongodb Query:

db.colname.find({$expr:{$and:[{'$gte':[{'$dateFromString':{'dateString':'$created_date',format: '%d-%m-%Y'}},startDate]},  {'$lte':[{'$dateFromString':{'dateString':'$created_date',format: '%d-%m-%Y'}},endDate]}]}})

answered Sep 17 '18 at 15:30

s7vr

73,656
11
106
127

I am trying to save date into ISODate but time is not correct. $orig_date = new DateTime(); $orig_date = new MongoDB\BSON\UTCDateTime($orig_date); It is saving like "created_date" : ISODate("2018-09-21T07:01:10.000+0000") , but time is near about 5 hours back from current time – Pawan Thakur Sep 21 '18 at 08:33
That is expected. Mongodb saves datetime in utc. Your local time is 5 hours ahead of utc. Don't worry about it. Convert from local time to utc time before passing to query. Example [here](https://stackoverflow.com/questions/2095703/php-convert-datetime-to-utc) – s7vr Sep 21 '18 at 12:50
I have done it with my way.. Thanks for your help bro – Pawan Thakur Sep 25 '18 at 04:56

score 0 · Accepted Answer · answered Sep 25 '18 at 04:54

Date is in strtotime() . I have changed it in the same format that is stored in database

$startDate=date('Y-m-d',$startDate);
$endDate=date('Y-m-d',$endDate);

$this->mongo_db->select("*");
$searchCriteria =array('created_date' => array('$gte' => $startDate, '$lte' => $endDate),'id' => $id);
$this->mongo_db->where($searchCriteria);
$results = $this->mongo_db->get('tbl_test');

The above code is tested i have done all type of testing. I have changed format of date only.

Mongodb and Codeigniter Record between two dates

2 Answers2