Date range with whereBetween

Hi,

I'm fighting with Carbon and the query builder, i want to be able to filter my data with a time range like :

api/data/?start=29-09-2016&end=29-09-2016

This should return the data of today.

My scope :

public function scopeDateBetween($query, $start, $end) {
     return $query->whereBetween('created_at', [$start, $end]);
    }

I pass an instance of carbon to $start and $end like :

$start = new Carbon($request->start);
$end = new Carbon($request->end);
$clicks->dateBetween($start, $end);

But this doesn't work. No results are returned.

Any solution ?

Thank you.

ttracey

9 years ago

Level 1

For starters, Carbon has some pretty convenient methods for instantiation. You can do something like

Carbon::createFromFormat('Y-m-d H', '1975-05-21 22')

I would peruse the instantiation section of the Carbon documentation to find what fits your needs most closely.

http://carbon.nesbot.com/docs/#api-instantiation

P.S. You're getting no results because you're passing the same dates to the constructor. Vanilla dates like that will evaluate to Midnight, or 00:00:00. So you are searching between 29-09-2016 00:00:00 and 29-09-2016 00:00:00, an interval of 0 milliseconds. Makes sense to get no results! I'm not sure if you would even get results for something that happened at exactly 29-09-2016 00:00:00.

Good luck

2 likes

tykus

9 years ago

Best Answer

Level 104

Create a Carbon instance for both your start and end dates and chain the startOfDay() and endOfDay() methods:

$start = Carbon::parse($request->start)->startOfDay();  //2016-09-29 00:00:00.000000
$end = Carbon::parse($request->end)->endOfDay(); //2016-09-29 23:59:59.000000
$clicks->dateBetween($start, $end);

This gives you a full day given the same request dates

1 like

Estev

9 years ago

Level 2

@tykus_ikus Working ! With the help of ttracey i found this :

$start = new Carbon($request->start);
$end = Carbon::createFromFormat('d-m-Y H', $request->end.' 24');

In this case $end become tomorrow of $start and it works. But your solution is more straightforward than mine.

Thank you.

tykus

9 years ago

Level 104

@Estev whatever works for you works ;D just remember that you'll have to understand the code again months into the future and what is easier to grok should win the day!

1 like

Please or to participate in this conversation.