php - SQL varchar 转日期时间

Question

我看到这个问题被问了很多次，但我有一些额外的因素，我没有看到答案考虑在内。

这是场景。我在表中存储了一些日期(目前为 varchar)。是时候将这些日期转换为日期时间并正确存储所有 future 的日期了。我从 newsapi.org 获取文章(标题、描述和日期，格式为 2017-05-03T00:48:09Z)

两个问题:我可以执行什么sql代码来将这一列字符串转换为日期时间？

编辑:我尝试了这个，但出现错误。我什至编辑了我的 php.ini 文件

MariaDB [default]> UPDATE News SET ts=CAST(publishedAt AS TIMESTAMP);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TIMESTAMP)' at line 1

什么样的 php 将允许我获取这样的字符串并将其正确地放入“insert into”语句中？

编辑当尝试运行 Ahmed Ginani 答案时，我收到此错误

Fatal error: Uncaught exception 'Exception' with message 'DateTime::__construct(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone.' in /var/www/html/news/news.php:32 Stack trace: #0 /var/www/html/news/news.php(32): DateTime->__construct('2017-05-10T18:0...') #1 {main} thrown in /var/www/html/news/news.php on line 32

使用

foreach ($obj->articles as &$article)
{
        $title = htmlspecialchars($article->title, ENT_QUOTES);
        $author = htmlspecialchars($article->author, ENT_QUOTES);
        $description = htmlspecialchars($article->description, ENT_QUOTES);
        $url = htmlspecialchars($article->url, ENT_QUOTES);
        $urlToImage = htmlspecialchars($article->urlToImage, ENT_QUOTES);
        $date = (new DateTime($article->publishedAt))->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s');


        $sql = "INSERT IGNORE INTO News (author, title, description, url, urlToImage, ts) VALUES ('" . $title . "', '" . $author . "', '" . $description . "', '" . $url . "', '" . $urlToImage . "', $date)";

        if($conn->query($sql) === TRUE)
        {
                echo "New Record Successfuly Created <br>";
        }
        else
        {
                echo "Error: " . $conn->error;
                echo "<br><br>";
        }
}

$conn->close();

Answer 1

这行不通吗？

select CAST('2017-05-03T00:48:09Z' AS TIMESTAMP);

适用于我所知道的所有符合 ISO/ANSI 标准的数据库...

显然，MariaDB 无法将 CAST() 转换为 TIMESTAMP 类型...然后，使用此处找到的文档:https://mariadb.com/kb/en/mariadb/str_to_date/ ，试试这个:

SELECT STR_TO_DATE(publishedAt,'%Y-%m-%dT%H:%i:%sZ');

在这里，我假设以下格式:2017-05-03T23:48:09Z 有效:24 小时时间描述。