Ask a Jedi: Abstract date ranges and search

For privacy reasons, when folks send a question to me and I respond on the blog, I only use the first name. I'm getting bored with that so I think may start providing completely made up last names. If this offends anyone, speak up. I won't use this an excuse for unbridled frivolity or anything else unbecoming a Jedi developer.

Gareth Supremepizzapants asks:

Hoping you can point me in the right direction. I want to be able to mimic the date search facility seen at ticketmaster.co.uk where users can search for events occurring 'next weekend', 'next month', 'this week' etc. Can't seem to find any info. I know how to get the current date with Now() but what about the date after today i.e its Thursday and I want to Return all items from today to the of the week on Sunday. Any ideas?

This is an interesting question. Date searches with defined ranges are simple enough. So for example, if you ask the user to provide a start and end date, there isn't much necessary to validate those dates and then restrict the search results. But Gareth wants something a bit more abstract. Users need to be able to search for "This Week" or "This Weekend", etc. While us humans have no problem with this, we have to be a bit more precise when we perform our query. It also becomes more complicated when you consider what you are searching. If you are selling tickets, and the user wants to see tickets for events this week, does it make sense to show results from events earlier in the week? Probably not. If it is Sunday morning and you search for this weekend, do you really want to see a result for the awesome Def Leppard concert last night? Again, the answer is probably not. Here is one way I solved this problem.

First, I created my sample data. I wanted a large set of data so I ended up caching it. Obviously this wouldn't be necessary for a real application.


cfif not isDefined("application.tdata")>
	<cfset q = queryNew("id,title,dtevent","integer,varchar,timestamp")>
	<cfloop index="x" from="1" to="500">
		<cfset queryAddRow(q)>
		<cfset querySetCell(q, "id", x)>
		<cfset querySetCell(q, "title", "Title #x#")>
		<cfset dt = dateAdd("h", x * 3, now())>
		<cfset querySetCell(q, "dtevent", dt)>
	</cfloop>
	<cfset application.tdata = q>
</cfif>

Nothing too complicated here. I create a query of 500 records. Each record has an id, title, and a timestamp for the event. The *3 in the dateAdd helps spread out my events over time.

Now let's build a form. Normally you would have a free form text input and other fields, but in this example I just have my date range filter. Also, I picked 3 arbitrary ranges (This Week, This Weekend, Next Weekend). You could add/remove these as you see fit.


<cfoutput>
<form action="#cgi.script_name#" method="post">
<select name="range">
<option value="thisweek" <cfif form.range is "thisweek">selected</cfif>>This Week</option>
<option value="thisweekend" <cfif form.range is "thisweekend">selected</cfif>>This Weekend</option>
<option value="nextweek" <cfif form.range is "nextweek">selected</cfif>>Next Week</option>
</select>
<input type="submit" name="search" value="Search">
</form>
</cfoutput>

Ok, now for the fun part. How do we convert each of these options into a range of dates? First I'll check to see if the form was submitted, and begin a case statement.


<cfif structKeyExists(form, "search")>

 <cfswitch expression="#form.range#">

Now for each type of search I'll use a case block. The first case block is for this week:


<!--- From now() till end of day saturday --->
<cfcase value="thisweek">
	<cfset start = now()>
	<cfset end = dateAdd("d", 7 - dayOfWeek(now()), now())>
	<!--- redo end to be end of day --->
	<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">
</cfcase>

Notice that the start value is right now. I don't want results from earlier in the week. My end value should be Saturday, end of day. I did this by using dateAdd. The 7-dayOfWeek() thing basically just 'moves' me up to Saturday. I used ColdFusion lose typing then to create a string for that date, 11:59 PM. This is not the only way I could have done this. Since I know I'm going to eventually use SQL, I could have made a date object for the day after end, with no time value, and use a < in my SQL. But this worked and gave me the warm fuzzies.

The next case block was for this weekend. This one was slightly more complex:


