Find intersection time between one date range and one time range

I am looking for a mysql query that will take a start date/end date and a start time/end time and 开发者_开发技巧compute the amount of time(in seconds) that the time range intersects the date range.

For example,

start date to end date: 2011-09-01 18:00:00 to 2011-09-01 19:00:00

start time to end time: 6pm to 8am

In this case the amount of time should be 3600 seconds.

Mysql: Datediff http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

MSSQL: Datediff http://msdn.microsoft.com/en-us/library/ms189794.aspx

Oracle: Datediff http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_techref/maxl/dml/funcs/datediff.htm

i think you meant 6pm to 7pm anyways:

SELECT ABS(UNIX_TIMESTAMP('2011-09-01 18:00:00') - UNIX_TIMESTAMP('2011-09-01 19:00:00'))

or

SELECT ABS(UNIX_TIMESTAMP(`end_date`) - UNIX_TIMESTAMP(`start_date`))