Find Customers who have birthdays in next 7 days in laravel

Question

I need to get list of Customers who have birthdays in next 7 days in laravel. i using following query. that query display passed birthdays also.

 $customerslist=Customers::WhereRaw('DAYOFYEAR(curdate()) <= DAYOFYEAR(date_of_birth) AND DAYOFYEAR(curdate()) + 7 >=  dayofyear(date_of_birth)' )->OrderBy(DB::raw("DAYOFYEAR(date_of_birth)"),'ASC')->get();

i use below query listed following but missing some customers

Missing Customer

Dont use DAYOFYEAR like this. DAYOFYEAR("2018-12-31") is 365 but DAYOFYEAR("2019-01-01") is 1. — Ben Sholdice, Jun 20 '19 at 18:46

Mallesbixie · Answer 1 · 2021-04-29T21:33:43.203

I found the raw query with the DAYOFYEAR approach very elegant, because it eliminates all problems with the different years of birth. It will however not handle leap years very nice.

I found a combination of the BETWEEN and DATE_FORMAT produces the correct results. It does need a check to handle looking over the years edge.

Here is an example of how to implement this in a scope for your model:

public function scopeBirthDayBetween ($query, Carbon $from, Carbon $till)
{
    $fromMonthDay = $from->format('m-d');
    $tillMonthDay = $till->format('m-d');
    if ($fromMonthDay <= $tillMonthDay) {
        //normal search within the one year
        $query->whereRaw("DATE_FORMAT(birthdate, '%m-%d') BETWEEN '{$fromMonthDay}' AND '{$tillMonthDay}'");
    } else {
        //we are overlapping a year, search at end and beginning of year
        $query->where(function ($query) use ($fromMonthDay, $tillMonthDay) {
            $query->whereRaw("DATE_FORMAT(birthdate, '%m-%d') BETWEEN '{$fromMonthDay}' AND '12-31'")
                ->orWhereRaw("DATE_FORMAT(birthdate, '%m-%d') BETWEEN '01-01' AND '{$tillMonthDay}'");
        });
    }
}

Usage:

$users = User::birthDayBetween(Carbon::now(), Carbon::now()->addWeek())->get()

To add ordering on birth day rather than birth date, some more Raw queries and DATE_FORMAT can help.

$users = User::query()
    ->birthDayBetween($from, $till)
    ->orderByRaw("DATE_FORMAT(birth_date,'%m%d')")
    ->orderByRaw("DATE_FORMAT(birth_date,'%y') desc")
    ->orderBy('first_name')
    ->get();

NOTE: When looking over the years edge, the ordering will be off since the users that have their birthday at the beginning of the next year will be ordered first. I chose to solve that in the front end.

ManojKiran A · Answer 2 · 2019-06-20T06:15:59.213

for the immediate solution i have created the raw query

// start range 7 days ago
$start = date('z') + 1 - 7;
// end range 7 days from now
$end = date('z') + 1 + 7;
$customerslist = Customers::whereRaw("DAYOFYEAR(birthday) BETWEEN $start AND $end")->get();

EDITED

So i have Created the 5000 records with the random year and date

MethodOne Using Eloquent filter

$checkRange =   array_map(
        function ($date) {
            return $date->format('Y-m-d');
        },
        \Carbon\CarbonPeriod::create(now(), now()->addDays(7))->toArray()
    );


    $carbonWay = Customer::get()->filter(function($eachCus) use ( $checkRange){
        return in_array( $eachCus->dob, $checkRange);
    });

But if you have Many Customer it will heatup the sql server

Method Two QueryBuilder Way

 $eloquentway = Customer::whereDate('dob','>=', now())
                    ->whereDate( 'dob', '<=', now()->addDays(7))
                    ->get();

So both results in the Same Results in the same for me

Finaly i have Checked if there the ids are mathes so

$idsofCarbon = $carbonWay->pluck('id')->toArray();
    $idsofFilter = $elequentway->pluck('id')->toArray();

    dump(array_diff( $idsofFilter, $idsofCarbon));
    dump(array_diff( $idsofCarbon, $idsofFilter));

