Solved

MySQL Load data infile - date format question.

Posted on 2013-05-21
11
843 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
  • 5
  • 4
  • 2
11 Comments
 
LVL 109

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 109

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

816 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now