SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?

    I hope this makes sense...

    thanks
    busch

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    select title 
      from images 
     where month(from_unixtime(photo_date)) = 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    // Get title from images uploaded after 2002
    $startYear mktime(0,0,0date('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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool, thanks for your helpful insight, rudy!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •