Skip to main content
Ben Nadel at Scotch On The Rocks (SOTR) 2011 (Edinburgh) with: Chandan Kumar and Sagar Ganatra
Ben Nadel at Scotch On The Rocks (SOTR) 2011 (Edinburgh) with: Chandan Kumar Sagar Ganatra

Bucketing Dates Using floor() In ColdFusion

By
Published in — Comments (1)

In ColdFusion, a date can be represented both as a date and as a number. And while you might easily get through your entire career without knowing about "numeric dates", this CFML language feature has some really neat benefits. For example, we can use floor(date) in order to get the numeric representation of the day on which a date occurs. This allows us to quickly "bucket" a set of dates by day in ColdFusion.

Yesterday at work, I was building a simple bar chart in ColdFusion using CSS Flexbox and "activity" data. Essentially, I was taking 30-days worth of data, slotting each value into a day-based bucket using the aforementioned floor(date) technique, and then charting the count of values in each bucket.

To see how numeric dates make this fun and easy, let's first generate some sample data. For this demo, I created a function that will randomly create activity items between two dates:

CAUTION: This is actually running on Lucee CFML and uses some Lucee-specific syntax (loop times).

<cfscript>

	/**
	* I generate a collection of random activity data between the two given dates. The
	* generated activity can happen at any time of the day on any date between the two
	* dates inclusive.
	*/
	public array function getRandomActivityData(
		required date fromDate,
		required date toDate,
		required numeric count
		) {

		// Using the numeric version of the ColdFusion dates, we can calculate the numeric
		// delta between the dates. Then, generating random dates becomes a matter of
		// picking a decimal value between the two end-dates and then converting that
		// random value back into a native ColdFusion date.
		var from = ( fromDate * 1 );
		var to = ( toDate * 1 );
		var delta = ( to - from );
		var values = [];

		loop times = count {

			values.append({
				createdAt: dateAdd( "d", 0, ( from + ( delta * rand() ) ) ),
				action: "Something happened...."
			});

		}

		return( values );

	}

</cfscript>

Here, we're already beginning to see the magic of numeric dates in ColdFusion. To generate random dates, all we have to do is convert the cap-dates into their numeric format; and then, pick a random decimal value between the two. This gives a random numeric date within the range, which we then convert back to a native ColdFusion date using dateAdd().

But that's not even the fun part of this blog post - that just gets us the data to play with. Now, let's look at how can take that data and bucket it into day-based buckets. The following demo has two phases:

  1. Create a data structure to hold our chart-data. This is going to be a day-based timeline, where each item within the sample data will be bucketed into an entry within this data structure. It's important that we create a full data structure first since we may not have activity data for every day. As such, it's important that we pre-create buckets with no data so that we don't depend on the activity data to drive creation.

  2. Iterate over our sample data, using floor(date) to find the right timeline bucket.

Once we have our activity data bucketed and our timeline calculated, we then graph it using some CSS Flexbox:

<cfscript>

	include "./random-data.cfm";

	// Let's generate 30-days worth of demo activity data. An activity entry can happen at
	// any time of the day.
	maxDate = now();
	minDate = maxDate.add( "d", -30 );
	fakeActivity = getRandomActivityData( minDate, maxDate, 1000 );

	// In ColdFusion, dates can be represented as decimal values, where the integer part
	// of the numeric date represents the number of days since ColdFusion's "Zero date"
	// (not to be confused with Epoch, which is different); and the decimal part of the
	// numeric date represents the time of day. To build up our bucketed timeline of
	// dates, we can convert our min/max cap-dates to integers.
	maxIndex = floor( maxDate );
	minIndex = floor( minDate );

	// Before we examine the demo data, let's build-out our timeline data structure. Each
	// entry in this timeline will represent a single day within the overall time-span. As
	// we built-out our timeline, we're also going to keep an index of each entry that we
	// can immediately access using the numeric version of a given date.
	timeline = [];
	dayIndex = {};

	// By stepping over the numeric dates "1" at a time, each iteration of this loop will
	// be able to address a unique numeric date.
	for ( i = minIndex ; i <= maxIndex ; i++ ) {

		// In this loop, "i" is the numeric date. We can use this to both populate the
		// index as well as cast the numeric date back to a native ColdFusion date by
		// adding "no time" to it.
		entry = dayIndex[ i ] = {
			date: dateAdd( "d", 0, i ),
			count: 0,
			values: []
		};
		// Even though we are adding this value to the timeline array, since structs in
		// ColdFusion are passed-by-reference, we can use the dayIndex to mutate this
		// structure at any time.
		timeline.append( entry );

	}

	// Now that we have our basic timeline data-structure in place, let's loop over our
	// demo activity data and assign each one to one of the day-based buckets.
	for ( record in fakeActivity ) {

		// By converting the ColdFusion date (of the activity) into a floored-number, we
		// get our bucket-index.
		i = floor( record.createdAt );

		dayIndex[ i ].count++;
		dayIndex[ i ].values.append( record );

	}