Both are giving me the [] Which means that the result are accurate

For more about date filtering

https://laraveldaily.com/eloquent-date-filtering-wheredate-and-other-methods/ https://laravel.com/docs/5.8/queries#where-clauses

Kindly Comment if any issues

Hope it helps

ok i will create some fake data and test it in myside and edit the answer — ManojKiran A, Jun 20 '19 at 05:28
DAte Format should be like this format('m-d'), and return should be like this return in_array( date('m-d', strtotime($eachCus->dob)), $checkRange); Because dob not gonna match with upcoming days due to change of birth year — Sahilbalgotra100, Apr 07 '21 at 07:49

Kevin Ho · Answer 3 · 2019-06-25T09:59:00.487

1

You could try to read laravel ORM article.

Use whereBetween to select the date, and using Carbon date is easier to read.

return $this->model
        ->whereBetween('dateOfBirth_Column', [Carbon::today()->toDate(), Carbon::today()->addDays(7)->toDate()])
        ->orderBy('dateOfBirth_Column')
        ->get();

Hope this helps.

edited Jun 25 '19 at 09:59

answered Jun 20 '19 at 08:28

Kevin Ho

19
2

2

it wont work because it will serach only with the current year – ManojKiran A Jun 20 '19 at 11:53
@Manojkiran.A incorrect. Carbon handles year rollover accurately. – Ben Sholdice Jun 20 '19 at 18:39
3

@BenSholdice If date of birth is listed in the database as for example 1990-06-21, then `Carbon::today()->addDays(7)` **will not** catch this. Even though technically the birthday is tomorrow, it's tomorrow, but 29 years ago. See the issue? Birthdays happen every year, which is why `DAYOFYEAR()` is being used. – Tim Lewis Jun 20 '19 at 21:05
@TimLewis ah yes, you are right. even so, `DAYOFYEAR()` as the original poster used is bad and wont handle the year rollover. – Ben Sholdice Jun 20 '19 at 21:36
@BenSholdice That's also correct, but an edge case. For that, you might need to use `IN(365, 1, 2, 3, 4, 5, 6)` or something. – Tim Lewis Jun 21 '19 at 14:07
it wont work because it will serach only with the current year + 1 – Pablo Papalardo Jan 30 '20 at 14:52

score 1 · Answer 4 · answered Jun 21 '19 at 22:30

As @TimLewis points out in comments, your model has a date-of-birth field and you are using it in comparisons with the current date (likely many years after to the date-of-birth).

php solution:

for readability, and to handle year rollover issues, you should:

use Carbon for your dates & date math
find the user's next/upcoming birthday
remember that a Y-m-d format usually implies a time of 00:00:00.

$now = Carbon::now();
$customers = Customer::all();

// find next birthday for each customer
foreach ($customers as $customer) {
    $curyear_bd = Carbon::createFromFormat('Y-m-d', $customer->date_of_birth)->setYear($now->year);
    $now > $curyear_bd->endOfDay() ? $next_bd = $curyear_bd->addYear(1) : $next_bd = $curyear_bd;

    if ($now <= $next_bd->startOfDay() && $next_bd <= Carbon::now()->addDay(7)->endOfDay())
    {
        print "customer $customer->name has birthday coming up\n";
    }
}

MySQL solution

You could use an SQL query like so:

select * from customers where
if (
CURDATE() >= date_add(date_of_birth, interval  TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())    year), 
             date_add(date_of_birth, interval (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())+1) year), 
             date_add(date_of_birth, interval  TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())    year)
)
between
curdate()
and
date_add(curdate(), interval 7 day);

but I'd be wary of timezones (server, client etc). This might warrant its own SO question...

I found the TIMESTAMPDIFF from Get difference in years between two dates in MySQL as an integer

score 0 · Answer 5 · answered Jun 20 '19 at 05:59

0

one possible solution is this

$customerslist = Customers::whereRaw("date_of_birth BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY")->get()

