Criteria with timestamp in MySQL

[Kirt]

I am unable to write a SQL query for :

Select any table where the year is 2009 and the month is $array [x]

I know this is not too complicated, but the thing is that the only search field that I have are timestamp.

My code:

   include '../../includes/connect_inc.php' ; 
   $ year_and_time = mktime (0, 0, 0, 0, 0, date (Y)); 
   $ year_on = date (Y) +1; 
   $ year_next = mktime (0, 0, 0, 0, 0, $ year_on); 

   $ query = mysql_query ("SELECT COUNT (*) AS rdv woof_agenda FROM WHERE timestamp>". $ year_and_time. "AND timestamp <". $ year_next. "AND ?"); 
   $ return = mysql_fetch_array ($ query) OR die (mysql_error ());

Is this correct? Can I do so?

[fellah]

Why don't you extract the year and month of your date? In fact it will probably be easier in your case. You just need to use the EXTRACT Function.

Syntax:
EXTRACT(unit FROM date)

Description:

The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

Example:

mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
-> 2009

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
-> 200907

mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
-> 20102

mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
-> 123

[Kirt]

I still have a problem. The command seem to be correct, but I always return 0.

  // I made a loop to generate the results by month 
    FOR ( $i = 0 ; $i < 12 ; $i++ )
    { 
   $month = sprintf ( "%02d" ,$i + 1 ) ;
   $year_and_time = date (Y); 

  // To check 
  echo "SELECT COUNT(*) AS nbr FROM woof_agenda WHERE EXTRACT(YEAR FROM timestamp) = " .$year_and_time. " AND EXTRACT(MONTH FROM timestamp) = " .$month. "<br />" ;
	  
   $query = mysql_query ( "SELECT COUNT(*) AS nbr FROM woof_agenda WHERE EXTRACT(YEAR FROM timestamp) = " .$year_and_time. " AND EXTRACT(MONTH FROM timestamp) = " .$month. "" ) ;
   $return = mysql_fetch_array ($query); 
   echo $return ['nbr'];  
  }

[fellah]

Without the structure of your table and an extract of what it contains, it will be difficult to help you. However you can do something like this:

WHERE EXTRACT (YEAR FROM timestamp ) = ".$year_and_time." AND EXTRACT ( MONTH FROM timestamp ) = ".$month_and_time."