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.

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 :?

why do you have separate date and time columns?

why do you allow zero values?

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?

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?

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

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.

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.

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.

post #6 seems to cover it
ie
where adatetimecolumn BETWEEN 'adatetimevalue' AND 'anotherdatetimevalue'

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.

you should have said this in post #3

you could always try to EXTRACT the time from the datetime value