PHP处理用户时区变更数据及跨时区CRON定时通知实现方案

Great questions—let's break these down one by one with practical PHP solutions:

问题1：如何使用PHP处理用户时区变更时数据库中存储的用户数据？ #

The key here is to separate your stored data from user-specific timezone display/processing. Here's how to handle it properly:

1. Adopt a UTC-first storage strategy (best practice) #

Always store dates/times in UTC—either as Unix timestamps (integers) or timezone-aware datetime values (like MySQL's TIMESTAMP type, which automatically converts to/from UTC). This way, your core data is timezone-agnostic, and you only apply user timezone conversions when displaying or acting on the data.

Example code for storing and retrieving:

// Storing data: Save UTC timestamp or UTC datetime
$utcTimestamp = time(); // Unix timestamp (UTC by default)
// OR create a UTC datetime string
$utcDatetime = (new DateTime('now', new DateTimeZone('UTC')))->format('Y-m-d H:i:s');

// When retrieving for a user with a new timezone:
$userTimezone = new DateTimeZone('America/Los_Angeles'); // User's updated timezone
$userDateTime = (new DateTime())->setTimestamp($utcTimestamp)->setTimezone($userTimezone);
echo $userDateTime->format('Y-m-d H:i:s'); // Displays time in user's current timezone

2. Fixing existing local-time stored data #

If you previously stored user local times instead of UTC, you'll need to migrate that data first:

// Example: Convert old local-time data to UTC
$oldTimezone = new DateTimeZone('Asia/Kolkata'); // User's original timezone
$storedLocalTime = '2024-05-20 08:00:00'; // Data stored in old local timezone

// Convert to UTC
$utcDateTime = DateTime::createFromFormat('Y-m-d H:i:s', $storedLocalTime, $oldTimezone)
    ->setTimezone(new DateTimeZone('UTC'));

// Update database with UTC value
// $db->query("UPDATE users SET event_time = '{$utcDateTime->format('Y-m-d H:i:s')}' WHERE id = {$userId}");

Once your data is in UTC, timezone changes only affect how you present/process it—no need to modify the database every time a user switches timezones.

问题2：跨时区的固定本地时间提醒（如每日上午8点） #

Your original approach of storing a fixed UTC time breaks when the user switches timezones. Instead, you need to store the user's desired local time of day and their current timezone, then dynamically calculate the UTC trigger time on the fly.

Step 1: Adjust your database schema #

Store two values for each user's reminder:

• reminder_local_time: A string like 08:00 (the local time they want to be notified)
• user_timezone: A PHP-supported timezone identifier (e.g., Asia/Kolkata, Europe/London—avoid raw offsets like +5:30 because of daylight saving time)

Step 2: Build the CRON-triggered notification script #

Your UTC-running CRON job will check each user's current local time against their desired reminder time. Here's a simplified example:

// CRON script runs in UTC timezone
date_default_timezone_set('UTC');

// Fetch users with active reminders (pseudo-code)
$users = $db->query("SELECT id, reminder_local_time, user_timezone FROM users WHERE reminder_enabled = 1")->fetchAll();

foreach ($users as $user) {
    // Get current UTC time
    $utcNow = new DateTime('now');
    
    // Convert to user's timezone
    $userTimezone = new DateTimeZone($user['user_timezone']);
    $userNow = $utcNow->setTimezone($userTimezone);
    
    // Create a datetime for today's reminder time in the user's timezone
    $reminderToday = clone $userNow;
    [$reminderHour, $reminderMinute] = explode(':', $user['reminder_local_time']);
    $reminderToday->setTime((int)$reminderHour, (int)$reminderMinute, 0);
    
    // Convert reminder time back to UTC to compare with current time
    $reminderUtc = $reminderToday->setTimezone(new DateTimeZone('UTC'));
    
    // Check if we're within a 1-minute window of the reminder (adjust as needed for CRON frequency)
    $timeDiff = $utcNow->getTimestamp() - $reminderUtc->getTimestamp();
    if ($timeDiff >= 0 && $timeDiff < 60) {
        // Send notification to user
        sendReminderNotification($user['id']);
    }
}

Key Notes: #

• Use timezone identifiers (not raw offsets) to handle daylight saving time automatically—PHP's DateTimeZone class takes care of DST shifts.
• Adjust the CRON frequency based on your needs: running every minute ensures you don't miss reminders, but you can optimize to run hourly if minute precision isn't critical.
• If users update their timezone, you don't need to modify their reminder_local_time—the script will automatically calculate the new UTC trigger time.

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