CODEWITHSUNDEEP
phpmysqlsqlselectbetween
UserMat
UserMat

Reputation: 616

How to select rows between date and time in mysql?

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?

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. enter image description here

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.

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.

Upvotes: 2

Views: 2362

Answers (3)

UserMat
UserMat

Reputation: 616

This is the answer:
UTCdate is date and satellite_derived_time is time in table.

//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)";
  }
}

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 24022

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 and 20:00:00 and for example doesn't show row 6 because its time is 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.

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.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Write the php code so that your database processes this command.

SELECT * FROM yourtable 
WHERE UTCdate BETWEEN  20140104 and 20140615   -- these are integers
AND satellite_derived_time BETWEEN '120000' and '200000' -- these are strings

Upvotes: 1

Related Questions