SQL Query - Date range filtering

Hi, i'm coding in PHP/MySQL and have the following table data, wondering how to achieve the following SQL queries...

TABLE: 'events'
id    title                   startDate                     finishDate       
1     do this                 01/09/2015 9:00 AM     30/09/2015 5:00 PM
2     do that            02/09/2015 9:30 AM       02/10/2015 5:30 PM
3     then this            10/10/2015 8:00 AM       11/11/2015 5:00 PM

SQL Search #1:
SELECT * FROM events WHERE (current time is in between `startDate` and `finishDate`)

Open in new window


SQL Search #2:
SELECT * FROM events WHERE (current month is inside of `startDate` and `finishDate`)

Open in new window


SQL Search #3:
SELECT * FROM events WHERE (current week is inside of `startDate` and `finishDate`)

Open in new window


Hope someone can shine some light.

Thanks in advance!
dev09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Search #1
SELECT * FROM `events` WHERE NOW() BETWEEN startDate AND finishDate

Open in new window


Search #2
SELECT * FROM `events` WHERE MONTH(NOW()) BETWEEN MONTH(startDate) AND MONTH(finishDate);

Open in new window

Search #3
SELECT * FROM `events` WHERE WEEK(NOW()) BETWEEN WEEK(startDate) AND WEEK(finishDate)

Open in new window


Here is where we need more information. The last query doesn't really make sense - what if start date is in week1 of january and finishDate is week 4 of March - then pretty much any date will match even those outside of the Jan-Mar period.

The same applies with months with respect to years.

Maybe give us some more information about the data you are expecting to see and exactly what you want to do with these queries.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
This is a fairly common calendar problem.  You probably want to use the ISO-8601 standard representation for dates.  It will make all of your programming easier.  Here's how to do that.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
Ray PaseurCommented:
See if this helps you get started.  Most of the code is just setup so we can test queries easily.  Moving parts start at line 175.
http://iconoun.com/demo/temp_dev09.php
<?php // demo/temp_dev09.php
/**
 * http://www.experts-exchange.com/questions/28702416/SQL-Query-Date-range-filtering.html
 * http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html
 *
 * TABLE: 'events'
 * id    title                   startDate                     finishDate
 * 1     do this                 01/09/2015 9:00 AM     30/09/2015 5:00 PM
 * 2     do that            02/09/2015 9:30 AM       02/10/2015 5:30 PM
 * 3     then this            10/10/2015 8:00 AM       11/11/2015 5:00 PM
 *
 * SELECT * FROM events WHERE (current time is in between `startDate` and `finishDate`)
 * SELECT * FROM events WHERE (current month is inside of `startDate` and `finishDate`)
 * SELECT * FROM events WHERE (current week is inside of `startDate` and `finishDate`)
 */
// RAISE THE ERROR REPORTING LEVEL TO THE HIGHEST POSSIBLE SETTING
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// CREATE AN ARRAY OF TEST DATA USING THE ISO-8601 STANDARD NOTATION
$test_names_arrays = array
( array( "title" => "do this"   , "startDate" => "2015-09-01 09:00" , "finishDate" => "2015-09-30 17:00")
, array( "title" => "do that"   , "startDate" => "2015-09-02 09:30" , "finishDate" => "2015-10-02 17:30")
, array( "title" => "then this" , "startDate" => "2015-10-10 08:00" , "finishDate" => "2015-11-11 17:00")
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
require_once('RAY_live_data.php');
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE events
( id         INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, title      VARCHAR(24) NOT NULL DEFAULT ''
, startDate  DATETIME    NOT NULL
, finishDate DATETIME    NOT NULL
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $evt)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_t  = $mysqli->real_escape_string($evt['title']);
    $safe_s  = $mysqli->real_escape_string($evt['startDate']);
    $safe_f  = $mysqli->real_escape_string($evt['finishDate']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO events ( title, startDate, finishDate ) VALUES ( '$safe_t', '$safe_s', '$safe_f')";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }


    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_t</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT * FROM events ORDER BY id";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE OTHER MYSQLI::RESULT PROPERTIES AND METHODS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo PHP_EOL . "QUERY: $sql ";
    echo PHP_EOL . "FOUND NO DATA ";
}
else
{
    echo PHP_EOL . "QUERY: $sql ";
    echo PHP_EOL . "FOUND $num_fmt ROWS OF DATA ";
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// SET UP A SPECIALIZED QUERY
$my_date = date('c', strtotime('September 15, 2015'));
echo PHP_EOL . $my_date;

// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT * FROM events WHERE '$my_date' BETWEEN startDate and finishDate ORDER BY id";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.