SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2007
    Location
    Shropshire, UK
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting items from database using timestamp

    Hi,
    I have a table in my database full of items. In this table is a field called create_date where everytime a new item is inserted a timestamp is also inserted.

    What I need to be able to do is select all of the items that were inserted on a specific month.

    For example:
    If the url to a page is page.php?February I need to show all of the items from the database that were created in February.

    I know I need to use mktime() but am not sure how to structure my sql query.

    Can anyone help?

  2. #2
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi use mktime to make a timestamp for the beginning of the month and then use mktime to make a timestamp for the ending of the month, then use BETWEEN in mysql to select the rows in between those timestamps.
    Have a good day.

  3. #3
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Or just...
    Code:
    SELECT stuff FROM table WHERE MONTH(create_date) = 'February'
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the problem with that, anthony, is that it is inefficient

    any time you apply a function to a column, the optimizer cannot use the index on that column (if any) -- and there should be an index on that column, for this type of search

    note too that create_date just might be an integer containing a unix timestamp, e.g. 1248701820, in which case you would have to say MONTH(FROM_UNIXTIME(create_date))

    darren, you're on the right track, but BETWEEN is not quite right when dealing with datetimes

    do something like this instead --
    Code:
    WHERE create_date >= 1246420800 /* 2009-07-01 */
      AND create_date  < 1249099200 /* 2009-08-01 */
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, that makes much more sense.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.


Tags for this Thread

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
  •