?
Solved

MySQL Load data infile - date format question.

Posted on 2013-05-21
11
Medium Priority
?
874 Views
Last Modified: 2013-05-23
I have a txt file im trying to load into my database and the PhotoTimestamp field is formatted like: 2011-05-12T16:10:23
which causes an error when trying to load. Data truncation: Incorrect datetime value: '' for column 'PhotoTimeStamp'

I'm assumming that it's the T? causing the error, how can get around this? Thanks!



set PhotoTimeStamp = str_to_date(@PhotoTimeStamp,'%Y-%m-%d %k:%i:%s');
0
Comment
Question by:Bang-O-Matic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 total points
ID: 39185983
Try omitting the str_to_date() function call and just use the unalloyed string.  It looks like a well-formed ISO-8601 DATETIME string, so it ought to work correctly as-is.  Put single quotes around it when you build the query string.  Your column definition should be DATETIME.

In case you're using PHP this article may be helpful.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39185984
You need to use named columns and a SET command on the end of your LOAD DATA INFILE command.    I'm working up an example for you.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39185987
Wait... Maybe you're already doing that and getting some other error.

Can you post the command you're using now?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 23

Expert Comment

by:nemws1
ID: 39185992
You can put the 'T' right into your date conversion.  Since it doesn't have a '%' sign in front of it, it'll be treated as a literal (just like the space you tried to use):
set PhotoTimeStamp = str_to_date(@PhotoTimeStamp,'%Y-%m-%dT%k:%i:%s'); 

Open in new window

0
 

Author Comment

by:Bang-O-Matic
ID: 39186035
@nemws1
"You can put the 'T' right into your date conversion.  Since it doesn't have a '%' sign in front of it, it'll be treated as a literal (just like the space you tried to use):"

I had already tried that and it just threw a syntax error.
0
 

Author Comment

by:Bang-O-Matic
ID: 39186039
I should have included that I'm using Coldfusion 9. MySql 5.5
0
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 1000 total points
ID: 39186071
weird.  Using the command line tool, this:
SELECT STR_TO_DATE('2011-05-12T16:10:23', '%Y-%m-%d %k:%i:%s'); 

Open in new window

Returns NULL.  But this (note 'T' instead of space):
SELECT STR_TO_DATE('2011-05-12T16:10:23', '%Y-%m-%dT%k:%i:%s');

Open in new window

Returns '2011-05-12 16:10:23' - as I would expect.

Really, it's just the 'T' that's causing the problem.  Have you tried this instead (change the 'T' to a space):
set PhotoTimeStamp = REPLACE(@PhotoTimeStamp, 'T', ' '); 

Open in new window

0
 

Author Comment

by:Bang-O-Matic
ID: 39186096
I haven't tried replacing the T but I bet that will work. It's going to be tomorrow before I get a chance to try it, I will post back and let you know. Thanks!
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39186159
No need to make this more complicated.  This tests out correctly.  The test data is near line 30. Add your credentials near line 37 and run it to see how it works.
http://www.laprbass.com/RAY_temp_bangomatic.php

<?php // RAY_temp_bangomatic.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THIS SCRIPT DEMONSTRATES MANY OF THE BASICS OF MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_ddates_arrays = array
( array( "ddate" => "2011-05-12T16:10:23 " )
, array( "ddate" => date('c')   )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, ddate DATETIME    NOT NULL DEFAULT '0000-00-00'
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_ddates_arrays as $ddate)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_dd  = $mysqli->real_escape_string($ddate['ddate']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( ddate ) VALUES ( '$safe_dd' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = "QUERY FAIL: "
        . $sql
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_dd</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT id, ddate FROM my_table ORDER BY ddate DESC";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

Open in new window

0
 
LVL 23

Expert Comment

by:nemws1
ID: 39191929
Bang-O-Matic - any updates/luck?
0
 

Author Comment

by:Bang-O-Matic
ID: 39192080
First let me say thank you both for the help!
Even though both of your solutions should have worked, it still didn't so I just skipped over that field. I'm going to split the points between you guys. If I come back to this issue again I will just ask another question after doing some additional testing.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question