Retrieving results by month... - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Retrieving results by month... (/showthread.php?tid=30027) |
Retrieving results by month... - El Forum - 04-29-2010 [eluser]wakey[/eluser] Hi, I've been trying to get this sorted for a while and can't seem to get it to work. I have records in my mysql database that each have column containing the current timestamp as default. I am attempting to select all records from the current month. I have a feeling I need to use code such as: Code: $month = date('n') I have tried the following SQL statement in my db which returns the value '4' - SELECT MONTH('2010-04-29 23:11:45') But when I try this statement on my 'current_time' field (which contains current timestamps) it returns NULL. Does anyone have any ideas?! Any help appreciated! Retrieving results by month... - El Forum - 04-29-2010 [eluser]Ivan A. Zenteno[/eluser] sorry I not understanding what do you need. Retrieving results by month... - El Forum - 04-30-2010 [eluser]WanWizard[/eluser] As per the MySQL manual, the MONTH() function requires a MySQL date as input, not a timestamp. Try: Code: $this->db->where('MONTH(FROM_UNIXTIME(current_time))', $month); Retrieving results by month... - El Forum - 04-30-2010 [eluser]wakey[/eluser] Thanks for the tip, I thought I was missing something and I now seem to be on the right track. I've just run the following query on my table directly in phpmyadmin- Code: SELECT MONTH(FROM_UNIXTIME(current_time)) FROM hits and this is returning '1' for each record and running the same query but with MONTHNAME returns 'January' which is strange as all records have a timestamp from April. Any idea why this is going wrong? Retrieving results by month... - El Forum - 04-30-2010 [eluser]WanWizard[/eluser] What is your definition of a timestamp? How is the database field defined, and what exactly is stored in it? Retrieving results by month... - El Forum - 04-30-2010 [eluser]wakey[/eluser] Hi WanWizard, This was a very strange one. I got there in the end by using SELECT MONTH(current_time) FROM hits but this only worked after dropping the table and re-creating it exactly as it was. I haven't changed anything except for the field name from current_time to time. Very strange but thanks for you help |