{"id":22,"date":"2008-04-09T16:15:22","date_gmt":"2008-04-09T21:15:22","guid":{"rendered":"http:\/\/osric.com\/chris\/accidental-developer\/?p=22"},"modified":"2008-04-10T08:48:17","modified_gmt":"2008-04-10T13:48:17","slug":"checking-for-date-time-conflicts","status":"publish","type":"post","link":"https:\/\/osric.com\/chris\/accidental-developer\/2008\/04\/checking-for-date-time-conflicts\/","title":{"rendered":"Checking for date-time conflicts"},"content":{"rendered":"

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:<\/p>\n

\"Conflicting<\/p>\n

There are 5 unique conflicts possible, and it seems like I’m always forgetting one.<\/p>\n

Here’s a ColdFusion function that I’ve been using to check the database for conflicts:<\/p>\n

\n

<cffunction name=\"hasConflicts\" access=\"private\" output=\"false\" returntype=\"boolean\" hint=\"Determines whether the specified start and end times have any conflicts\">\r\n<cfargument name=\"startTime\" type=\"date\" required=\"true\" hint=\"Start datetime timestamp\">\r\n<cfargument name=\"endTime\" type=\"date\" required=\"true\" hint=\"End datetime timestamp\">\r\n\t\r\n\t<cfset var getConflicts = \"\">\r\n\t<cfset var myResult = TRUE>\r\n\t\r\n\t<cfquery name=\"getConflicts\" datasource=\"#THIS.ds#\">\r\n\t\tSELECT id\r\n\t\tFROM reservations\r\n\t\tWHERE (\r\n\t\t\t(\r\n\t\t\t\tstart_timestamp >= <cfqueryparam value=\"#ARGUMENTS.startTime#\" cfsqltype=\"cf_sql_timestamp\"> AND \r\n\t\t\t\tend_timestamp <= <cfqueryparam value=\"#ARGUMENTS.endTime#\" cfsqltype=\"cf_sql_timestamp\"> \r\n\t\t\t) OR ( \r\n\t\t\t\tstart_timestamp <= <cfqueryparam value=\"#ARGUMENTS.startTime#\" cfsqltype=\"cf_sql_timestamp\"> AND \r\n\t\t\t\tend_timestamp >= <cfqueryparam value=\"#ARGUMENTS.endTime#\" cfsqltype=\"cf_sql_timestamp\">\r\n\t\t\t) OR ( \r\n\t\t\t\tstart_timestamp < <cfqueryparam value=\"#ARGUMENTS.endTime#\" cfsqltype=\"cf_sql_timestamp\"> AND \r\n\t\t\t\tend_timestamp >= <cfqueryparam value=\"#ARGUMENTS.endTime#\" cfsqltype=\"cf_sql_timestamp\"> \r\n\t\t\t) OR ( \r\n\t\t\t\tstart_timestamp <= <cfqueryparam value=\"#ARGUMENTS.startTime#\" cfsqltype=\"cf_sql_timestamp\"> AND \r\n\t\t\t\tend_timestamp > <cfqueryparam value=\"#ARGUMENTS.startTime#\" cfsqltype=\"cf_sql_timestamp\">\r\n\t\t\t)\r\n\t\t)\r\n\t<\/cfquery>\r\n\t\r\n\t<cfif getConflictingReservations.recordCount EQ 0>\r\n\t\t<cfset myResult = FALSE>\r\n\t<\/cfif>\r\n\t\r\n\t<cfreturn myResult>\r\n<\/cffunction><\/code>\r\n<\/pre>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading Checking for date-time conflicts<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[349,25,23,24],"class_list":["post-22","post","type-post","status-publish","format-standard","hentry","category-coldfusion","tag-coldfusion","tag-date","tag-sql","tag-timestamps"],"_links":{"self":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/22","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/comments?post=22"}],"version-history":[{"count":0,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/22\/revisions"}],"wp:attachment":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/media?parent=22"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/categories?post=22"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/tags?post=22"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}