Search code examples
phpmysqlsqldatetimedata-conversion

String to datetime with CET/CEST


I am having some difficulties converting a string to datetime in PHP.

The string can end with both CEST and CET as timezone, so I guess I have to somehow split before that? I am not looking to store the CET or CEST. I only want the date converted to a datetime.

This is the strings:

Dec 27 2017, 22:46:15 CET and Dec 27 2017, 22:46:15 CEST

But also, the spaces in the strings are   and not regular spaces. So in HTML it looks like this:

Dec 27 2017, 22:46:15 CET and Dec 27 2017, 22:46:15 CEST

I want it converted in to: 2017-12-27 22:46:15 and then insert that to my database. I figured out how to change this in an SQL query (my column is called lastlogin) and I do that by doing this:

STR_TO_DATE(SUBSTRING_INDEX(REPLACE(lastlogin,char(160),' '),'C',1), '%b %d %Y, %T')

But I'd really like to do this in PHP. So let's say my variable with the date is called $lastlogin, how would I apply that SQL logic to my PHP? the char(160) in SQL is the  , I am not sure about PHP.

Edit: I am storing it in $character->lastlogin variable. (note: apparently it is getting   instead of  )

$character->lastlogin = str_replace(' ', ' ', $character->lastlogin);
$datetime = DateTime::createFromFormat('M d Y, H:i:s e', $character->lastlogin);
$character->lastlogin = $datetime->format('Y-m-d H:i:s');

Gives me this error: Fatal error: Uncaught Error: Call to a member function format() on boolean


Solution

  • You should use DateTime::createFromFormat

    // CET Timezone (+01:00)
    $datetime = DateTime::createFromFormat('M d Y, H:i:s e', 'Dec 27 2017, 22:46:15 CET');
    
    // change timezone to UTC (+00:00) (if necessary)
    $datetime->setTimeZone(new DateTimeZone('UTC'));
    
    $output = $datetime->format('Y-m-d H:i:s');
    // output: 2017-12-27 21:46:15