Checking for date-time conflicts

It seems like I’ve worked on a lot of applications lately that involve time slots: room reservation systems, appointment schedulers, and so on. One thing that tripped me (and some other developers) up at first was checking for conflicting time slots. It turns out, there are a variety of possible conflicts:

There are 5 unique conflicts possible, and it seems like I’m always forgetting one.

Here’s a ColdFusion function that I’ve been using to check the database for conflicts:

<cffunction name="hasConflicts" access="private" output="false" returntype="boolean" hint="Determines whether the specified start and end times have any conflicts">
<cfargument name="startTime" type="date" required="true" hint="Start datetime timestamp">
<cfargument name="endTime" type="date" required="true" hint="End datetime timestamp">
	
	<cfset var getConflicts = "">
	<cfset var myResult = TRUE>
	
	<cfquery name="getConflicts" datasource="#THIS.ds#">
		SELECT id
		FROM reservations
		WHERE (
			(
				start_timestamp >= <cfqueryparam value="#ARGUMENTS.startTime#" cfsqltype="cf_sql_timestamp"> AND 
				end_timestamp <= <cfqueryparam value="#ARGUMENTS.endTime#" cfsqltype="cf_sql_timestamp"> 
			) OR ( 
				start_timestamp <= <cfqueryparam value="#ARGUMENTS.startTime#" cfsqltype="cf_sql_timestamp"> AND 
				end_timestamp >= <cfqueryparam value="#ARGUMENTS.endTime#" cfsqltype="cf_sql_timestamp">
			) OR ( 
				start_timestamp < <cfqueryparam value="#ARGUMENTS.endTime#" cfsqltype="cf_sql_timestamp"> AND 
				end_timestamp >= <cfqueryparam value="#ARGUMENTS.endTime#" cfsqltype="cf_sql_timestamp"> 
			) OR ( 
				start_timestamp <= <cfqueryparam value="#ARGUMENTS.startTime#" cfsqltype="cf_sql_timestamp"> AND 
				end_timestamp > <cfqueryparam value="#ARGUMENTS.startTime#" cfsqltype="cf_sql_timestamp">
			)
		)
	</cfquery>
	
	<cfif getConflictingReservations.recordCount EQ 0>
		<cfset myResult = FALSE>
	</cfif>
	
	<cfreturn myResult>
</cffunction>

There might be a more elegant query, but this one works. I’m posting it here for my own future reference, and anyone else that might find it useful.