Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2009
    Posts
    33

    Unanswered: Comparing Time in conditional statement

    hi, I'm looking to include a condition which compares the current time of day during our MYSQL query. However, I'm having some trouble.

    Code:
    $query = 'SELECT * FROM jos_keyword_keys WHERE (state = 0 AND (((DATEDIFF(CURDATE(), StartDate) >= 0 AND DATEDIFF(CURDATE(), EndDate) <= 0) OR (StartDate = 0000-00-00 AND EndDate = 0000-00-00)) AND (StartTime = 00:00:00 AND EndTime = 00:00:00)))';
    	
    	
    $query = mysql_query($query);
    before trying to implement the TIMEDIFF() method I decided to include the default StartTime and EndTime values, however even these don't seem to be working correctly. Is it the :?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why do you have separate date and time columns?

    why do you allow zero values?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    33
    because I require that each attribute be as atomic as possible. The application I've developed should only function during certain times/dates. The 0(state) variable indicates whether this entry should be considered trash or usable.

    Why are you ask?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Schweppesale
    Why are you ask?
    because a single datetime value is much easier to work with than a combination of date and time values

    and what's with the zero values? why aren't you using NULLs?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    33
    so what you're telling me is that it would be easier to write a conditional query which compares both time and date when the values are stored in a single attribute? Kind of like using Name instead of using First Name and Last Name

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Schweppesale
    so what you're telling me is that it would be easier to write a conditional query which compares both time and date when the values are stored in a single attribute? Kind of like using Name instead of using First Name and Last Name
    you actually don't need me to tell you, you can test it yourself

    let's say you wanted all rows between '2009-06-30 09:37' and '2009-07-01 15:55'

    example with one column --

    WHERE datetimecolumn BETWEEN '2009-06-30 09:37' AND '2009-07-01 15:55'

    now it's your turn, show us how to do the same thing when there two columns for date and time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2009
    Posts
    33
    ok, now filter all results by a certain time. Frankly, I don't care how simple the condition statement is. The one that I have now is working fine.

    Next time I need you to critic my database I'll post a UML diagram. All I asked what how to compare two Time columns.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Schweppesale
    ...Next time I need you to critic my database I'll post a UML diagram. All I asked what how to compare two Time columns.
    might be a better idea to post your ERD so that potential issues can be caught before they become nagging painful problems

    like all phases of design getting it right first time is rare, but y'don't try and build something on a dodgy foundation. if the foundations are dicky to start with then that system is going to be a bastard to maintain over time, and thats where the true cost of developer time gets expensive, picking up the pieces working out how the developer does thiungs and then implementing the changes.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2009
    Posts
    33
    alright, I understand what you're saying. My knowledge of MYSQL is shaky at best though. How would I go about filtering my query result should I decide to return only a entries where the time within my TIMEDATE column matches a certain value.
    Last edited by Schweppesale; 06-30-09 at 13:00.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    post #6 seems to cover it
    ie
    where adatetimecolumn BETWEEN 'adatetimevalue' AND 'anotherdatetimevalue'
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jun 2009
    Posts
    33
    correct. My question is how would I filter out a specific time though, rather than both the date and time.

    For instance, if I want to pull the earliest time then compare it to midnight to see how many entries are within the database.

    Normally I'd just take the two times and use a WHERE (Time <= 12:00:00)

    with DateTime both of the Date and Time attributes would be combined; that's really the entire reason why I decided to keep both of them seperate.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Schweppesale
    that's really the entire reason why I decided to keep both of them seperate.
    you should have said this in post #3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could always try to EXTRACT the time from the datetime value
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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