从mysql查询中提取特定日期
I have to columns in my table (check_in and checkout). Both of them has this format dd-mm-yyyy
. I want to check if there is any date available between my two paramaters(inputs).
I tried like this:
select * from rezervare where date(check_out) > date('my_checkout_input')
but this works only in a few cases. If i have a reservation between 12-03-2015 - 15-03-2015 and another reservation between 17-03-2015 and 20-03-2015. It will not take the date between 16-03-2015 as a free date. Can you give me some ideas ?
我必须在我的表中使用列(check_in和checkout)。 他们都有这种格式 我试过这样: p>
但这只适用于少数情况。 如果我在 12-03-2015 - 2015年3月15日 strong>之间进行预订,并在 17-03-2015和2015-03-03之间预订 strong>。 它不会将 16-03-2015 strong>之间的日期作为免费日期。 你能给我一些想法吗? p>
div> dd-mm-yyyy code>。 我想检查两个参数(输入)之间是否有可用的日期。 p>
select * from rezervare where date(check_out)> date('my_checkout_input')
code> pre>
create table rezervare (
id int auto_increment,
check_in varchar(10),
checkout varchar(10),
primary key (id)
);
insert into rezervare values
(null, '12-03-2015', '12-03-2015'),
(null, '13-03-2015', '13-03-2015'),
(null, '14-03-2015', '14-03-2015'),
(null, '15-03-2015', '15-03-2015'),
(null, '16-03-2015', '16-03-2015'),
(null, '17-03-2015', '17-03-2015'),
(null, '19-03-2015', '19-03-2015'),
(null, '20-03-2015', '20-03-2015')
;
select * from rezervare;
+----+------------+------------+
| id | check_in | checkout |
+----+------------+------------+
| 1 | 12-03-2015 | 12-03-2015 |
| 2 | 13-03-2015 | 13-03-2015 |
| 3 | 14-03-2015 | 14-03-2015 |
| 4 | 15-03-2015 | 15-03-2015 |
| 5 | 16-03-2015 | 16-03-2015 |
| 6 | 17-03-2015 | 17-03-2015 |
| 7 | 19-03-2015 | 19-03-2015 |
| 8 | 20-03-2015 | 20-03-2015 |
+----+------------+------------+
If your schema same as this, you can't use date function, because:
select id, date(check_in), date(checkout) from rezervare ;
+----+----------------+----------------+
| id | date(check_in) | date(checkout) |
+----+----------------+----------------+
| 1 | NULL | NULL |
| 2 | NULL | NULL |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
| 6 | NULL | NULL |
| 7 | NULL | NULL |
| 8 | NULL | NULL |
+----+----------------+----------------+
But you can use between, in my example it works fine:
select * from rezervare where checkout between '12-03-2015' and '15-03-2015';
+----+------------+------------+
| id | check_in | checkout |
+----+------------+------------+
| 1 | 12-03-2015 | 12-03-2015 |
| 2 | 13-03-2015 | 13-03-2015 |
| 3 | 14-03-2015 | 14-03-2015 |
| 4 | 15-03-2015 | 15-03-2015 |
+----+------------+------------+
select * from rezervare where checkout > '14-03-2015';
+----+------------+------------+
| id | check_in | checkout |
+----+------------+------------+
| 4 | 15-03-2015 | 15-03-2015 |
| 5 | 16-03-2015 | 16-03-2015 |
| 6 | 17-03-2015 | 17-03-2015 |
| 7 | 19-03-2015 | 19-03-2015 |
| 8 | 20-03-2015 | 20-03-2015 |
+----+------------+------------+
But true way - alter table and use correct date type.
In this case, you should use int
to store the date as a timestamp.
The PHP function date()
could transform timestamp to date in the format you choose.
Use the BETWEEN function:
SELECT *
FROM rezervare
WHERE (date BETWEEN '2010-03-12' AND '2010-03-15')