CODEWITHSUNDEEP
phpmysqlsqldatetime
sfxworks
sfxworks

Reputation: 1091

SQL varchar to datetime

I see this question being asked many times but I have some extra factors that I don't see answers take account for.

Here's the scenario. I have some dates (in varchar at the moment) that I have stored in my table. It has come time to convert these dates into datetimes and have all future dates stored properly. From newsapi.org, I get articles (titles, descriptions, and dates in this format 2017-05-03T00:48:09Z)

Two questions: What sql code can I execute to convert this column of strings into a datetime?

Edit: I tried this but I got an error. I even edited my php.ini file

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

What sort of php will allow me to take such a string and properly put it into an "insert into" statement?

Edit When trying to run Ahmed Ginani answer I get this error

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

using

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();

Upvotes: 0

Views: 313

Answers (2)

marcothesane
marcothesane

Reputation: 6721

Would this not work?

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

Works on all ISO / ANSI compliant databases I know ...

Apparently, MariaDB can't CAST() to a TIMESTAMP type ... Then, using the docu found here: https://mariadb.com/kb/en/mariadb/str_to_date/ , try this:

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

Here, I'm assuming that a format of: 2017-05-03T23:48:09Z is valid: 24-hour time depiction.

Upvotes: 1

Ahmed Ginani
Ahmed Ginani

Reputation: 6650

Try below code
Note the 'Z' at the end which means Zulu (a.k.a UTC) so it might be worth converting your date and time to UTC as follows: 

<?php
$date = (new DateTime('2017-05-03T00:48:09Z'))->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s');
?>

Upvotes: 1

Related Questions