answered Jun 20 '19 at 05:59

harisu

1,376
8
17

score 0 · Answer 6 · answered Jan 30 '20 at 15:25

My solution:

On your user model

    use Carbon\CarbonPeriod;

    public function scopeBirthdayBetween($query, $dateBegin, $dateEnd)
    {
        $monthBegin = explode('-', $dateBegin)[0];
        $dayBegin = explode('-', $dateBegin)[1];
        $monthEnd = explode('-', $dateEnd)[0];
        $dayEnd = explode('-', $dateEnd)[1];
        $currentYear = date('Y');

        $period = CarbonPeriod::create("$currentYear-$monthBegin-$dayBegin", "$currentYear-$monthEnd-$dayEnd");

        foreach ($period as $key => $date) {
            $queryFn = function($query) use ($date) {
                $query->whereMonth("birthday", '=', $date->format('m'))->whereDay("birthday", '=', $date->format('d'));
            };

            if($key === 0) {
                $queryFn($query);
            } else {
                $query->orWhere(function($q) use ($queryFn) {
                    $queryFn($q);
                });
            }
        }

        return $query;
    }

Usage:

   User::birthdayBetween('01-30', '02-15')->get()

Robin van Baalen · Answer 7 · 2022-06-21T14:49:14.567

A slight tweak on @mallesbixie's original answer sets defaults for the $from and $till values, defaulting to upcoming birthdays in the next month.

/**
 * @param Builder $builder
 * @param DateTime|null $from
 * @param DateTime|null $till
 * @return Builder
 */
public function scopeUpcomingBirthdays(Builder $builder, DateTime $till = null, DateTime $from = null): Builder
{
    $from = $from ?? today();
    $till = $till ?? today()->addMonth();

    $fromMonthDay = $from->format('m-d');
    $tillMonthDay = $till->format('m-d');
    if ($fromMonthDay <= $tillMonthDay) {
        //normal search within the one year
        return $builder->whereRaw("DATE_FORMAT(dob, '%m-%d') BETWEEN '{$fromMonthDay}' AND '{$tillMonthDay}'");
    }

    //we are overlapping a year, search at end and beginning of year
    return $builder->where(function ($query) use ($fromMonthDay, $tillMonthDay) {
        $query->whereRaw("DATE_FORMAT(dob, '%m-%d') BETWEEN '{$fromMonthDay}' AND '12-31'")
            ->orWhereRaw("DATE_FORMAT(dob, '%m-%d') BETWEEN '01-01' AND '{$tillMonthDay}'");
    });
}

Usage:

// Get birthdays in the upcoming month
Customers::query()->upcomingBirthdays()->get();

// Get birthdays in the upcoming week
Customers::query()->upcomingBirthdays(today()->addWeek())->get()

score -1 · Answer 8 · answered Jun 20 '19 at 05:58

-1

I added the raw query please check if its working or not and let me know

 $customerslist=Customers::WhereRaw(' date_of_birth >= DATEADD(day,-7, GETDATE())' )->get();

Hope it helps

answered Jun 20 '19 at 05:58

Boni

338
2
13

score -1 · Answer 9 · answered Jun 20 '19 at 06:16

-1

Using the carbon date

$customerslist= Customers::whereDate('date_of_birth', '<=', Carbon::today()->addDays(7))->get();

answered Jun 20 '19 at 06:16

harisu

1,376
8
17

score -1 · Answer 10 · answered Jun 15 '21 at 14:58

-1

I faced this challenge and solved it this way:

$next_days = now()->addDays(7);
$customers = Customers::query()->whereMonth('date_of_birth', $next_days->month)->whereDay('date_of_birth',$next_days->day)->get();

answered Jun 15 '21 at 14:58

hassan helfi

1
1
1

That will only give a list of customers where the birthday is on the 7th day from now. The question needs all the customers who have birthdays in the next 7 days, so you have to do a whereBetween query. – Vishal Srivastava Jul 23 '21 at 16:56

Find Customers who have birthdays in next 7 days in laravel

10 Answers10