Serious MySQL DateTime troubles

[ava]

Hi, I'm looking for a specific MySQL statement.
I have a starttime, an endtime and a filter. I need a procedure that computes the amount of time between the .

Example:
Starttime = 2006-05-09 00:00:00
Endtime = 2006-05-11 00:00:00
filter = (TIME(time)> 08:00:00 AND TIME(time) < 17:00:00)
This should give 18 hours

If I'm not being clear, please let me know and I'll try to be clearer, but I really hope someone can help me.

[chazzy]

you have to explain the logic better. the filter you listed doesn't really make sense. the time in both of those those datetimes is midnight, not sure how you expect to get 18 hours. there's 48 hours difference between the two. even 08:00 on 5/9 and 17:00 on 5/11 isn't 18 hours.

[ava]

Well, basically I'm looking at an entire interval between 2006-05-09 00:00:00 and 2006-05-11 00:00:00. That's 48 hours.
Now, I only want the parts between 8:00:00 and 17:00:00 every day, which means 9 hours per day, which means 18 hours over the entire interval.

[sridhar_423]

subtract the two dates and multiply by 9.

SQL Query ->
select (to_date('12-12-2006 02:00:00','dd-mm-yyyy hh:mi:ss')-to_date('10-12-2006 01:00:00','dd-mm-yyyy hh:mi:ss'))*9 diff from dual

[ava]

I'm sorry for the confusion, and I think I can be clearer this time.

I'm looking for a script/function that can combine the following statements:
MYTIME < 2006-05-12 00:00:00
MYTIME > 2006-04-30 00:50:12
NOT (MYTIME < 2006-05-03 01:11:00 AND MYTIME > 2006-05-03 16:43:44)
TIME(MYTIME) > 13:00:00
TIME(MYTIME) < 17:30:00

and give me an amount of seconds that meets this criteria.

[chazzy]

you're looking for 2 different things. you want the difference in time multiplied by the difference in number of days.

there is no direct way to do it, you could write your own function to calculate it if you want.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

get the minutes, seconds, hours and compute the differences, then do a datediff to get the # of days between.