php - 尽管格式正确,MySQL 字段 DATETIME 仍会截断
问题描述
我目前正在尝试将数据加载reloads.csv
到我的数据库中。但是 DATETIME 字段被截断。我注意得到正确的日期格式,如this answer。
当我加载数据时,我得到了这种错误:
连接成功 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,725. -01-01T23:56:45,2017-01-02T05:05:33 错误:插入重新加载(踏板车,用户,initialLoad,finalLoad,sourceX,sourceY,destinationX,destinationY,startTime,endTime)值('248', '129','2','4','4.37052','50.86135','4.36254','50.82652','2017-01-01 23:56:45','2017-01-02 05:05 :33 ') 键 'PRIMARY' 363,17,1,4,4.36902,50.87497,4.3785,50.87691,2017-01-01T22:00:52,2017-01-02T05 的重复条目 '2017-01-01': 08:34 错误:插入重新加载(踏板车、用户、initialLoad、finalLoad、sourceX、sourceY、destinationX、destinationY、startTime、endTime)值('363'、'17'、'1','4','4.36902','50.87497','4.3785','50.87691','2017-01-01 22:00:52','2017-01-02 05:08:34')重复键 'PRIMARY' 的条目 '2017-01-01' ...
reloads.csv 具有以下结构:
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
...
我这样创建了 Reloads 表:
$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)
)";
以下是负载如何重新加载到 Reloads 表中:
//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);
}
我不知道如何调试和解决问题。在我的数据库中,所有日期时间都以这种形式截断:2017-01-01
提前致谢
解决方案
根据您的数据,您可以使用substr
andstrpos
仅获取 Format 中的日期Y-m-d
$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'));
您可以DateTime
用于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');
推荐阅读
- entity-framework-core - Entity Framework Core NodaTime 按日期分组
- google-api - 无法使用 API 执行 google sheet 操作
- symfony - Symfony - ManyToMany - 替换而不是添加
- python - 使用 Pythonnet,如何在 C# 中处理输出?
- c++ - 将两个不同大小的二维数组相乘
- java - Maven 看不到 Spring 依赖项
- sql - Yii2,返回具有相同值的记录
- visual-studio-code - VSCode 跳转到当前行中间
- r - 如何在ggplot2中标记折线图的特定部分?
- mysql - SQL 将同一表中的每个字段的新行插入表中