Unix Time query (Solved)

[eluser]gazza7364[/eluser]
Hi

I've been writing a blog while learning CI, but I've hit a block.

What I would like to do is make a archive section and would like to extract post from the database, based on month and year. The problem I have is the date and time is in unixtime. eg 1337358506. I'm not sure how I can query this time format, to get only blogs posted on say :- May 2012. Any pointers, I've looked on the net and user guide, but cannot find a solution. The only example I did find was $month = date("n", 5); $year = date("Y", 12), which tells me the the year is 2012 and the month 5.

Thanks in advance.

[eluser]neilmcgann[/eluser]
There's a MySQL unix_timestamp function....

[eluser]gazza7364[/eluser]
[quote author="neilmcgann" date="1337431466"]There's a MySQL unix_timestamp function....
[/quote]

I've had a look at this has suggested, but this seems to convert the date into readable format from unixtime and visa versa. What I need is has follows.
The date in my database is 1325379601 which represents Year: 2012 Month: 01 Day: 01 - 01:00 am . I'm not sure how I can query the date column with the above unixtime and get only blogs, which have the year 2012 and the month January. I have looked at other blogs with archives and they seem to sending $this->uri->segment(3)) and $this->uri->segment(4)) eg, test.com/12/1 for Jan 2012. So somehow sending these 2 parameters to the controller to extract the required blogs for month and year. Sending the parameters isn't a problem. turning them into a query to extract the information from the unixtime, is what I'm having problems with.

Any pointers

[eluser]neilmcgann[/eluser]
Convert the dates you want to compare against into unix timestamps and then just use normal numeric comparisons to compare those against the saved timestamps?

I'd have just used a date or datetime field in the DB and then all the normal sql date & time comparison functions are usable. The stored datetime would be human-readable too.

[eluser]InsiteFX[/eluser]
CodeIgniter Users Guide - Date Helper

[eluser]gazza7364[/eluser]
Thanks for those who replied, I solved it by searching the net and taken advice from replied post.

What I did is change the date to datetime (as mention in a posted reply) and then use the built in mysql date and time function to make the query work, which is "SELECT * FROM blog WHERE MONTH(date) = 5 and YEAR(date) = 2012"; these numbers can be changed to variables to suit.

Thanks again