Solved

MySQL Load data infile - date format question.

Posted on 2013-05-21
11
856 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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