Solved

MySQL Load data infile - date format question.

Posted on 2013-05-21
11
836 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 108

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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 108

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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