There is a table that has three columns. ID, Date and Time. I need to select rows that are between 2014/01/04 and 2014/06/15 (specified by green) where start time is 12:00:00 and finish time is 20:00:00 (specified by blue). How can I select all blue rows?
有一个表有三列。 ID,日期和时间。我需要选择2014/01/04和2014/06/15之间的行(由绿色指定),其中开始时间是12:00:00,结束时间是20:00:00(由蓝色指定)。如何选择所有蓝色行?
I used this method:
我用这个方法:
$query = "SELECT * FROM $database_table
WHERE UTCdate BETWEEN '{$from_date}' AND '{$to_date}'
AND satellite_derived_time BETWEEN CAST('{$from_time}' AS UNSIGNED)
AND CAST('{$to_time}' AS UNSIGNED)";
But it's not right because time doesn't always increase like date.
但这不对,因为时间并不总是像日期一样增加。
I need all rows that have a date between 2014/01/04 and 2014/06/15 AND also have a time between 12:00:00 and 20:00:00.
我需要所有日期在2014/01/04和2014/06/15之间的行,并且时间在12:00:00到20:00:00之间。
Date is OK because I can use between from_date and to_date but my problem is time. Look at table, Time isn't increasing like date, so I can't use between for time. For example my time start from 135911 and end with 020719 so it just lists rows that are between these two numbers but I need all blue rows like row 6 that is not in this range.
日期还可以,因为我可以在from_date和to_date之间使用,但我的问题是时间。看表,时间不像日期那样增加,所以我不能在时间之间使用。例如,我的时间从135911开始,以020719结束,所以它只列出了这两个数字之间的行,但我需要所有蓝色行,如第6行,不在此范围内。
3 个解决方案
#1
1
Write the php code so that your database processes this command.
编写php代码,以便数据库处理此命令。
SELECT * FROM yourtable
WHERE UTCdate BETWEEN 20140104 and 20140615 -- these are integers
AND satellite_derived_time BETWEEN '120000' and '200000' -- these are strings
#2
1
Edit 1:
This work for me exactly:
这对我来说完全符合:
$query = "SELECT * FROM $database_table WHERE UTCdate BETWEEN '{$from_date}' AND '{$to_date}' AND satellite_derived_time BETWEEN CAST('{$from_time}' AS UNSIGNED) AND CAST('{$to_time}' AS UNSIGNED)";
but doesn't show all rows I need. It just shows me rows that are between
12:00:00
and20:00:00
and for example doesn't show row 6 because its time is23
但是没有显示我需要的所有行。它只显示在12:00:00和20:00:00之间的行,例如不显示第6行,因为它的时间是23
Change part of your query like the following:
更改部分查询,如下所示:
SELECT * FROM $database_table
WHERE str_to_date( concat( UTCdate, ' ', satellite_derived_time ),
'%Y%m%d %H%i%s' )
BETWEEN str_to_date( concat( '{$from_date}', ' ', '{$from_time}' ),
'%Y%m%d %H%i%s' )
AND str_to_date( concat( '{$to_date}', ' ', '{$to_time}' ),
'%Y%m%d %H%i%s' )
Assuming your input for dates is in yyyymmdd
format and for time is in H:i:s
format.
假设您输入的日期是yyyymmdd格式,时间是H:i:s格式。
select * from $database_table
where str_to_date( UTCdate, '%Y%m%d' )
between str_to_date( '$from_date', '%Y%m%d' )
and str_to_date( '$to_date', '%Y%m%d' )
and str_to_date( satellite_derived_time, '%H%i%s' )
/*
between str_to_date( '$fromTime', '%Y%m%d' )
and str_to_date( '$toTime', '%Y%m%d' )
*/
-- or, if you use straight time format, then
between '$fromTime' and '$toTime'
Use prepared statement to bind variables.
使用预准备语句绑定变量。
#3
1
This is the answer:
UTCdate is date and satellite_derived_time is time in table.
这就是答案:UTCdate是日期,satellite_derived_time是表格中的时间。
//Get the accessible first day from date range between '$from_date' and '$to_date'. Then get the first time that is bigger or equal to '$from_time'. If there is no time bigger than '$from_time' in the specified first day so it get the first time of next day.
{
//Find the first accessible day between two given date range.
$first_day = "(SELECT UTCdate FROM $database_table WHERE UTCdate BETWEEN '{$from_date}' AND '{$to_date}' LIMIT 1)";
$result = mysql_query($first_day) or die('Query faild'.mysql_error());
$first_day_rows = mysql_fetch_assoc($result);
//Get id of the nearest day that is biggest or equal to '$from_date' and '$from_time'.
$first_time = "(SELECT id FROM $database_table WHERE UTCdate = '{$first_day_rows['UTCdate']}' AND satellite_derived_time >= CAST('{$from_time}' AS UNSIGNED) LIMIT 1)";
$result = mysql_query($first_time) or die('Query faild'.mysql_error());
$first_time_rows = mysql_fetch_assoc($result);
if((!$first_time_rows) && $first_day_rows['UTCdate'])
{
$first_day = "(SELECT UTCdate FROM $database_table WHERE UTCdate > '{$first_day_rows['UTCdate']}' LIMIT 1)";
$first_time = "(SELECT id FROM $database_table WHERE UTCdate = $first_day AND satellite_derived_time >= CAST('0' AS UNSIGNED) LIMIT 1)";
}
}
//Get the accessible last day from date range between '$from_date' and '$to_date'. Then get the last time that is smaller or equal to '$to_time'. If there is no time lesser than '$to_time' in the specified last day so it get the last time of previous day.
{
//Find the last accessible day between two given date range.
$last_day = "(SELECT UTCdate FROM $database_table WHERE UTCdate BETWEEN '{$from_date}' AND '{$to_date}' ORDER BY UTCdate DESC LIMIT 1)";
$result = mysql_query($last_day) or die('Query faild'.mysql_error());
$last_day_rows = mysql_fetch_assoc($result);
//Get id of the nearest day that is biggest or equal to '$from_date' and '$from_time'.
$last_time = "(SELECT id FROM $database_table WHERE UTCdate = '{$last_day_rows['UTCdate']}' AND satellite_derived_time <= CAST('{$to_time}' AS UNSIGNED) ORDER BY satellite_derived_time DESC LIMIT 1)";
$result = mysql_query($last_time) or die('Query faild'.mysql_error());
$last_time_rows = mysql_fetch_assoc($result);
if((!$last_time_rows) && $last_day_rows['UTCdate'])
{
$last_day = "(SELECT UTCdate FROM $database_table WHERE UTCdate < '{$last_day_rows['UTCdate']}' ORDER BY UTCdate DESC LIMIT 1)";
$last_time = "(SELECT id FROM $database_table WHERE UTCdate = $last_day AND satellite_derived_time <= CAST('235959' AS UNSIGNED) ORDER BY satellite_derived_time DESC LIMIT 1)";
}
}
#1
1
Write the php code so that your database processes this command.
编写php代码,以便数据库处理此命令。
SELECT * FROM yourtable
WHERE UTCdate BETWEEN 20140104 and 20140615 -- these are integers
AND satellite_derived_time BETWEEN '120000' and '200000' -- these are strings
#2
1
Edit 1:
This work for me exactly:
这对我来说完全符合:
$query = "SELECT * FROM $database_table WHERE UTCdate BETWEEN '{$from_date}' AND '{$to_date}' AND satellite_derived_time BETWEEN CAST('{$from_time}' AS UNSIGNED) AND CAST('{$to_time}' AS UNSIGNED)";
but doesn't show all rows I need. It just shows me rows that are between
12:00:00
and20:00:00
and for example doesn't show row 6 because its time is23
但是没有显示我需要的所有行。它只显示在12:00:00和20:00:00之间的行,例如不显示第6行,因为它的时间是23
Change part of your query like the following:
更改部分查询,如下所示:
SELECT * FROM $database_table
WHERE str_to_date( concat( UTCdate, ' ', satellite_derived_time ),
'%Y%m%d %H%i%s' )
BETWEEN str_to_date( concat( '{$from_date}', ' ', '{$from_time}' ),
'%Y%m%d %H%i%s' )
AND str_to_date( concat( '{$to_date}', ' ', '{$to_time}' ),
'%Y%m%d %H%i%s' )
Assuming your input for dates is in yyyymmdd
format and for time is in H:i:s
format.
假设您输入的日期是yyyymmdd格式,时间是H:i:s格式。
select * from $database_table
where str_to_date( UTCdate, '%Y%m%d' )
between str_to_date( '$from_date', '%Y%m%d' )
and str_to_date( '$to_date', '%Y%m%d' )
and str_to_date( satellite_derived_time, '%H%i%s' )
/*
between str_to_date( '$fromTime', '%Y%m%d' )
and str_to_date( '$toTime', '%Y%m%d' )
*/
-- or, if you use straight time format, then
between '$fromTime' and '$toTime'
Use prepared statement to bind variables.
使用预准备语句绑定变量。
#3
1
This is the answer:
UTCdate is date and satellite_derived_time is time in table.
这就是答案:UTCdate是日期,satellite_derived_time是表格中的时间。
//Get the accessible first day from date range between '$from_date' and '$to_date'. Then get the first time that is bigger or equal to '$from_time'. If there is no time bigger than '$from_time' in the specified first day so it get the first time of next day.
{
//Find the first accessible day between two given date range.
$first_day = "(SELECT UTCdate FROM $database_table WHERE UTCdate BETWEEN '{$from_date}' AND '{$to_date}' LIMIT 1)";
$result = mysql_query($first_day) or die('Query faild'.mysql_error());
$first_day_rows = mysql_fetch_assoc($result);
//Get id of the nearest day that is biggest or equal to '$from_date' and '$from_time'.
$first_time = "(SELECT id FROM $database_table WHERE UTCdate = '{$first_day_rows['UTCdate']}' AND satellite_derived_time >= CAST('{$from_time}' AS UNSIGNED) LIMIT 1)";
$result = mysql_query($first_time) or die('Query faild'.mysql_error());
$first_time_rows = mysql_fetch_assoc($result);
if((!$first_time_rows) && $first_day_rows['UTCdate'])
{
$first_day = "(SELECT UTCdate FROM $database_table WHERE UTCdate > '{$first_day_rows['UTCdate']}' LIMIT 1)";
$first_time = "(SELECT id FROM $database_table WHERE UTCdate = $first_day AND satellite_derived_time >= CAST('0' AS UNSIGNED) LIMIT 1)";
}
}
//Get the accessible last day from date range between '$from_date' and '$to_date'. Then get the last time that is smaller or equal to '$to_time'. If there is no time lesser than '$to_time' in the specified last day so it get the last time of previous day.
{
//Find the last accessible day between two given date range.
$last_day = "(SELECT UTCdate FROM $database_table WHERE UTCdate BETWEEN '{$from_date}' AND '{$to_date}' ORDER BY UTCdate DESC LIMIT 1)";
$result = mysql_query($last_day) or die('Query faild'.mysql_error());
$last_day_rows = mysql_fetch_assoc($result);
//Get id of the nearest day that is biggest or equal to '$from_date' and '$from_time'.
$last_time = "(SELECT id FROM $database_table WHERE UTCdate = '{$last_day_rows['UTCdate']}' AND satellite_derived_time <= CAST('{$to_time}' AS UNSIGNED) ORDER BY satellite_derived_time DESC LIMIT 1)";
$result = mysql_query($last_time) or die('Query faild'.mysql_error());
$last_time_rows = mysql_fetch_assoc($result);
if((!$last_time_rows) && $last_day_rows['UTCdate'])
{
$last_day = "(SELECT UTCdate FROM $database_table WHERE UTCdate < '{$last_day_rows['UTCdate']}' ORDER BY UTCdate DESC LIMIT 1)";
$last_time = "(SELECT id FROM $database_table WHERE UTCdate = $last_day AND satellite_derived_time <= CAST('235959' AS UNSIGNED) ORDER BY satellite_derived_time DESC LIMIT 1)";
}
}