UNIX Timestamp Vs. MySQL Datetime

[eluser]arthurhanlon[/eluser]
Now, before I start, I don't want to start another "which is better" debate but am asking with regards to my specific situation.

I am developing a returns system for my current workplace and it's looking pretty good so far but when I started, it's purpose was to simply log returns in a fashion that the user could then look back and find them to mark them as received. I now have to build in some basic reporting functionality like amount of returns each month for the past 6 months etc.

The problem I am seeing is that initially, I stored all dates as UNIX timestamps and now can't see a way to easily generate said reports without a bunch of additional PHP logic. I was thinking about switching them all to MySQL Datetime fields as I know you can do some pretty nifty things in SQL with them but was wondering how others were doing this kind of thing?

Also, regardless of the switch, I have drawn a mental blank on how to go about creating the reports. How do I get the 6 months prior to the current one and how would I go about finding the first and last days in each to generate the queries for the reports?

Any advice or opinions would be really helpful and much appreciated.

Cheers,
Arthur

[eluser]CroNiX[/eluser]
It would probably be easier to convert them, but you can also select a unix timestamp as a datetime. You can use "SELECT FROM_UNIXTIME(timestamp)". Im a bit short on time right now, but this will allow you to use regular mysql date calculations, which will really help you do what you're trying to do here.

Here's an older tutorial to help show you how to use some of them.

[eluser]arthurhanlon[/eluser]
CroNiX,

What you have provided here is more than enough for now, thanks loads. I also found this post http://www.phpbuilder.com/board/showthre...t=10290647 to give me an idea of how to grab totals for each month.

Any idea on what kind of overhead performance wise FROM_UNIXTIME has over natively handling the datetime?

Just out of curiousity, what do you tend to use?

Cheers,
Arthur