Thank you for your replies and link to the MySQL page on Date/Timestamp.
Using SELECT * FROM conversions WHERE date = CURRENT_DATE() - INTERVAL 1 DAY; does not work :S
Just like the other SQL query I mentioned, it says there are no entries from yesterday. Should I work around it using the PGP date function? I know how I can do that but I wanted to get the MySQL date working because its something new ;)
The problem may be the fact that there are times on your dat so it doesn't match yeterday.
SELECT * FROM conversions WHERE date >= CURRENT_DATE():00:00:00 - INTERVAL 1 DAY and date <= CURRENT_DATE():12:59:59 - INTERVAL 1 DAY;
The syntax may be off but the basic idea is you want to see if your dateTime falls in the time range for yesterday. There may be a CURRENT_DATE().earliestTime() or CURRENT_DATE().latestTime() funtion that will return the latet and erliest times of a day but I am not sure.
It will suck for ordering by date and time though if they are seperate fields. It will also make comparisons harder if you split them. Like when you want to query things that come before or after something else based on date and time. Because if it has to consider the time then it is a little bit of a hassle. You have to make sure the date fields are equal, then you can check if the time is before or after a time you need to compare against.
Just a heads up of a few pit falls of splitting your datetime field into 2 seperate fields.