SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    May 2010
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Date Queries

    Hi there guys,

    Very simple question here. I am writing backend coding in m PHP file to extract data from the database, but would like to write queries to extract data forr a certain date ranges. How would I write queries to extract date for example:


    Current Date -1
    Current Date -3
    Current Date -7
    Current Date -14
    Current Date -30
    Current Month -1
    etc...

    Thnx

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Check out the MySQL manual's Date and Time Functions page for lots of useful stuff:
    http://dev.mysql.com/doc/refman/5.1/...functions.html

    Basically, use DATE_SUB and intervals

    Code:
    SELECT * FROM table WHERE date > DATE_SUB(CURRENT_TIMESTAMP, interval 7 day)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'd use CURRENT_DATE instead of CURRENT_TIMESTAMP in this scenario, dan

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    On the same subject, is it considered bad form to do the following?

    Code:
    SELECT some_field FROM some_table WHERE date > DATE_ADD(NOW(), interval -7 day)
    I mean the -7 day interval (as opposed to using DATE_SUB).
    It does work, but I'm wondering if it's correct

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    interesting that you should make a distinction between "works" and "correct"

    very often an SQL query will "work" (i.e. not produce a syntax error) but give the wrong answer

    me personally, i prefer to do it this way --
    Code:
    ... WHERE datecolumn > CURRENT_DATE - INTERVAL '7' DAY
    the syntax used here is not specific to mysql (whereas DATE_ADD, DATE_SUB, and NOW are all proprietary mysql syntax)

    furthermore, as i said earlier, i'd use CURRENT_DATE instead of CURRENT_TIMESTAMP (which is the standard SQL equivalent for the NOW function) because this will return results that are likely more correct

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    The correcter, the better.

    Thanks for your comments, interesting reading.
    I'm not really planning on supporting anything other than MySQL in my app in the near future, but I always try to use as little proprietary syntaxes). I guess that I failed in this case

  7. #7
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    The function current_timestamp, as defined in the sql standard, should include a time zone displacement. This means that the current_timetamp function in Mysql is not standard compliant.

    There is a standard sql function named localtimestamp that does not include the time zone.

  8. #8
    SitePoint Member
    Join Date
    May 2010
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW, thanks to everyone on all your comments. It has definately helped me out a lot. I will keep all of this in mind when developing my site queries THanks guys


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
  •