CODEWITHSUNDEEP
phpmysqldatetimexampp
Dixel
Dixel

Reputation: 564

MySQL field DATETIME truncates despite correct format

I'm currently trying to load data from reloads.csv into my database. But the DATETIME fields gets truncated. I took care to get the correct date format as in this answer.

When I load the data, I get this kind of error:

Connected successfully 537,496,3,4,4.36186,50.86034,4.372,50.86309,2017-01-01T22:05:55,2017-01-02T05:02:20 248,129,2,4,4.37052,50.86135,4.36254,50.82652,2017-01-01T23:56:45,2017-01-02T05:05:33 Error: INSERT INTO Reloads (scooter, user, initialLoad, finalLoad, sourceX, sourceY, destinationX, destinationY, startTime, endTime) VALUES ('248', '129','2', '4', '4.37052', '50.86135', '4.36254','50.82652', '2017-01-01 23:56:45', '2017-01-02 05:05:33 ') Duplicate entry '2017-01-01' for key 'PRIMARY' 363,17,1,4,4.36902,50.87497,4.3785,50.87691,2017-01-01T22:00:52,2017-01-02T05:08:34 Error: INSERT INTO Reloads (scooter, user, initialLoad, finalLoad, sourceX, sourceY, destinationX, destinationY, startTime, endTime) VALUES ('363', '17','1', '4', '4.36902', '50.87497', '4.3785','50.87691', '2017-01-01 22:00:52', '2017-01-02 05:08:34 ') Duplicate entry '2017-01-01' for key 'PRIMARY' ...

reloads.csv has the following structure:

scooter, user, initialLoad, finalLoad, sourceX, sourceY, destinationX, 
destinationY, startTime, endTime
537,496,3,4,4.36186,50.86034,4.372,50.86309,2017-01-01T22:05:55,2017-01-02T05:02:20
248,129,2,4,4.37052,50.86135,4.36254,50.82652,2017-01-01T23:56:45,2017-01-02T05:05:33
363,17,1,4,4.36902,50.87497,4.3785,50.87691,2017-01-01T22:00:52,2017-01-02T05:08:34
...

I created the Reloads table like this:

$sql = "CREATE TABLE Reloads (
        scooter INT NOT NULL,
        user INT NOT NULL,
        initialLoad INT,
        finalLoad INT,
        sourceX FLOAT,
        sourceY FLOAT,
        destinationX FLOAT,
        destinationY FLOAT,
        startTime DATETIME NOT NULL,
        endTime DATETIME,
        PRIMARY KEY (startTime),
        FOREIGN KEY (scooter) REFERENCES Scooters(numero),
        FOREIGN KEY (user) REFERENCES Rechargeurs(ID)
)";

And here is how a load reloads into the Reloads table:

//INSERT RELOADS IN DB
if ($fh = fopen("../../data2019/reloads.csv", 'r')) {
  $line = fgets($fh);//remove first line
  $sql = "";
  while (!feof($fh)) {
    $line = fgets($fh);
    echo "<br>" . $line;
    //echo $line;
    $str_arr = explode(",", $line);
    //print_r($str_arr);
    if (count($str_arr) == 10) {
      $startTime = str_replace("T", " ",$str_arr[8]);
      $endTime = str_replace("T", " ",$str_arr[9]);
      $sql = "INSERT INTO Reloads (scooter, user, initialLoad, finalLoad, sourceX, sourceY, destinationX, destinationY,
startTime, endTime)
      VALUES (
              '$str_arr[0]',
              '$str_arr[1]',
              '$str_arr[2]',
              '$str_arr[3]',
              '$str_arr[4]',
              '$str_arr[5]',
              '$str_arr[6]',
              '$str_arr[7]',
              '$startTime',
              '$endTime'
      )";
      if ($conn->query($sql) === TRUE) {
        //echo "\nNew record created successfully";
      } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
      }
    }
  }
  fclose($fh);
}

I have no more idea of how to debug and resolve the problem. All datetimes are truncated in this form in my database: 2017-01-01

Thanks in advance

Upvotes: 0

Views: 368

Answers (1)

Rakesh Jakhar
Rakesh Jakhar

Reputation: 6388

You can use substr and strpos to only get the date in Format Y-m-d, based on your data

$startTime = '2017-01-01T22:05:55';
$endTime   = '2017-01-02T05:08:34';
$startTime = substr($startTime,0,strpos($startTime, 'T'));
$endTime   = substr($endTime,0,strpos($endTime, 'T'));

You can use DateTime for Y-m-d H:i:s

$startTime  = '2017-01-01T22:05:55';
$dateObject = new DateTime($startTime);
$startTime  = $dateObject->format('Y-m-d H:i:s');

$endTime   = '2017-01-02T05:08:34';
$dateObject = new DateTime($endTime);
$endTime  = $dateObject->format('Y-m-d H:i:s');

Upvotes: 1

Related Questions