MySQL NOW()与PHP时区不匹配致邮箱验证链接过期逻辑失效求助

Hey there! I can see exactly where the problem is—you’re right about the timezone mismatch between PHP and MySQL causing this issue. Let’s break down how to fix it step by step.

The Root Cause #

You’ve set PHP’s timezone to Asia/Kolkata with date_default_timezone_set('Asia/Kolkata'), and you’re generating tokenExpire using that timezone. But MySQL’s NOW() function is likely using a different default timezone (like UTC), so when you compare tokenExpire > NOW(), the two timestamps aren’t in sync. That’s why your link still shows as valid even after the 10-minute window passes.

Step 1: Check MySQL’s Current Timezone #

First, confirm what timezone MySQL is using. Run this query directly in your MySQL client or phpMyAdmin:

SELECT @@global.time_zone, @@session.time_zone;

This will show you if MySQL’s global or session timezone is different from Asia/Kolkata.

Step 2: Sync PHP and MySQL Timezones #

You have two reliable ways to align the timezones:

Option A: Set MySQL’s Default Timezone Permanently

Edit your MySQL configuration file (usually my.cnf on Linux or my.ini on Windows) and add this line under the [mysqld] section:

default-time-zone='Asia/Kolkata'

Save the file and restart your MySQL server. This ensures all future connections use the correct timezone.

Option B: Set Timezone Per Database Connection (Temporary but Quick)

Right after you establish your PDO connection in PHP, execute a query to set the session timezone to match PHP’s:

$con = new PDO("mysql:host=your_host;dbname=your_db", "user", "pass");
// Add this line to sync timezone
$con->exec("SET time_zone = 'Asia/Kolkata';");

This will make sure the current database session uses the same timezone as PHP for all queries, including NOW().

Step 3: Updated Working Code #

Here’s the full corrected code flow to ensure everything works as expected:

// Set PHP timezone
date_default_timezone_set('Asia/Kolkata');

// Establish PDO connection
$con = new PDO("mysql:host=localhost;dbname=your_database", "your_username", "your_password");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Sync MySQL session timezone
$con->exec("SET time_zone = 'Asia/Kolkata';");

// Generate tokenExpire (when creating the verification link)
$tokenExpire = (new DateTime('+10 minutes'))->format('Y-m-d H:i:s');
// Insert into database (example)
$insertSql = $con->prepare("INSERT INTO signup (Email, token, tokenExpire) VALUES (:email, :token, :expire)");
$insertSql->execute([
    ':email' => 'abcd@domain.com',
    ':token' => 'your_random_token',
    ':expire' => $tokenExpire
]);

// When verifying the link
$emailid = $_GET['email']; // Or your input method
$tokenum = $_GET['token'];

$sql = $con->prepare("SELECT `Email`,`token` FROM `signup` WHERE Email= :email AND token= :token AND tokenExpire > NOW()");
$sql->execute([ ':email' => $emailid, ':token' => $tokenum ]);

if ($sql->rowCount() > 0) { 
    echo "Link Is Valid"; 
} else { 
    echo "Link Expired"; 
}

Verification Tip #

After making these changes, test by generating a verification link, waiting 10 minutes, and then checking if it correctly shows "Link Expired". You can also verify the timestamps by running:

SELECT NOW(), tokenExpire FROM signup WHERE Email = 'abcd@domain.com';

Both timestamps should now be in the Asia/Kolkata timezone, so the comparison will work as intended.

内容的提问来源于stack exchange，提问作者John Cart