无法将日期INSERT到MySQL TIMESTAMP类型字段导致错误的时区

Question

Environment: Wordpress, XAMPP Version: 7.2.10, PHP 7.2, Windows 10

I am calculating a user subscription expiration date (by adding 90 days to today's date) and storing it in a mysql database. Although the php calculation shows the date in the right timezone, the date stored in the MySQL table is incorrect by +5 hours. For example, "2019-10-29 14:55:01" should be "2019-10-29 9:55:01". (FYI - other timestamp fields that have an "on update CURRENT_TIMESTAMP" attribute are updating to the correct timezone. Also the Windows environment is set correctly.)

I have tried the following with no success:

• setting the default timezone in my php code.
• adding the following to my .htaccess: php_value date.timezone 'America/Chicago'
• php_value date.timezone 'America/Chicago'
• adding this to php.ini: date.timezone=America/Chicago

(Restarting Xampp after each change.)

My PHP Code:

$start_date = date("Y-m-d H:i:s");  //  Today's date in timestamp format.
$default_subscription_period = get_option('apr2s_user_subscription_period' );  // For now, 90 days
$user_subscription_expire_date = calculate_user_subscription_expire_date($start_date, $default_subscription_period);

echo "Start Date is  : ".$start_date."<br>";   //  Shows correct date & time in correct timezone
echo "Expire Date is : ".$user_subscription_expire_date."<br>";  //  Shows correct date & time in correct timezone

$results1 = $wpdb->insert($user_table_name, array(
"user_subscription_expire_date" => $user_subscription_expire_date,));

function calculate_user_subscription_expire_date($start_date, $subscription_period_in_days){
    date_default_timezone_set('America/Chicago');
    $expire_date = date('Y-m-d H:i:s', strtotime($start_date. ' + '.$subscription_period_in_days.'days'));
    return ($expire_date);
}

Ideas? Thanx!

Answer 1

You should store everything in UTC for simplicity. Perform all calculations in UTC too. Convert into the right timezone only when displaying to the user, and use DateTime() class to do so instead of date_default_timezone_set. It is best to keep your default timezone set to UTC in PHP and MySQL.

You could avoid making the date calculation in PHP altogether and use SQL functions.

INSERT INTO $user_table_name (user_subscription_expire_date) 
    VALUES (DATE_ADD(NOW(), INTERVAL 90 DAY))

or dynamically in wordpress:

$wpdb->query( $wpdb->prepare("INSERT INTO $user_table_name (user_subscription_expire_date) 
        VALUES (DATE_ADD(NOW(), INTERVAL %d DAY))", 
    $default_subscription_period
) );

When you want to display to the user just use:

$date = new \DateTime($dateFromDB);
$date->setTimezone(new \DateTimeZone('America/Chicago'));