Working with Laravel dates for dummies?

Can someone recommend a course I can take that explains better how to compare and filter using dates with Laravel and MySQL? Every time I think I understand, I realize I don’t get what works and doesn’t and I’m beyond frustrated.

I’m working with Carbon dates and trying to compare them - doesn’t work! Trying to filter carbon period to remove times not in an array of carbons and it doesn’t work. I just want to throw my computer out the window!

Please guide me. Dumb it down.

LaryAI

2 years ago

Level 58

It sounds like you're having a tough time with date manipulation in Laravel, which is a common challenge for many developers. Laravel uses the Carbon library for date handling, which is a powerful and fluent PHP API extension for DateTime. Here's a mini-guide to help you understand how to work with dates in Laravel and MySQL:

Understanding Carbon

First, make sure you understand the basics of Carbon. You can find the documentation here: https://carbon.nesbot.com/docs/

Comparing Dates

To compare dates in Laravel using Carbon, you can use methods like isBefore(), isAfter(), eq() (equals), and many others.

$now = Carbon::now();
$yesterday = Carbon::yesterday();

if ($now->gt($yesterday)) { // gt means greater than
    echo "Now is after yesterday.";
}

if ($now->lt($yesterday)) { // lt means less than
    echo "Now is before yesterday.";
}

if ($now->eq($yesterday)) { // eq means equal to
    echo "Now is the same as yesterday.";
}

Filtering Dates

When you want to filter dates, you can use Laravel's query builder or Eloquent to compare dates directly in your database queries.

$users = User::whereDate('created_at', '2021-01-01')->get(); // Users created on a specific date

$users = User::whereDate('created_at', '>=', '2021-01-01')->get(); // Users created after a specific date

$users = User::whereBetween('created_at', [$startDate, $endDate])->get(); // Users created between two dates

Carbon Period

If you're working with a range of dates, you might want to use CarbonPeriod. It allows you to iterate over a period of time, applying filters as needed.

$period = CarbonPeriod::create('2021-01-01', '2021-01-10');

// Iterate over the period
foreach ($period as $date) {
    echo $date->format('Y-m-d');
}

// Exclude certain dates from the period
$excludedDates = [Carbon::parse('2021-01-05'), Carbon::parse('2021-01-07')];
$period->filter(function ($date) use ($excludedDates) {
    foreach ($excludedDates as $excludedDate) {
        if ($date->eq($excludedDate)) {
            return false;
        }
    }
    return true;
});

// Iterate over the filtered period
foreach ($period as $date) {
    echo $date->format('Y-m-d');
}

Courses and Resources

As for courses, Laracasts itself has a series called "Laravel From Scratch" that covers the basics of Laravel, including working with dates. You can also check out the official Laravel documentation on dates and mutators: https://laravel.com/docs/eloquent-mutators#date-mutators

Remember, practice is key. Try to create small examples and manipulate dates in different ways to get comfortable with Carbon and Laravel's date handling features. If you have specific examples of what's not working, feel free to post them, and the community can help you troubleshoot.

1 like

jlrdw

2 years ago

Level 75

Let the sql do the work, I usually get the needed dates from a calendar like:

        $bdate = Request::input('begindate');
        $edate = Request::input('enddate');

And in code, all code not shown:

       // code here to figure offset, etc
        
        $pagingQuery = "LIMIT {$offset}, {$perpage}";
        $sql = "select distinct `account_types`.`AccountType` AS `AccountType`,`accounts`.`AccountNumber` ";
        $sql .= "AS `AccountNumber`,`accounts`.`AccountName` AS `AccountName`,sum(`transactions`.`Expense`) ";
        $sql .= "AS `Sum_Expense`,sum(`transactions`.`Income`) AS `Sum_Income` ";
        $sql .= "from ((`account_types` join `accounts` on((`account_types`.`AccountTypeID` = ";
        $sql .= "`accounts`.`AccountTypeID`))) join `transactions` on((`accounts`.`AccountID` = ";
        $sql .= "`transactions`.`AccountID`))) ";
        $sql .= "where (`transactions`.`TransactionDate` Between :bdate and  :edate) ";
        $sql .= "group by `account_types`.`AccountType`,`accounts`.`AccountNumber`,`accounts`.`AccountName` ";
        $sql .= $pagingQuery;
        $sth = DB::connection('mysqlcb')->getPdo()->prepare($sql);
        $params = ['bdate' => $bdate, 'edate' => $edate];
        $sth->execute($params);
        $quy = $sth->fetchAll(\PDO::FETCH_OBJ);
        $report = new LengthAwarePaginator($quy, $numrows, $perpage);
        $title = 'Monthly Report';
        return view('account.report', compact('report', 'params', 'title'));

But notice this line:

"where (`transactions`.`TransactionDate` Between :bdate and  :edate) ";

To me this stuff is easier without carbon, in the query just use mysql formatted dates.

Please or to participate in this conversation.