Only choose mySQL based on the month and year.

Problem

In my mySQL database, I have a column with certain rows. One of the rows contains a DATE, such as this: 2012-02-01

I want to use PHP to perform a SELECT based just on the year and month.

The following will be the logic of the SELECT:

$q="SELECT * FROM projects WHERE Date="SELECT HERE THE SPECIFIC YEAR AND MONTH"";

A $_POST variable will be used to pass the precise month and year, such as $_POST[‘period’]=”2012-02″;

I’m not sure how I’m going to accomplish it.

Asked by DiegoP.

Solution #1

SELECT * FROM projects WHERE YEAR(Date) = 2011 AND MONTH(Date) = 5

Answered by Rick Kuipers

Solution #2

If you have

$_POST['period'] = "2012-02";

To begin, locate the month’s first day:

$first_day = $_POST['period'] . "-01"

If you have a Date index, then this query will use it:

$q = "
    SELECT *  
    FROM projects 
    WHERE Date BETWEEN '$first_day' 
                   AND LAST_DAY( '$first_day' )
     " ;

You might also use inclusive-exclusive intervals, which work well (you don’t have to care about whether the column is DATE, DATETIME, or TIMESTAMP, and you don’t have to worry about precision:

$q = "
    SELECT *  
    FROM projects 
    WHERE Date >= '$first_day' 
      AND Date  < '$first_day' + INTERVAL 1 MONTH 
     " ;

Security warning:

These values should be appropriately escaped or prepared statements should be used. To summarize, utilize whichever PHP approach is currently suggested to avoid SQL injection concerns.

Answered by ypercubeᵀᴹ

Solution #3

So there you have it. Leave the computation to PHP and free up some space in your database. You may make good use of an index on the Date column this way.

<?php
    $date = $_POST['period'];

    $start = strtotime($date);
    $end   = strtotime($date . ' 1 month - 1 second');

    $query = sprintf(
        'SELECT *
           FROM projects
          WHERE Date BETWEEN FROM_UNIXTIME(%u) AND FROM_UNIXTIME(%u)',
        $start,
        $end
    );

EDIT: I completely forgot about the Unix timestamp conversion.

Answered by aefxx

Solution #4

Assume you have a database column called created at that gets its value from a timestamp. From the created at date, you wish to search by Year and Month.

YEAR(date(created_at))=2019 AND MONTH(date(created_at))=2

Answered by Nazmul Haque

Solution #5

$q="SELECT * FROM projects WHERE YEAR(date) = 2012 AND MONTH(date) = 1;

Answered by Arjan

Post is based on https://stackoverflow.com/questions/9104704/select-mysql-based-only-on-month-and-year