MySQL：两个日期字段之间的日期

I have two fields with stores a particular date-range of an event.

我有两个字段,存储事件的特定日期范围。

Let's say and event will start at 12/13/12 - 12/20/12.

比方说,活动将于12月13日12日至12月12日开始。

So our fields are

所以我们的领域是

Name:My Event Start_at: 12/13/12 End_at: 12/20/12

姓名:我的活动Start_at:12/13/12 End_at:12/20/12

the user will have two input box. One box will state the from_date and the other to_date. If the user selects

用户将有两个输入框。一个框将声明from_date和另一个to_date。如果用户选择

from_date: 12/15/12 to_date: 12/19/12

from_date:12/15/12 to_date:12/19/12

it should display the 'my event` record since it falls into the date_range.

它应该显示'my event`记录,因为它属于date_range。

Question: How do you go about this in SQL?

问题:你如何在SQL中解决这个问题?

2 个解决方案

#1

it depends on how you keep the date data in database

这取决于您如何将日期数据保存在数据库中

if it is a 'datetime' field use 'between' construction

如果它是'datetime'字段,则使用'between'构造

mysql_query("select * from events where date between '".date("Y-m-d H:i:s", strtotime($from_date))."' and ".date("Y-m-d H:i:s", strtotime($to_date)));

i can't make exact query because i dunno field names in table and your parameter names in query

我无法进行确切的查询,因为我不知道表中的字段名称和查询中的参数名称

i hope you understood the idea

我希望你理解这个想法

#2

as for my experience. date in INT datatype is lighter than datetime datatype on the mysql database, you can try changing the datetime to INT first, then before saving it on the database you can convert it to seconds, say:

至于我的经历。 INT数据类型中的日期比mysql数据库上的datetime数据类型轻,您可以先尝试将日期时间更改为INT,然后在将数据保存到数据库之前将其转换为秒,例如:

<?php
 $start_at = strtotime("12/13/12");
 $end_at = strtotime("12/20/12");
/* SAVE on the database */
?>

then save it on the table date field with INT datatype

然后使用INT数据类型将其保存在表日期字段中

then on the query, you can easily say

然后在查询上,你可以很容易地说

if the user inputs from_date: 12/15/12 to_date: 12/19/12 on the two fields (start and end field)

如果用户在两个字段(开始和结束字段)上输入from_date:12/15/12 to_date:12/19/12

<?php
 $start_at = strtotime($from_date); //12/15/12
 $end_at = strtotime($to_date); //12/19/12
/*do the query*/
 mysql_query("SELECT name FROM table WHERE Start_at <= '$start_at' AND End_at >= '$end_at'");
?>

I didnt complete the script but the idea is there, hope you get it.

我没有完成脚本,但想法就在那里,希望你明白。

#1