CODEWITHSUNDEEP
phpmysql
DiegoP.
DiegoP.

Reputation: 45757

Select mySQL based only on month and year

I have a column in my mySQL DB that has some rows. One of this row is a DATE, like this: 2012-02-01

What I want to achieve is to do a SELECT with PHP based only on the year and month.

The logic of the SELECT will be the following:

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

The specific month and year will be be passed from a $_POST variable, like this $_POST['period']="2012-02";

How can I do it?

Upvotes: 119

Views: 322987

Answers (16)

Ankush Mukati
Ankush Mukati

Reputation: 1

It is so simple. You may use inbuilt MySql functions YEAR() and MONTH().

$query = "select * from table_name where MONTH($variable) AND YEAR($variable)";

Upvotes: -1

Rick Kuipers
Rick Kuipers

Reputation: 6607

SELECT * FROM projects WHERE Date BETWEEN '2000-01-01' AND '2000-01-31'

for January, 2000

Upvotes: 234

T30
T30

Reputation: 12242

You can use mysql DATE_FORMAT function consider just the year+month part of the date:

select * from table where DATE_FORMAT(column, '%Y%m') = '202105';

Upvotes: 5

Chirag
Chirag

Reputation: 107

To get the specific month and year data from the table

SELECT * FROM table WHERE DATE_FORMAT(column_name,'%Y-%m') = '2021-06'

Upvotes: 8

Whip
Whip

Reputation: 2244

No one seems to be talking about performance so I tested the two most popular answers on a sample database from Mysql. The table has 2.8M rows with structure

CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `salaries`
  ADD PRIMARY KEY (`emp_no`,`from_date`);

Here are the tests performed and results

SELECT * FROM `salaries` WHERE YEAR(from_date) = 1992 AND MONTH(from_date) = 6
12339 results
-----------------
11.5 ms 
12.1 ms
09.5 ms
07.5 ms
10.2 ms
-----------------
10.2 ms Avg
-----------------


SELECT * FROM `salaries` WHERE from_date BETWEEN '1992-06-01' AND '1992-06-31'
-----------------
10.0 ms
10.8 ms
09.5 ms
09.0 ms
08.3 ms
-----------------
09.5 ms Avg
-----------------


SELECT * FROM `salaries` WHERE YEAR(to_date) = 1992 AND MONTH(to_date) = 6
10887 results
-----------------
10.2 ms
11.7 ms
11.8 ms
12.4 ms
09.6 ms
-----------------
11.1 ms Avg
-----------------


SELECT * FROM `salaries` WHERE to_date BETWEEN '1992-06-01' AND '1992-06-31'
-----------------
09.0 ms
07.5 ms
10.6 ms
11.7 ms
12.0 ms
-----------------
10.2 ms Avg
-----------------

My Conclusions

  1. BETWEEN was slightly better on both indexed and unindexed column.
  2. The unindexed column was marginally slower than the indexed column.

Upvotes: 1

Symon265
Symon265

Reputation: 11

you can do it by changing $q to this:

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

Upvotes: 1

Nazmul Haque
Nazmul Haque

Reputation: 868

Suppose you have a database field created_at Where you take value from timestamp. You want to search by Year & Month from created_at date.

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

Upvotes: 9

Frank Casser
Frank Casser

Reputation: 9

Here is a query that I use and it will return each record within a period as a sum.

Here is the code:

$result = mysqli_query($conn,"SELECT emp_nr, SUM(az) 
FROM az_konto 
WHERE date BETWEEN '2018-01-01 00:00:00' AND '2018-01-31 23:59:59' 
GROUP BY emp_nr ASC");

echo "<table border='1'>
<tr>
<th>Mitarbeiter NR</th>
<th>Stunden im Monat</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
$emp_nr=$row['emp_nr'];
$az=$row['SUM(az)'];
echo "<tr>";
echo "<td>" . $emp_nr . "</td>";
echo "<td>" . $az . "</td>";
echo "</tr>";
}
echo "</table>";
$conn->close();
?>

This lists each emp_nr and the sum of the monthly hours that they have accumulated.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

If you have 

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

First, find the first day of the month:

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

Then this query will use an index on Date if you have one:

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

One could also use inclusive-exclusive intervals, which work pretty good (you don't have to worry if the column is DATE, DATETIME or TIMESTAMP, nor about the precision:

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

Security warning:

You should properly escape these values or use prepared statements. In short, use whatever method is recommended these days in PHP, to avoid any SQL injection issues.

Upvotes: 20

BLOGBIR
BLOGBIR

Reputation: 67

Yeah this is working, but it returns one row only while there is several rows to retrieve using selected columns only. Like SELECT Title,Date FROM mytable WHERE YEAR(Date)=2017 AND MONTH(Date)=09 returns only one row.

Upvotes: -2

chintan mahant
chintan mahant

Reputation: 136

Here, FIND record by MONTH and DATE in mySQL

Here is your POST value $_POST['period']="2012-02";

Just, explode value by dash $Period = explode('-',$_POST['period']);

Get array from explode value :

Array ( [0] => 2012 [1] => 02 )

Put value in SQL Query:

SELECT * FROM projects WHERE YEAR(Date) = '".$Period[0]."' AND Month(Date) = '".$Period[0]."';

Get Result by MONTH and YEAR.

Upvotes: 2

aefxx
aefxx

Reputation: 25279

Here you go. Leave the computing to PHP and save your DB some work. This way you can make effective use of an index on the Date column.

<?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
Forgot the Unix timestamp conversion.

Upvotes: 9

Andreas Helgegren
Andreas Helgegren

Reputation: 1678

You can do like this:

$q="SELECT * FROM projects WHERE Year(Date) = '$year' and Month(Date) = '$month'";

Upvotes: 2

PAULDAWG
PAULDAWG

Reputation: 790

to get the month and year values from the date column

select year(Date) as "year", month(Date) as "month" from Projects

Upvotes: 1

Arjan
Arjan

Reputation: 9884

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

Upvotes: 12

Hristo Petev
Hristo Petev

Reputation: 309

The logic will be:

SELECT * FROM objects WHERE Date LIKE '$_POST[period]-%';

The LIKE operator will select all rows that start with $_POST['period'] followed by dash and the day of the mont

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html - Some additional information

Upvotes: 2

Related Questions