如何在PHP中将MySQL的UTC时间转换为瑞典本地时间（不含毫秒）

Hey there! As a fellow PHP developer who's dealt with timezone conversions before, let me walk you through two simple, reliable ways to get that UTC timestamp with milliseconds converted to Swedish CET time (without the fractional seconds) for your forklift display project.

方法1：在MySQL查询中直接处理 #

This is often the most efficient approach since you can get the formatted time straight from the database, saving you extra PHP processing steps.

核心查询语句 #

Use CONVERT_TZ() to shift the UTC time to Sweden's timezone, and DATE_FORMAT() to strip off the milliseconds:

SELECT 
    DATE_FORMAT(
        CONVERT_TZ(your_timestamp_column, '+00:00', 'Europe/Stockholm'),
        '%Y-%m-%d %H:%i:%s'
    ) AS local_cet_time
FROM 
    your_license_plate_table;

关键说明 #

• CONVERT_TZ(utc_time, '+00:00', 'Europe/Stockholm'): Converts the UTC timestamp to Swedish time (this automatically handles daylight saving time changes, so you don't have to worry about switching between CET and CEST manually).
• DATE_FORMAT(..., '%Y-%m-%d %H:%i:%s'): Formats the result to your desired YYYY-MM-DD HH:MM:SS format, dropping the milliseconds entirely.

修复CONVERT_TZ返回NULL的问题 #

If you get NULL from the query, it means your MySQL server doesn't have timezone data installed. On Ubuntu 16.04, fix this by running this command in your terminal (enter your MySQL root password when prompted):

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

方法2：在PHP中处理查询结果 #

If you prefer to handle the conversion in your PHP code (maybe you need the raw UTC time for other purposes too), here's how to do it:

代码示例 #

// Assume this is the timestamp fetched from your MySQL database
$utcTimestamp = '2018-04-05 08:56:33.320000';

// 1. Create a DateTime object for the UTC timestamp (including milliseconds)
$date = DateTime::createFromFormat(
    'Y-m-d H:i:s.u',  // Matches the format of your stored timestamp
    $utcTimestamp,
    new DateTimeZone('UTC')
);

// 2. Switch the timezone to Swedish CET/CEST
$date->setTimezone(new DateTimeZone('Europe/Stockholm'));

// 3. Format the date to remove milliseconds
$localCetTime = $date->format('Y-m-d H:i:s');

// Output: 2018-04-05 10:56:33
echo $localCetTime;

关键说明 #

• createFromFormat('Y-m-d H:i:s.u', ...): The u placeholder tells PHP to parse the milliseconds part of the timestamp.
• setTimezone(new DateTimeZone('Europe/Stockholm')): Adjusts the time to Sweden's local timezone, handling DST automatically.
• format('Y-m-d H:i:s'): Outputs the time in your desired format, excluding milliseconds.

Both methods should work perfectly on your Ubuntu 16.04.1 setup. I'd recommend starting with the MySQL approach since it's more efficient, but the PHP method gives you more flexibility if you need to manipulate the time further in your code.

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