<!--- this sat, but not before now(), to end of day sunday --->
<cfcase value="thisweekend">
	<cfset start = dateAdd("d", 7 - dayOfWeek(now()), now())>
	<!--- redo start to be 12:00 am --->
	<cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM">
	<!--- if this is before now, reset to now() --->
	<cfif dateCompare(start, now()) is -1>
		<cfset start = now()>
	</cfif>
<cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">

</cfcase>

I begin by creating a date value for Saturday. I then reformat it to drop the time. Note though that I compare this value to now() and use whichever is larger. The end range worked much like the previous case, except this time I want to end at one minute to Monday morning. My previous code worked for Saturday, so I simply added one more date.

The last case handles next week:


<!--- next sunday, 12am till sat midnight --->
<cfcase value="nextweek">
<cfset start = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())>
<!--- redo start to be 12:00 am --->
<cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM">
<cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 6, now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">

</cfcase>

This is pretty much a repeat of what we had before, just adjusted a bit. Again, there are other ways to get the date values.

So the final step is to simply uses these values in a query. Here is what I used:


<cfquery name="results" dbtype="query">
select	*
from	application.tdata
where	dtevent between 
	<cfqueryparam cfsqltype="cf_sql_timestamp" value="#start#">
	and
	<cfqueryparam cfsqltype="cf_sql_timestamp" value="#end#">
order by dtevent asc
</cfquery>

Obviously your database would be different. And yes, I use cfqueryparam even in 'fake' queries. I've included the full code base below. Some other things to consider: For a ticket seller, you probably don't want to use now() as you minimum range. You probably instead want to only sell tickets for events that are at least one hour away. Even that only applies to events with "Will Call" pick up. For events without them, you probably want an even wider range to handle physical delivery. Another problem would be for a site that sells events over multiple time zones. (Personally I think we should just all use Swatch Time.)


<cfif not isDefined("application.tdata")>
<cfset q = queryNew("id,title,dtevent","integer,varchar,timestamp")>
<cfloop index="x" from="1" to="500">
<cfset queryAddRow(q)>
<cfset querySetCell(q, "id", x)>
<cfset querySetCell(q, "title", "Title #x#")>
<cfset dt = dateAdd("h", x * 3, now())>
<cfset querySetCell(q, "dtevent", dt)>
</cfloop>
<cfset application.tdata = q>
</cfif>
<cfparam name="form.range" default="">
<cfoutput>
<form action="#cgi.script_name#" method="post">
<select name="range">
<option value="thisweek" <cfif form.range is "thisweek">selected</cfif>>This Week</option>
<option value="thisweekend" <cfif form.range is "thisweekend">selected</cfif>>This Weekend</option>
<option value="nextweek" <cfif form.range is "nextweek">selected</cfif>>Next Week</option>
</select>
<input type="submit" name="search" value="Search">
</form>
</cfoutput>
<cfif structKeyExists(form, "search")>
<!--- create date/time ranges based on search type --->
<cfswitch expression="#form.range#">
<!--- From now() till end of day saturday --->
<cfcase value="thisweek">
<cfset start = now()>
<cfset end = dateAdd("d", 7 - dayOfWeek(now()), now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">
</cfcase>
<!--- this sat, but not before now(), to end of day sunday --->
<cfcase value="thisweekend">
<cfset start = dateAdd("d", 7 - dayOfWeek(now()), now())>
<!--- redo start to be 12:00 am --->
<cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM">
<!--- if this is before now, reset to now() --->
<cfif dateCompare(start, now()) is -1>
<cfset start = now()>
</cfif>
<cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">
</cfcase>
<!--- next sunday, 12am till sat midnight --->
<cfcase value="nextweek">
<cfset start = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())>
<!--- redo start to be 12:00 am --->
<cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM">
<cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 6, now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">
</cfcase>		
</cfswitch>
<cfquery name="results" dbtype="query">
select	*
from	application.tdata
where	dtevent between
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#start#">
and
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#end#">
order by dtevent asc
</cfquery>

<cfdump var="#results#"> </cfif>

Raymond Camden

Ask a Jedi: Abstract date ranges and search

Support this Content!

Webmentions