php - mysql select month from timestamp -

i have mysql query it's not working yet. idea is: have month calendar, select items current month form db.

i select tv show episodes, air on specific date available next day. want have available dates in cal. specific example: episode aires 2013-03-31 , available on 2013-04-01. episode not selected db. have tried this:

$this->db->where('month(from_unixtime(first_aired+86400))='.$month); 

but didn't work. know solution? full code:

$this->db->select('*, episodes.overview overview, episodes.id ep_id, shows.id id'); $this->db->from('episodes'); $this->db->join('shows', 'episodes.imdb_id = shows.imdb_id');  $this->db->where('char_length(episodes.ep_title) > 1');  $this->db->where('month(from_unixtime(first_aired))='.$month); $this->db->where('year(from_unixtime(first_aired))='.$year); $this->db->order_by('episodes.first_aired', 'desc');  $data = $this->db->get()->result_array(); 

thanks in advance!

use mysqls date_add function:

select mydate, date_add(mydate, interval 1 day) testdata; 

so if want month of next day, use month(date_add(...))

mysql> select mydate, date_add(mydate, interval 1 day) "next day", month(date_add(mydate, interval 1 day)) "month of next day" testdata; +------------+------------+-------------------+ | mydate     | next day   | month of next day | +------------+------------+-------------------+ | 2013-01-31 | 2013-02-01 |                 2 | | 2013-01-01 | 2013-01-02 |                 1 | +------------+------------+-------------------+ 2 rows in set (0.00 sec) 

so query, use $this->db->where('month(date_add(first_aired, interval 1 day))='.$month);. oh, , might have add from_unixtime here , there.