Select mySQL based only on month and year

Question

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

User · Answer

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    lt  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

User · Answer

You can do like this    q  SELECT   FROM projects WHERE Year Date      year  and Month Date      month

User · Answer

q  SELECT   FROM projects WHERE YEAR date    2012 AND MONTH date    1

User · Answer

SELECT   FROM projects WHERE YEAR Date    2011 AND MONTH Date    5

User · Answer

to get the month and year values from the date column  select year Date  as  year   month Date  as  month  from Projects

User · Answer

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

User · Answer

Suppose you have a database field created at Where you take value from timestamp  You want to search by Year  amp  Month from created at date   YEAR date created at   2019 AND MONTH date created at   2

User · Answer

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  The unindexed column was marginally slower than the indexed column

User · Answer

you can do it by changing  q to this   q  quot SELECT   FROM projects WHERE YEAR date     year v AND MONTH date     month v

User · Answer

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

User · Answer

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    gt  2012          1    gt  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

User · Answer

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  gt     first day         AND Date   lt    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

User · Answer

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   lt table border  1  gt   lt tr gt   lt th gt Mitarbeiter NR lt  th gt   lt th gt Stunden im Monat lt  th gt   lt  tr gt     while  row   mysqli fetch array  result      emp nr  row  emp nr     az  row  SUM az     echo   lt tr gt    echo   lt td gt      emp nr     lt  td gt    echo   lt td gt      az     lt  td gt    echo   lt  tr gt      echo   lt  table gt     conn- gt close      gt    This lists each emp nr and the sum of the monthly hours that they have accumulated

[php] Select mySQL based only on month and year

Examples related to php

Examples related to mysql