</cfscript>
<cfoutput>

	<!doctype html>
	<html lang="en">
	<head>
		<meta charset="utf-8" />
		<link rel="stylesheet" type="text/css" href="./styles.css" />
	</head>
	<body>

		<h1>
			Bucketing Dates With floor() In ColdFusion
		</h1>

		<div class="chart">
			<cfloop item="entry" array="#timeline#">
				<div class="datum">
					<div class="bar" style="height: #( entry.count * 5 )#px ;">

						<div class="info">
							Date: #dateFormat( entry.date, "mmm d, yyyy" )#<br />
							Count: #entry.count#
						</div>

					</div>
				</div>
			</cfloop>
		</div>

	</body>
	</html>

</cfoutput>

Now if we run this ColdFusion code, we get the following output:

Bar chart of day-based activity in ColdFusion

As you can see, we successfully bucketed each activity item into the correct day-based bucket using our floor(date) technique. This is the power of numeric dates in ColdFusion.

Also, how awesome is ColdFusion that we can seamlessly move from a script-based syntax for our business logic over to a tag-based syntax for our front-end templating. Freaking magical!

For the sake of completeness, here is the CSS that I used to render the chart. It's not pertinent to the post, necessarily, but I am just keen on CSS Flexbox:

body {
	font-family: sans-serif ;
}

.chart {
	background-color: #ffffff ;
	border: 1px solid #cccccc ;
	display: flex ;
	padding: 4px 4px 4px 4px ;
	position: relative ;
}

.datum {
	background-color: #f5f5f5 ;
	border-left: 2px solid #ffffff ;
	border-radius: 2px 2px 2px 2px ;
	border-right: 2px solid #ffffff ;
	display: flex ;
	flex: 1 1 auto ;
	flex-direction: column ;
	justify-content: flex-end ;
	padding-top: 20px ;
}
.datum:first-child {
	border-left-width: 0px ;
}
.datum:last-child {
	border-right-width: 0px ;
}

.bar {
	background-color: gold ;
	border-radius: 2px 2px 0px 0px ;
}
.datum:hover .bar {
	background-color: #ff3a83 ;
}

.info {
	background-color: #121212 ;
	border-radius: 5px 5px 5px 5px ;
	color: #ffffff ;
	display: none ;
	font-size: 16px ;
	left: 10px ;
	line-height: 23px ;
	padding: 10px 15px 10px 15px ;
	position: absolute ;
	top: 10px ;
}

.datum:hover .info {
	display: block ;
}
.datum:nth-child( 1 ):hover .info,
.datum:nth-child( 2 ):hover .info,
.datum:nth-child( 3 ):hover .info,
.datum:nth-child( 4 ):hover .info,
.datum:nth-child( 5 ):hover .info,
.datum:nth-child( 6 ):hover .info,
.datum:nth-child( 7 ):hover .info,
.datum:nth-child( 8 ):hover .info,
.datum:nth-child( 9 ):hover .info,
.datum:nth-child( 10 ):hover .info,
.datum:nth-child( 11 ):hover .info,
.datum:nth-child( 12 ):hover .info,
.datum:nth-child( 13 ):hover .info,
.datum:nth-child( 14 ):hover .info,
.datum:nth-child( 15 ):hover .info {
	left: auto ;
	right: 10px ;
}

Anyway, happy Friday!

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/4316

Reader Comments

15,848 Comments

As a fast-follow to this post, I wanted to look at using ColdFusion's native dateTimeFormat() and parseDateTime() functions in order to approach "bucketing" from a different angle:

www.bennadel.com/blog/4317-tokenizing-date-time-values-in-lucee-cfml-5-3-7-47.htm

This technique generates "human friendly" buckets, as opposed to the proprietary numeric formats that ColdFusion uses internally. For bucketing, I still prefer the numeric approach because it makes looping over date-ranges dead simple. But, it's good to have more tools in the toolbelt.

Post A Comment — I'd Love To Hear From You!

Post a Comment →

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel