Selecting users having a birthday when it's midnight in their time zone

Hi all, I'm trying to write a cron job that will run every hour and select any users that are having a birthday when it's midnight in their time zone and I'm kind of having coder's block on it.

In my user table the pertinent fields I have are timezone and birthdate.

Time zone is stored as Time Zone Description | offset, for example, Eastern Standard Time is stored as "EST|-300" where the -300 is -300 minutes.

Birthdate is stored as a string date i.e., 1980-01-26.

The server time is set to EST.

The way I display the correct time to my users based on their time zone is as follows:

$datetime = time();      //server time
$offset = "-300"; //user's offset
$gmt = ($datetime + 18000);
$local = $gmt + ($offset * 60);
echo date("D M d, Y g:i a", $local);

Currently the cron job I have written runs every day at 6AM local server time (which is EST), grabs all users whose birthdate is the current day ("WHERE birthdate LIKE \"%-" . date("m-d", time()) . "\" ";) and email a birthday greeting.

What I want to do is modify this job so that it runs on the hour and emails users whose birthday it is when it's midnight in their time zone.

I could just grab all users, loop through and do the time calculations above but that doesn't sound very efficient, I'd like to be able to do it with just a SQL statement but am totally blanking on how I would do that.

Any ideas?Start Free Trial