SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question INTERVAL syntax question

    I have a simple query (I think). I want to allow a user to specify the interval in days, months and years. I want to put in a standard query and I've got this at the moment:


    Is that the best way to do it? Or is there a better way? It certainly seems to work as it is but I imagine it could be faster

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    Is that the best way to do it?
    no

    when you apply a function to a column, like DATE(updated), you do not get the benefit of indexed retrieval

    better is to put the column on one side of the operator, and the calculations on the other

    i suspect you can simply do
    Code:
    WHERE updated > ... some calculation
    without worrying about the time component

    as for the date calculations, be careful

    if today is june 5th, then 5 days ago is may 31st, and 3 months before that is february the what?

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

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK, I hear what you're saying. I was previously told to do it that way around, but I never understood why. Can you come up with a better way for me to do the calculation though? In all likelihood it will only ever use the month value but the option has to be there for further fine tuning. I'm using it to define an archive date. At the moment we just move jobs that are over 6 months in to an archive table as that data is rarely used any more.

    I don't like your example about the dates though. What happens if I just use INTERVAL and we go back in to a date that doesn't exist? It works fine, MySQL is designed to handle it fine. When it's selecting rows that are dated prior to 'February 31st' then it will find everything up to the end of the month and then nothing else. That's what I expect it to do


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
  •