Solved

MySQL Load data infile - date format question.

Posted on 2013-05-21
11
861 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 110

Accepted Solution

by:
Ray Paseur earned 250 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 250 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 110

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

729 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