The Accidental Developer

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.

Tags: ColdFusion, date, sql, timestamps

5 Comments to Checking for date-time conflicts

1. 

   I must change the formatting for code. This looks ugly!
chris on April 9th, 2008
2. 

   See, I have been having the same problem.
   If you throw a

    tag around it you will do a little better, but its still a pretty big issue.
giblfiz on April 10th, 2008
3. 

   hrmm. 20 seconds on google: http://lorelle.wordpress.com/2007/02/23/wordpress-plugins-that-help-you-write-code/
giblfiz on April 10th, 2008
4. 

   I installed the plugin. If you wrap it in a <pre> block and then a <code> block it looks better, although the lines are too long for this puny 450px width. Looks like it’s time to update the site styles as well and give ourselves a little elbow room.
Chris Herdt on April 10th, 2008
5. 

   great article. I will bookmark this
ColdFusion consulting on September 30th, 2009

Leave a comment