Searching specifically for year in date field

[davidcriniti]

Hi all,

 

I have a search page where I'd like members to be able to search for races that they and their friends have entered into a database. When entering the data, users put the race date into the race_date field (Which is a DATETIME field) in the table.

 

I've set up the search and display pages, so users can search by a number of other criteria (first name, last name, race distance, time, etc), but am having trouble with searching by year. On my search form, is a field called race_year.

 

So, obviously I need some change in the code below, specifically in relation to the line $where .= " AND race_date='$race_year'"; because I want to extract the year part of the race_date column from the table and see if it matches $race_year .

 

Any advice would be appreciated.

 

Cheers,

Dave

 

$race_year = $_POST['race_year'];
if ($race_year != '')
{ // A year is selected
  $where .= " AND race_date='$race_year'";
}

 

[Barand]

Use the YEAR() function

... YEAR(race_date) = $race_year

[requinix]

I've wondered about this for a while but never had the chance to test it:

 

Would a BETWEEN be more efficient?

...race_date BETWEEN '$race_year-01-01' AND '$race_year-12-31'

...race_date BETWEEN '$race_year-01-01 00:00:00' AND '$race_year-12-31 23:59:59'

Or is MySQL smart enough to do that optimization (or something else) by itself?

[Barand]

requinix, I hate to say this but you win

 

I too became curious so I set up a benchmark with and without an index on the date field

/******* THE TABLE **********************************
        CREATE TABLE `dates` (
          `thedate` date NOT NULL DEFAULT '0000-00-00',
          `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (`id`),
          KEY `idx_date` (`thedate`)
        )
******************************************************/

$years = range(1986,2014);
$days = range(1,365);
shuffle($years);
shuffle($days);

/**** CREATE 10,000+ recs in random order ************

        $sql = "INSERT INTO dates (thedate)
                VALUES (STR_TO_DATE(?, '%Y %j'))";
        $stmt = $db->prepare($sql) or die($db->error);
        $stmt->bind_param('s', $date);

        foreach ($days as $d) {
            foreach ($years as $y) {
                $date = "$y $d";
                $stmt->execute();
            }
        }

*******************************************************/

// BENCHMARK TEST

$t1 = microtime(1);
foreach ($years as $y) {
    $sql = "SELECT * 
            FROM dates 
            WHERE YEAR(thedate) = $y";
    $db->query($sql);
}
$t2 = microtime(1);
foreach ($years as $y) {
    $sql = "SELECT * 
            FROM dates 
            WHERE thedate BETWEEN '{$y}-01-01' AND '{$y}-12-31'";
    $db->query($sql);
}
$t3 = microtime(1);

echo "<h3>Result</h3><pre>";
printf ("YEAR()  : %8.4f\n", $t2-$t1);
printf ("BETWEEN : %8.4f\n", $t3-$t2);

The results were

with index on thedate

YEAR()  :   0.1619
BETWEEN :   0.0276

without index

YEAR()  :   0.1740
BETWEEN :   0.1659

[davidcriniti]

Perfect!

 

Thank you both!