选择仅在今天和未来 2 天之间的结果 [英] Select results that are only between today and 2 days in the future

问题描述

我对处理日期感到很困惑.如何编写此查询以使其仅选择今天和未来 2 天之间的结果?

I'm so confused with working with dates. How can I write this query so it only selects results that are between today and 2 days in the future?

日期存储在名为 EstimatedTime 的列中(具有文本"类型...)，如下所示 201502181150

The date is stored in a column named EstimatedTime (with a "text" type...) like this 201502181150

<?php 

$stmt = $db->query('SELECT * FROM data WHERE Status = "D" ORDER BY id DESC');

/*
$Date = 201502181150;
$time_ahead = date('M d', strtotime($Date. ' + 2 days'));
// The above returns Feb 20, but how can I do this on MySQL?
*/

while($row = $stmt->fetch()) {
    echo "<tr>";
    echo '<td>' . date('M d', strtotime($row['EstimatedTime'])) . '</td>';
    echo "</tr>";
}

?>

任何帮助将不胜感激！非常感谢.

Any help would be appreciated! Thank you so much.

推荐答案

如果 EstimatedTime 列被声明为数据类型 DATETIME 或 DATE，则很简单:

If EstimatedTime column is declared as datatype DATETIME or DATE, then it's straightforward:

WHERE t.EstimatedTime >= DATE(NOW())
  AND t.EstimatedTime  < DATE(NOW()) + INTERVAL 2 DAY

NOW() 返回当前日期和时间，DATE() 函数修剪掉时间部分，使其等同于午夜.

NOW() returns the current date and time, the DATE() function trims off the time portion, making it equivalent to midnight.

如果列被声明为字符类型，而不是 DATETIME(但为什么在上帝的绿色地球上你会这样做?)，将 DATETIME 表达式转换为字符 in适当的规范格式，以便字符串比较能够正常工作:

If the column is declared as character type, rather than DATETIME (but why in God's green earth would you do that?), convert the DATETIME expression to character in an appropriate canonical format, so that string comparisons will work appropriately:

WHERE t.EstimatedTime >= DATE_FORMAT(DATE(NOW())                 ,'%Y%m%d%H%i%s')
  AND t.EstimatedTime  < DATE_FORMAT(DATE(NOW()) + INTERVAL 2 DAY,'%Y%m%d%H%i%s')

如果 EstimatedTime 存储为数字(整数)数据类型，则通过添加零将字符串转换为数字...

If EstimatedTime is stored as a numeric (integer) datatype, then convert the string to numeric by adding a zero...

WHERE t.EstimatedTime >= DATE_FORMAT(DATE(NOW())                 ,'%Y%m%d%H%i%s')+0
  AND t.EstimatedTime  < DATE_FORMAT(DATE(NOW()) + INTERVAL 2 DAY,'%Y%m%d%H%i%s')+0

这篇关于选择仅在今天和未来 2 天之间的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！