The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Queries based on Month & Year

i am building a database that will hold info about images. the images will be organized by month and year when they are shown to the user. for example i will have a page that will give the user this option:View Images From:January '04, February '04, etc...

i am most familiar with using the unix timestamp (ie. 1097655863) and manipulating the date with PHP functions. But now i need to make queries on the database that will be month and year specific.

My question: is it possible to make one query on the database that stores a unix timestamp to get the images that were from, for example, October 2002? I tried something like this but with no luck:

PHP Code:

$sql = "SELECT title FROM images WHERE month('photo_date') = '10'";

Would it better to store my images with a DATETIME value lik 'YYYY-MM-DD HH:MM:SS' or is it possible to break down the unix timestamp and make queries on that value?

yes, it's pretty much six of one or a half dozen of the other, i just find working with dates a lot easier, both conceptually and from the point of view of the sql, than working with seconds

furthermore, no date-to-seconds conversions are necessary for stuff like

Code:

... where date_col >= '2002-01-01'

i have this silly rule of thumb (silly me, eh) that says when working with databases, the less application code the better, and zero application code is best (makes the application more portable to other databases too)