Welcome Guest, Not a member yet? Register   Sign In
Retrieving results by month...
#1

[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')

$this->db->where('MONTH(current_time)', $month);
$this->db->where('user_id', $uid);
$query = $this->db->get('hits');

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! Smile
#2

[eluser]Ivan A. Zenteno[/eluser]
sorry I not understanding what do you need.
#3

[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);
#4

[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?
#5

[eluser]WanWizard[/eluser]
What is your definition of a timestamp? How is the database field defined, and what exactly is stored in it?
#6

[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 Smile




Theme © iAndrew 2016 - Forum software by © MyBB