CODEWITHSUNDEEP
mysqllaraveleloquenttimezone
Furqan Freed
Furqan Freed

Reputation: 376

Get Users Who has Birthday Today in their TimeZone

We have users from multiple timezone and our command runs and see if we have a user whose birthday is today in their timezone.

DB Structure

id => bigInt
name => string
dob => datetime
timezone => string

dob = Date Of Birth timezone = ex: America/New_York

We get all users whose birthday was yesterday, today and tomorrow on UTC time to cover all timezones and get limited users instead of all users

    $users = User::whereRaw("DATE_FORMAT(dob, '%m-%d') = '" . now()->format('m-d') . "'")
        ->orWhereRaw("DATE_FORMAT(dob,'%m-%d') = '" . now()->subDay(1)->format('m-d') . "'")
        ->orWhereRaw("DATE_FORMAT(dob,'%m-%d') = '" . now()->addDay(1)->format('m-d') . "'")
        ->selectRaw('*')
        ->whereNotNull('timezone')->get();

Then we loop through these users and see if a user's birthday is today in their timezone at a specific time like 9:00AM then we send them greetings.

    foreach ($users as $user) {
        if ($user->isMorningForUserOnBirthday()) {
            $user->notify(new BirthdayNotification($user));
        }
    }

Function isMorningForUserOnBirthday()

public function isMorningForUserOnBirthday()
{
    // its birthday date and for that user its time at 9:00AM
    if ((now($this->timezone)->day == $this->dob->day) && now($this->timezone)->hour == 9) {
        return true;
    }
    return false;
}

What We are Looking Far: we are getting results and all is working fine, next phase after working fine is making it awesome,

we want to refactor Eloquent Query and instead of using that function isMorningForUserOnBirthday() we want use MySql native things that could use users 'timezone' column and get us users whose birthday is today.

Upvotes: 0

Views: 555

Answers (2)

James Mills
James Mills

Reputation: 554

Making sure that we take into considerations that some timezones are not just a difference of hours (there are some 30/45 mins difference) we will need to run the scheduled command every 15 mins.

The helper function that you can add to your Users model would look like this

public function getUsersMatchingTodayHourInTheirTimezone($column_name, $time)
    {
        return $this->selectRaw("*, DATE_FORMAT(CONVERT_TZ(DATE_FORMAT(CONCAT(DATE($column_name), ' $time:00:00'), '%Y-%m-%d %T'), timezone, '+00:00'), '%m-%d %H:%i') as column_at_utc")
            ->having('column_at_utc', '=', now()->format('m-d H:i'))
            ->whereNotNull('timezone')
            ->get();
    }

Assuming that dob is 1989-07-28 00:00:00 or 1989-07-28 format.

You can change the $column_name (in this example we use dob) column to be whatever you want and you can also change the $time (In this example we wanted 9am for the user 09) to be whatever hour you want.

Assuming that you are running his on a system when the system and application time is set to UTC.

Calling this every 15 mins will make sure that you only get the users returned when it is the required time for them.

Upvotes: 1

Kalhan.Toress
Kalhan.Toress

Reputation: 21901

The logic in my head is that

Lets say i want to get all the users who are celebrating their birthdays on a given UTC date_time

EX: given date is: 06-04 (UTC) (which is in month-date format)

and all the dob are saved in the db with respective timezone, so now based on dob and their timezone we can calculate the UTC date for each dob

CONVERT_TZ(dob, timezone, "+00:00")
//CONVERT_TZ (dt, from_tz, to_tz)

This says that dob is in this timezone (because we save their dob in their timezone) and then we say convert it to UTC by passing +00:00

now we can use current UTC date to filter out the results

SELECT
    name,
    DATE_FORMAT(CONVERT_TZ(dob, timezone, "+00:00"), "%m-%d") as dob_utc
    FROM users HAVING dob = "07-01";

We need DATE_FORMAT because when checking dob, the year is not important.

When we put that in to laravel

$utc = now()->format('m-d')

$users = User::selectRaw("DATE_FORMAT(CONVERT_TZ(dob, timezone, '+00:00'), '%m-%d') as dob_utc")
    ->having("dob_utc", "=", $utc)
    ->whereNotNull('timezone')
    ->get();

Not tested

Upvotes: 2

Related Questions