PHP mysql 选择日期范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6482908/
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
PHP mysql select date range
提问by Learner
I have a invoice type form submission and every submission has a date so I have created a varcha date field in mysql database to save the time.Time format is like '2011-06-26'.Now I need to select a range based on the date from my database.I tried following options but they don't display any result just blank page is displayed,even no errors.
我有一个发票类型的表单提交，每次提交都有一个日期，所以我在 mysql 数据库中创建了一个 varcha 日期字段来保存时间。时间格式就像'2011-06-26'。现在我需要选择一个基于我的数据库中的日期。我尝试了以下选项，但它们不显示任何结果，只显示空白页，甚至没有错误。
Should I follow some special techniques on saving date to my database.If so please give me some explain about that because I am new to PHP and MYSQL development.
我是否应该遵循一些将日期保存到我的数据库的特殊技术。如果是这样，请给我一些解释，因为我是 PHP 和 MYSQL 开发的新手。
SELECT * FROM table_name
WHERE 'date'
BETWEEN UNIX_TIMESTAMP('2011-06-02') AND UNIX_TIMESTAMP('2011-06-25')
SELECT * FROM my_table
WHERE 'date'
BETWEEN CAST('2011-06-02' AS DATE) AND CAST('2011-06-25' AS DATE)";
This is what I used to extract data
这是我用来提取数据的
$result=mysql_query($query);
while($row=mysql_fetch_array($result)){
echo $row['tax']."<br>";
}
Thank you.
谢谢你。
回答by dave
If you use DATETIMEtype to store your date the trick is simple.
DATETIMEallows you to store the date and the timestamp, however if only the date is specifed, timestamp will be stored as 00:00:00
如果你使用DATETIMEtype 来存储你的日期，这个技巧很简单。
DATETIME允许您存储日期和时间戳，但是如果只指定日期，时间戳将存储为00:00:00
I'll show you a simple example.
我将向您展示一个简单的例子。
mysql> CREATE TABLE test_table(
id INT UNSIGNED AUTO_INCREMENT,
test_date DATETIME,
PRIMARY KEY (id));
mysql> INSERT INTO test_table(test_date)
VALUES('2011-06-26'), ('2011-05-14'), ('2011-05-02');
mysql> SELECT * FROM test_table;
+----+---------------------+
| id | test_date |
+----+---------------------+
| 1 | 2011-06-26 00:00:00 |
+----+---------------------+
| 2 | 2011-05-14 00:00:00 |
+----+---------------------+
| 3 | 2011-05-02 00:00:00 |
+----+---------------------+
mysql> SELECT * FROM test_table WHERE test_date
BETWEEN '2011-05-01' AND '2011-05-31';
+----+---------------------+
| id | test_date |
+----+---------------------+
| 2 | 2011-05-14 00:00:00 |
+----+---------------------+
| 3 | 2011-05-02 00:00:00 |
+----+---------------------+
That's it.
就是这样。
回答by user710818
- Name of column - or without or in backticks
- If date is varchar - also need cast date
SELECT * FROM my_table
WHERE cast(
dateas DATE) BETWEEN CAST('2011-06-02' AS DATE) AND CAST('2011-06-25' AS DATE)";
- 列名 - 或不带或带反引号
- 如果日期是 varchar - 还需要投射日期 SELECT * FROM my_table WHERE cast(
dateas DATE) BETWEEN CAST('2011-06-02' AS DATE) AND CAST('2011-06-25' AS DATE)";
回答by Dan Smith
If the date column is of the TIMESTAMP or DATETIME type then the query should be:
如果日期列是 TIMESTAMP 或 DATETIME 类型，那么查询应该是：
SELECT * FROM table_name
WHERE 'date'
BETWEEN '2011-06-02' AND '2011-06-25'
Edit: Sorry didn't see the column was a VARCHAR - you should change it to the DATETIME type to make things simpler.
编辑：抱歉，没有看到该列是 VARCHAR - 您应该将其更改为 DATETIME 类型以使事情更简单。
