对于下面的示例,表名将为matches,列将为match_time,使用您提供的完整数据集23:30, 5:30, 11:30, 17:30。
如果您的问题中的数据是文字数据,请让我相信您正在使用VARCHAR数据类型。您需要将列更改为适当的TIME数据类型,以便选择时间范围。这可能还需要您更新应用程序代码以适应数据库中新的HH:MM:SS格式。
原因是数字字符串的排序或索引方式与DATE,TIME或INTEGER数据类型不同,导致在数字字符串上的行为异常。EG:WHERE match_time BETWEEN '5:00' AND '10:00'无法检索5:30
MySQL TIME数据类型转换# Update string times to appropriate TIME format
UPDATE matches
SET match_time = TIME(STR_TO_DATE(match_time, '%k:%i'));
# Change the data-type
ALTER TABLE matches MODIFY match_time TIME;
否则,您将需要类型转换WHERE子句,这将导致全表扫描,这通常是不希望的,并且随着表大小的增加其性能也很差。
MySQL TIME类型转换(替代)SELECT TIME(STR_TO_DATE(match_time, '%k:%i'))
FROM matches
WHERE TIME(STR_TO_DATE(match_time, '%k:%i')) BETWEEN TIME(:start_date) AND TIME(:end_date)
接下来,由于时间以UTC的形式存储在数据库中,因此您需要将IST中的搜索时间转换为UTC。
PHP TimeZone转换$tzIST = new DateTimeZone('Asia/Kolkata');
$tzUTC = new DateTimeZone('UTC');
$start = 1;
$end = 11;
//create times in IST
$dateStartIST = (new DateTimeImmutable('today', $tzIST))->setTime($start, 0);
$dateEndIST = $dateStartIST->setTime($end, 0);
//convert times to UTC
$dateStartUTC = $dateStartIST->setTimezone($tzUTC);
$dateEndUTC = $dateEndIST->setTimezone($tzUTC);
//ensure the dates are the same to prevent Timezone incremental day rollover.
$dateStartUTC = $dateStartUTC->setDate(1980, 1, 1);
$dateEndUTC = $dateEndUTC->setDate(1980, 1, 1);
//output times
printf('%s - %s (%s - %s UTC)',
$dateStartIST->format('H:i'),
$dateEndIST->format('H:i'),
$dateStartUTC->format('H:i'),
$dateEndUTC->format('H:i')
);
结果01:00 - 11:00 (19:30 - 05:30 UTC)
如您所见,检索到19:30 - 5:30 UTC,但是由于19:30晚于05:30,因此它将阻止您查询match_time BETWEEN :start_date AND :end_date。因此,您需要通过根据需要切换开始和结束来补偿方差。if ($dateStartUTC > $dateEndUTC) {
//flip the dates as needed
$dateRange = [
'start_date' => $dateEndUTC->format('H:i'),
'end_date' => $dateStartUTC->format('H:i')
];
} else {
$dateRange = [
'start_date' => $dateStartUTC->format('H:i'),
'end_date' => $dateEndUTC->format('H:i')
];
}
现在match_time列为TIME数据类型,并且日期以正确的顺序,您现在可以在数据库中查询适当的时间。 (可选)在参数周围使用TIME()功能,以正确设置搜索时间的格式。
MySQL参数化查询
假设您在数据库表上执行了TIME数据类型转换。有关参数化查询的更多详细信息,请参见PHP手册中的PDO和mysqli$sql = 'SELECT match_time
FROM matches
WHERE match_time BETWEEN TIME(:start_date) AND TIME(:end_date)';
前端显示
然后显示从数据库中检索到的AS IST时间,将其从UTC转换为IST。
将比赛时间从UTC转换为ISTforeach ($matches as $match) {
//ensure the format is converted from HH:MM:SS as UTC
$dateMatchTimeUTC = DateTimeImmutable::createFromFormat('|H:i:s', $match['match_time'], $tzUTC);
if ($dateMatchTimeUTC) {
//change display Timezone to IST
$dateMatchTimeIST = $dateMatchTimeUTC->setTimeZone($tzIST);
printf("%s (%s UTC)\n", $dateMatchTimeIST->format('H:i'), $match['match_time']);
}
}
由于您没有指定使用的是PDO还是mysqli,因此我省略了查询的执行,但是提供了一个使用PDO的有效示例。
我也将范围调整为仅11 - 18 (05:30 - 12:30 UTC),因为1 - 11 (05:30 - 19:30 UTC)会在没有适当断言的情况下检索所有记录。11:00 - 18:00 (05:30 - 12:30 UTC)
11:00 (05:30:00 UTC)
17:00 (11:30:00 UTC)