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?
SELECT * FROM projects WHERE Date BETWEEN '2000-01-01' AND '2000-01-31'
for January, 2000
EXTRACT(YEAR_MONTH FROM Date)?
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.
$q="SELECT * FROM projects WHERE YEAR(date) = 2012 AND MONTH(date) = 1;
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.
$end = strtotime($date .' 1 month - 1 second');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
To get the specific month and year data from the table
SELECT * FROM table WHERE DATE_FORMAT(column_name,'%Y-%m') = '2021-06'
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';
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
You can do like this:
$q="SELECT * FROM projects WHERE Year(Date) = '$year' and Month(Date) = '$month'";
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.
to get the month and year values from the date column
select year(Date) as "year", month(Date) as "month" from Projects
you can do it by changing $q to this:
$q="SELECT * FROM projects WHERE YEAR(date) = $year_v AND MONTH(date) = $month_v;
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
BETWEEN was slightly better on both indexed and unindexed column.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.
It is so simple. You may use inbuilt MySql functions YEAR() and MONTH().
$query = "select * from table_name where MONTH($variable) AND YEAR($variable)";
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.
