NOW whereBetween two database dates

Are the dates in show_from and show_till column Carbon instances?

You need to declare them inside the 'items' Model.

protected $dates = ['show_from', 'show_till'];

Thanks for the response!

So I added the dates to my Item Model, but I still don't get the items that are currently (current time) between the show_from and show_till time.

Maybe it's because they are in a pivot table?

This is what I have in my Screen Model

public function items()
    {
        return $this->belongsToMany(Item::class)->withPivot('id', 'screen_id', 'content', 'item_order', 'approved', 'show_from', 'show_till')->withTimestamps()->orderBy('item_order', 'asc');
    }

You'd need a whereBetween clause to specify that Carbon::now() is between show_from and show_till... and really, if you're loading that much extra data into your pivot table, you're probably better off using a model to represent the pivot rather than withPivot.

Or maybe I'm misunderstanding. But it sounds like you have these dates, the order, etc on the pivot table, rather than on the Item table where they more likely belong.

@willvincent but the whereBetween clause only lets me specify 2 parameters, a database field and a array of 2 dates of a range. Is it possible to do the opposite? Have 2 fields from the database as range and 1 field as Carbon::now()?

And thanks for the advice, they do belong in a pivot as the Item has data that can be used multiple times. I didn't knew I could create a model for a pivot.

Can you check like this ?

$show_from = Screen::find($id)->items()
                  ->where('show_from', '<=', Carbon::now())->get();

$show_till = Screen::find($id)->items()
                   ->where('show_till', '>=', Carbon::now())->get();


$screen = $show_from->merge($show_till );

return $screen;

OR

Screen::find($id)->items()
                  ->where('show_from', '<=', Carbon::now())
                  ->orWhere('show_till', '>=', Carbon::now())->get();

@sabuncuserhat I don't think that will work as both checks need to be in 1 query, else it will still show the outdated items.

So it needs to be within show_from AND show_till. If I do a OR statement it will show the outdated items too.

Use a closure?

->where(function ($q) {
  $q->where('show_from', '<=', Carbon::now());
  $q->where('show_till', '>=', Carbon::now());
})

That will ensure that these two conditions must be true -- independent of any other where clauses.

In other words, when the query string is written, these will be within a set of parenthesis:

SELECT * FROM Foo WHERE (show_from <= whatever AND show_till >= whatever)

Or just use keyboard and type something like

SELECT * FROM Foo WHERE show_from Between begindate AND enddate

And you DO NOT need carbon to do things like this.

@jlrdw That's not what is being asked.

Specifically @renzz17 is looking for records where the current date/time is between show_from and show_till fields.

And, specifically wanting to do so with eloquent -- suggesting otherwise is really counter productive.

Thanks @willvincent the where closure works :)

@willvincent your solution worked perfectly for me but I'm having an issue with UTC conversion and I was wondering if you knew a solution (yes, I know this conversation is old and you might not be around to solve it).

I have a table that has a field labelled 'start_at' and a field labelled 'end_at' and these store times when someone is available to work, and it stores these values in UTC (so if I can work 2pm - 10pm then it is stored in the DB as 18:00:00 and 02:00:00 respectively). So right about now you should see where my dilemma is with your above code. If it is currently 5pm local time, converted to UTC that would be 21:00:00, and that would satisfy the first 'where' clause but it would fail the second 'where' clause (as 02:00:00 is not > 21:00:00).

So my question is, in the 'where' clause is there a way to convert the 'show_from' and 'show_till' fields to my current timezone before doing the comparison?

Thank you