Queries based on Month & Year

[Busch]

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:

$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?

I hope this makes sense...

thanks
busch

[r937]

i personally would use DATE (not DATETIME), because then date calculations are straightforward, whereas with unix timestamps, you always have to convert them, thus making indexes on them unusable too

in your case you would use

select title 
  from images 
 where month(from_unixtime(photo_date)) = 10

[Busch]

thanks for the info, r937

i was under the impression that date calculations were simpler with a unix timestamp. you just add the number of seconds that you need. i don't really know how to do calculations on MySQL timestamps

also, since i made my post i came across doing something like this if you are using a unix timestamp:

// Get title from images uploaded after 2002
$startYear = mktime(0,0,0, date('m'), date('d'), date('2002'));

$sql = "SELECT title FROM images 
        WHERE upload_date > $startYear
        ORDER BY upload_date ASC";
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
    echo $row['title'] . "<br />";
} 

im still experimenting with it all but any input you might have would be really appreciated!

thanks
busch

[r937]

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

... 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)

[Busch]

cool, thanks for your helpful insight, rudy!