JSON解析后ISO8601时间转SQL DATETIME字段问题求助

Hey Stefan, I spot the immediate issue causing that 1970 epoch timestamp error, plus a few tweaks to get your date conversion working correctly for SQL!

First: The Variable Name Typo #

PHP is case-sensitive, and in your test code, you defined $TimeStamp (capital S) but tried to use $Timestamp (lowercase s) in strtotime(). That means you're passing an undefined variable to strtotime(), which returns false—and when date() gets false, it defaults to the Unix epoch (1970-01-01). Fixing that case mismatch will already solve most of your problem.

Correct Conversion Methods #

Once you fix the variable name, here are reliable ways to convert your ISO8601 string to a SQL-compatible DATETIME format:

1. Using strtotime() (Simple Approach)

strtotime() natively supports ISO8601 formats (including the T separator and milliseconds), so you don't need to replace anything first. Just use the standard SQL DATETIME format (YYYY-MM-DD HH:MM:SS):

$TimeStamp = $fgc['result'][$i]["TimeStamp"];
$formattedDate = date('Y-m-d H:i:s', strtotime($TimeStamp));
echo "convert:" . $formattedDate . '<br />';

For your source time 2018-01-06T11:48:40.207, this will output 2018-01-06 11:48:40—perfect for inserting into a SQL DATETIME field.

2. Using DateTime Class (More Robust)

The DateTime class is more flexible, especially if you need to handle timezones or edge cases:

$TimeStamp = $fgc['result'][$i]["TimeStamp"];
$dateObj = new DateTime($TimeStamp);
// Format to SQL DATETIME standard
$formattedDate = $dateObj->format('Y-m-d H:i:s');

If you need to adjust timezones (e.g., convert UTC to your local timezone):

$dateObj = new DateTime($TimeStamp, new DateTimeZone('UTC'));
$dateObj->setTimezone(new DateTimeZone('Europe/Berlin')); // Replace with your timezone
$formattedDate = $dateObj->format('Y-m-d H:i:s');

Quick Notes About SQL DATETIME #

• Most SQL databases (like MySQL, PostgreSQL) expect the YYYY-MM-DD HH:MM:SS format, so stick to Y-m-d H:i:s instead of y.m.d (lowercase y gives a 2-digit year, which is less reliable).
• You don't need to strip milliseconds—both strtotime() and DateTime will ignore them automatically when formatting to H:i:s.

Give these fixes a try, and your timestamp should now insert into the database without issues!

内容的提问来源于stack exchange，提问作者Stefan S.