Convert string to MySQL date

Question

I need to be able to convert a string (see example below) to a format that can be added to MySQL. I could just add it as a var car but I need to be able to run queries based on these date ranges.

e.g. '02 May 2013 12:08 AM GMT'

Any suggestion of the best way to achieve this?

EDIT Currently trying the following:

$date = date_create_from_format('d M Y H:i A e', '02 May 2013 12:08 AM GMT');
echo $date;

but getting a server error when I trying echoing the date.

I've also tried the following to break the date and timezone apart and try to deal with it separately:

$myvalue = '02 May 2013 12:08 AM GMT';
$arr = explode(' ',trim($myvalue));
$timezone = new DateTimeZone($arr[5]);
$arr[count($arr)-1]='';
$time=implode(' ',$arr);
$date = date_create_from_format('d M Y h:i A', $time, $timezone);
echo $date;

but again I'm getting a server error.

EDIT I just realized that the second chunk of code might be going wrong due to the GMT as it doesn't appear to be a usable format with this function.

EDIT After further investigation I think the best way to store the data is to have all dates stored as a DATETIMEin MySQL with the same timezone (gmt) and along with it storing the actual timezone they are and using the timezone when running queries if needed.

$myvalue = '02 May 2013 12:08 AM GMT';
$arr = explode(' ',trim($myvalue));
$timezone = new DateTimeZone($arr[5]);
$arr[count($arr)-1]='';
$time=implode(' ',$arr);
$timestamp = strtotime($time);
$date = date("Y-m-d H:i:s", $timestamp);
echo $date;

For everything but the timezone: http://stackoverflow.com/questions/10539154/insert-into-db-datetime-string For the timezone: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz So you will have to split the string by timezone and not timezone first. Or if timezone never varies, you can just ignore it. — Patashu, May 05 '13 at 00:54
Which errors do you get? `A server error` does not help anyone. The exact error message is much more useful. — Arjan, May 05 '13 at 10:44

Smuuf · Answer 1 · 2014-07-20T17:37:17.860

1

Why don't you just do this, then?

function convertToMysqlTime($string){
    $seconds = strtotime($string);
    if (!$seconds) return false;
    return date("Y-m-d H:i:s", $seconds);
}

echo convertToMysqlTime('02 May 2013 12:08 AM GMT');

It accounts for that GMT part, too.

edited Jul 20 '14 at 17:37

answered May 05 '13 at 11:33

Smuuf

6,339
3
23
35

There is an error in this function. You are performing a double conversion of strtotime. Your function should end with "return date("Y-m-d H:i:s",$seconds);" – BrochanGuMor Jul 18 '14 at 14:28
Whoa. I don't know _how_ that happened! :) Fixed. – Smuuf Jul 20 '14 at 17:37

bvarga · Answer 2 · 2013-05-05T18:08:36.703

0

Use the DateTime class createFromFormat function. If your PHP version <= 5.3. Here's the documentation: http://www.php.net/manual/en/datetime.createfromformat.php

Edit:

$date = DateTime::createFromFormat('d M Y H:i A e','02 May 2013 12:08 AM GMT');
echo $date->getTimeZone()->getName(); //return 'UTC'
echo $date->format('Y-m-d H:i:s'); // return 2013-05-02 00:08

Set the session timezone in mysql:

edited May 05 '13 at 18:08

answered May 05 '13 at 01:18

bvarga

716
6
12

this seems to be the ticket. the only thing i've realised is that the timezone will matter so I will need to adjust the time to GMT time depending on the timezone. Is this possible without a messy if/else or switch? – Alan Doolan May 05 '13 at 08:33
I do not quite understand what you mean. Your format is: d M Y H:i A e Your timezone is UTC (e). – bvarga May 05 '13 at 09:06
$date = DateTime::createFromFormat('d M Y H:i A e','02 May 2013 12:08 AM GMT'); echo $date->getTimeZone()->getName(); //return 'UTC' echo $date->format('Y-m-d H:i:s'); // return 2013-05-02 00:08 Set the session timezone in mysql: http://www.sitepoint.com/synchronize-php-mysql-timezone-configuration/ – bvarga May 05 '13 at 18:04

score 0 · Answer 3 · answered May 05 '13 at 01:29

I hope you want to convert date obtained from javascript "Date()" function. consider this function in javascript part:

function ISODateString(d) // to get date in format that MySQL accepts
{
  function pad(n){return n<10 ? '0'+n : n}
  return d.getUTCFullYear()+'-'
      + pad(d.getUTCMonth()+1)+'-'
      + pad(d.getUTCDate()) +' '
      + pad(d.getUTCHours())+':'
      + pad(d.getUTCMinutes())+':'
      + pad(d.getUTCSeconds())
}

after this use:

var d=ISODateString(new Date())

now to send it either use post method or GET with url encoding to utf-8

score 0 · Answer 4 · answered May 05 '13 at 10:51

$date = date_create_from_format('d M Y H:i A e', '02 May 2013 12:08 AM GMT');
echo $date;

This code will always produce error messages, for different reasons.

If the PHP version is < 5.3 then the code will fail, because date_create_from_format() does not exist.

If the PHP version is >= 5.3 then the second line will fail, because $date is an instance of DateTime and that class does not have a __toString() method. Use DateTime::format() instead.

score -1 · Answer 5 · answered May 05 '13 at 01:04

-1

You can use strtotime() to convert a textual representation of a time (such as your example) to a Unix timestamp:

$timestamp = strtotime($string);

Then you can store this value in MySQL's TIMESTAMP field type.

answered May 05 '13 at 01:04

VettelS

1,204
1
9
17

Convert string to MySQL date

5 Answers5