Inserting Datetime into MySQL: Strange behaviour

[msimonds]

okay let me try and explain this to see if anyone can give me some help:

I have this function:

function store_in_db($records,$db)
{
    $record_count = count($records);
    $rows_loaded = 0;
    foreach ($records as $r)
    {
        $pass_this['id'] = $r->Id;
        foreach ($r->fields as $key => $value)
        {
            $pass_this[$key] = addslashes($r->fields->$key);
        }

    $fields = implode(",",array_keys($pass_this));
    $values = implode("','",array_values($pass_this));

    $query = "INSERT INTO sforce_lead (".$fields.") VALUES ('".$values."')";


    if ($db->Execute($query))
    {
        $rows_loaded++;
    }
    else
    {
        echo $db->ErrorMsg()."<br />";
        return false;
    }
}
return $rows_loaded;
}

the data being inserted looks like this:

Array
(
    [id] => 00Q4000000HBX6zEAH
    [CreatedDate] => 2008-05-16T20:05:38.000Z
    [CreatedById] => 00540000000wvEyAAI
    [LastModifiedDate] => 2008-10-29T19:15:21.000Z
    [LastModifiedById] => 00540000000wvEyAAI
    [SystemModstamp] => 2008-10-29T19:15:21.000Z
    [LastActivityDate] => 
    [SICCode__c] => 2768
    [ProductInterest__c] => GC5000 series
    [Primary__c] => Yes
    [CurrentGenerators__c] => All
    [NumberofLocations__c] => 130.0
)

the problem is when I run the script and insert all the fields, it works fine

BUT

when my friend runs the exact same script he is getting errors on the date fields:

[CreatedDate] => 2008-05-16T20:05:38.000Z

mine inserts perfectly and I am NOT modifying the data at all

When he does it he gets an error:
Incorrect datetime value: '2008-05-16T20:05:38.000Z' for column 'CreatedDate' at row 1

my tests:

On my localhost I am using XAMPP: PHP 5.2.5 and MySQL 5.0.45

On external server: CentOS, PHP 5.2.5 and MySQL 4.1.22-standard-log.

same schema on both servers, he is also using the same schema

My CreatedDate field is populated with : 2008-05-16 20:05:38

So for some reason it is accepting mine and not his, without formatting it at all

Is there something that I am doing wrong or he is doing wrong? is there a setting in MySQL that is different that I do not know about. He is using MySQL 5.0.51

any help would be appreciated

Thanks,
Mike

[igorlopez]

For the moment no access to my server so I can't test but from the little I know,

Incorrect datetime value: '2008-05-16T20:05:38.000Z' for column 'CreatedDate' at row 1

is what I would suspect since it should look like:

[CreatedDate] => 2008-05-16 20:05:38

I don't know why you need the T and Z in the field to begin with (and the microsecond part with 6 trailing numbers is only allowed in some context)

[CreatedDate] => 2008-05-16 20:05:38.000000

[Weedpacket]

igorlopez wrote:

I don't know why you need the T and Z in the field to begin with

That's ISO standard format, "T" separates the date from the time and "Z" specifies the timezone.

I'd've thought that even MySQL would be capable of parsing ISO-formatted date/times, but what I read in its manual says otherwise. Stupid MySQL.
I guess you'll have to reformat that field:

$pass_this['CreatedDate'] = date('Y-m-d H:i:s', strtotime($pass_this['CreatedDate']));

and log this as a bug on MySQL's bug tracker.