javascript 使用 PHP 从 MySql 数据库中选择两个日期之间的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17176847/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting records between two dates using PHP from MySql database
提问by Vikram
In my project , I am generating and storing the Bill (invoice). The date of Bill is coming to the textbox from the javascript date picker(small pop-up calender) before saving. The format of the date is : DD-MON-YYYY (18-JUN-2013).
在我的项目中，我正在生成和存储账单（发票）。比尔的日期在保存之前从 javascript 日期选择器（小型弹出日历）进入文本框。 日期格式为：DD-MON-YYYY (18-JUN-2013)。
I am using 'Text' data type for storing dates in MySql table.
我正在使用“文本”数据类型在 MySql 表中存储日期。
I have done selecting of records(Previous Bills) from the table by given single date like. . .
我已经按照给定的单个日期从表中选择了记录（以前的账单）。. .
$result = mysql_query("SELECT * FROM outward WHERE date='".$date."' ORDER BY billNo");
$result = mysql_query("SELECT * FROM Outward WHERE date='".$date."' ORDER BY billNo");
Now, what i want to do is:To select records (Bills) between two dates.....
现在，我想做的是：在两个日期之间选择记录（账单）.....
My exact Question is:Is it possible to query mysql database with this settings or I have to make some changes to select records between 2 dates efficiently ? How can i achieve this ?
P.s.- Is it effective to use
1."SELECT * FROM outward WHERE date BETWEEN '" . $from_date . "' AND '" . $to_date . "' ORDER by id DESC"
Or
2.SELECT * FROM outward WHERE date > "15-JUN-2013" and date < "18-JUN-2013"
我的确切问题是：是否可以使用此设置查询 mysql 数据库，或者我必须进行一些更改才能有效地选择 2 个日期之间的记录？我怎样才能做到这一点？
Ps- 使用
1."SELECT * FROM external WHERE date BETWEEN '"是否有效。$from_date 。“' 和 '” 。$to_date 。"' ORDER by id DESC"
或
2.SELECT * FROM 外向 WHERE date > "15-JUN-2013" and date < "18-JUN-2013"
采纳答案by zavg
You should use strtotimePHP function to convert string date to UNIX timestamp format and change MySQL data type for date field to TIMESTAMP.
您应该使用strtotimePHP 函数将字符串日期转换为 UNIX 时间戳格式，并将日期字段的 MySQL 数据类型更改为TIMESTAMP.
Than you can do effective queries with >and <.
比你可以用>和做有效的查询<。
回答by Brian Hoover
You could do it in a pure SQL way, but you are going to have to do a full table scan for each query.
您可以用纯 SQL 的方式来完成，但您必须对每个查询进行全表扫描。
select the_dates,
STR_TO_DATE(the_dates, '%d-%M-%Y') as converted
from testing
where STR_TO_DATE(the_dates, '%d-%M-%Y') between '2013-06-20' and '2013-06-23'
回答by Pinoniq
Since you are storing it as text but you want SQL to parse it as a DATE SQL doesn't understand in the first place.
由于您将其存储为文本，但您希望 SQL 将其解析为 DATE SQL 首先无法理解。
In your example SQL will use TEXT comparison rules. So 15-April < 15-Mar > 15-DEC
在您的示例中，SQL 将使用 TEXT 比较规则。所以 4 月 15 日 < 3 月 15 日 > 12 月 15 日
If you are storing dates in an SQL database you should be storing it as a Date and not as TEXT.
如果您将日期存储在 SQL 数据库中，您应该将其存储为日期而不是文本。
回答by Ed Gibbs
If it's a DATEcolumn, you can get all dates between 15 June 2013 and 18 June 2013 (inclusive) using this:
如果它是一DATE列，您可以使用以下命令获取 2013 年 6 月 15 日至 2013 年 6 月 18 日（含）之间的所有日期：
WHERE date BETWEEN '2013-06-15' AND '2013-06-18'
If it's a DATETIMEcolumn, do this instead:
如果它是一DATETIME列，请改为执行此操作：
WHERE date >= '2013-06-15' AND date < '2013-06-19'
If the datecolumn is indexed, this approach will make sure the indexes are available for optimization. If it isn't indexed, the approach is just as fast as the many other ways you can do this.
如果该date列已编入索引，则此方法将确保索引可用于优化。如果它没有被编入索引，则该方法与您可以执行此操作的许多其他方法一样快。
Addendum: Just saw the "storing as text" amidst all the other shouted info. Note that this answer applies only if the type is DATEor DATETIME. I'll leave it up because the best answer is to change the column's data type and then use this or one of the other suggested options.
附录：刚刚在所有其他喊叫的信息中看到了“存储为文本”。请注意，此答案仅适用于类型为DATE或 的情况DATETIME。我会保留它，因为最好的答案是更改列的数据类型，然后使用此选项或其他建议选项之一。
回答by Icarus
I am using 'Text' data type for storing dates in MySql table.
我正在使用“文本”数据类型在 MySql 表中存储日期。
That's a problem. You should store dates as dateor datetimedata type in MySQL. If you don't care about the time part, dateshould be sufficient.
这是个问题。您应该在 MySQL 中将日期存储为date或datetime数据类型。如果不关心时间部分，date应该足够了。
If you change your data type to date, then doing:
如果您将数据类型更改为日期，则执行以下操作：
select x,y,z from table a where a.datecolumn between @startdate and @enddate
Should work fine.
应该工作正常。
If you use a text data type, you would have to cast the column to a date column and then apply your date selection range which is going to be slower due to the cast.
如果您使用文本数据类型，则必须将该列强制转换为日期列，然后应用您的日期选择范围，这会因转换而变慢。
Always store data in the data type that matches its kind. If a date then a date column, if it's text then text or varchar, etc. The presentation layer of your app can worry about the formatin which this data is presented to the user.
始终以与其类型匹配的数据类型存储数据。如果是日期，则是日期列，如果是文本，则是文本或 varchar，等等。您的应用程序的表示层可能会担心这些数据以何种格式呈现给用户。
回答by Peter Geer
You said you were using a TEXTcolumn to store the dates. That's an extremely bad idea. If you switch to a DATEor a DATETIME, then this becomes trivial.
你说你正在使用一TEXT列来存储日期。这是一个非常糟糕的主意。如果您切换到 aDATE或 a DATETIME，那么这将变得微不足道。
