Considering UTC And Recording Activity Streak Data In ColdFusion
For as long as I can remember, I've been storing Date/Time values in UTC (Coordinated Universal Time) within my ColdFusion applications. UTC is "the primary time standard by which the world regulates clocks and time" (source). Date/Time values get stored in UTC and then - as needed - translated back into a given user's timezone during the rendering process. This is why, when I added the concept of "workout streaks" in Dig Deep Fitness (my ColdFusion fitness tracker), my initial instinct was to use UTC. Unfortunately, this approach quickly broke down.
Consider a fitness enthusiast living in the Eastern timezone of the United States. At the time of this writing, the Eastern timezone is "GMT-4"; that is, we're 4-hours behind GMT. This fitness enthusiast wakes up at 5am and gets their first workout done. Then, at the end of the day, at 10pm, they get in a quick second workout.
For them, 5am and 10pm are the same day. But, if you view these times through a UTC lens, these translated times fall on two different days:
- 5am EST + 4 => 9am UTC
- 10pm EST + 4 => 2am UTC - the next day
Now, if I was just storing date/time values, this wouldn't matter. But, in this case, since I am trying to store "activity streak" / "behavior streak" data, it's critical that both of these times get classified a the same day from the user's perspective.
In order to properly store these date values, I have to handle them in the user's timezone - not the UTC timezone. And, since my ColdFusion server doesn't actually know anything about the user or where they are in the world, I have to use a little JavaScript to sneak some user-related data into my form submissions.
Let's take a look at a simplified version of my "Start Workout" form. Since my "streak" data relates to workouts, the "Start Workout" workflow acts to both start a new workout and record a "streak entry". In the following code, note that I am using JavaScript to update a hidden form field:
<cfscript>
param name="form.submitted" type="boolean" default=false;
param name="form.offsetInMinutes" type="numeric" default=0;
if ( form.submitted ) {
// Get the UTC time on the server (which is always running in UTC).
serverNow = now();
// Using the offset provided by the user's browser (via JavaScript), we can
// convert the UTC timestamp back to a user-local timestamp. This will allow us to
// properly classify / bucket their activity streak data.
userNow = serverNow.add( "n", form.offsetInMinutes );
}
</cfscript>
<cfoutput>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8" />
</head>
<body>
<h1>
Start a New Workout
</h1>
<form method="post">
<!--- This input value to by DYNAMICALLY CHANGED at runtime. --->
<input type="hidden" name="offsetInMinutes" value="0" />
<input type="hidden" name="submitted" value="true" />
<button type="submit">
Get Swole!
</button>
</form>
<cfif form.submitted>
<hr />
<ul>
<li>
<strong>UTC Time:</strong>
#dateTimeFormat( serverNow, "yyyy-mm-dd '... at' hh:nn TT" )#
</li>
<li>
<strong>Local Time:</strong>
#dateTimeFormat( userNow, "yyyy-mm-dd '... at' hh:nn TT" )#
</li>
</ul>
</cfif>
<script type="text/javascript">
var now = new Date();
// NOTE: The timezone offset is the number of minutes we have to ADD TO LOCAL
// in order to calculate UTC. However, the value we want is actually the
// inverse: what do we have to ADD TO UTC in order to get back to the user's
// local time? As such, we want to send through the NEGATIVE value in our form
// submission.
var offsetInMinutes = - now.getTimezoneOffset();
// INJECT the offset into the hidden form field so that we can use it on the
// ColdFusion server to calculate the user's local time.
document
.querySelector( "input[name='offsetInMinutes']" )
.value = offsetInMinutes
;
// Debugging.
console.group( "User's Time" );
console.log( ...highlight( "LOCAL:" ), now );
console.log( ...highlight( "UTC:" ), now.toUTCString() );
console.log( ...highlight( "Offset:" ), offsetInMinutes );
console.groupEnd();
// Logs the given value with a fun styled output.
function highlight( value ) {
return([
`%c${ value }`,
"background-color: gold ; font-weight: bold ; padding: 2px 4px ; border-radius: 4px ;"
]);
}
</script>
</body>
</html>
</cfoutput>
The Date.prototype.getTimezoneOffset() method, in JavaScript, gives us the number of minutes that we would have to add to the local time in order to calculate UTC time. However, we actually want to go the other direction: calculating the user's local time from the UTC time. As such, the value that we want to submit with the form is the inverse of this.
And, when we render this ColdFusion page for the first time, you can see that we are injecting the inverse value into the hidden form input:
As you can see, when I run this ColdFusion page in EDT (Eastern Daylight Time), we're injecting a value of -240. Which means, we'll have to subtract 240-minutes from UTC in order to generate a date/time value in the user's local timezone.
Now, when I submit this ColdFusion form, along with the hidden offset-in-minutes, you can see that I am able to generate both the UTC time and the user's local time:
Now that I have the user's local timezone offset on the server, I can properly record their activity streak data. My goal here is to bucket each workout into a date (no time) that marks an entry in the streak. For this, I have a very simple MySQL database table that contains the userID and the createdAt date:
CREATE TABLE `workout_streak` (
`userID` bigint unsigned NOT NULL,
`createdAt` date NOT NULL,
PRIMARY KEY (`userID`,`createdAt`)
) ENGINE=InnoDB;
Notice that the createdAt column is a date - not a datetime. This is because the time is irrelevant for streak calculations. Also, unlike most other dates in my database, this createdAt column represents the user's local time, not UTC time.
And, since the primary key is the combination of both relevant columns (a compound key), I can safely call this as many times as I need to using, INSERT IGNORE INTO:
<cfscript>
param name="form.submitted" type="boolean" default=false;
param name="form.offsetInMinutes" type="numeric" default=0;
if ( form.submitted ) {
// Get the UTC time on the server (which is always running in UTC).
serverNow = now();
// Using the offset provided by the user's browser (via JavaScript), we can
// convert the UTC timestamp back to a user-local timestamp. This will allow us to
// properly classify / bucket their activity streak data.
userNow = serverNow.add( "n", form.offsetInMinutes );
// Record this new workout in the activity streak log.
createStreakEntry( 123, userNow );
}
</cfscript>
<cffunction name="createStreakEntry" returnType="void">
<cfargument name="userID" type="numeric" required="true" />
<cfargument name="createdAt" type="date" required="true" />
<cfquery name="local.results" result="local.metaResults">
INSERT IGNORE INTO
workout_streak
SET
userID = <cfqueryparam value="#userID#" cfsqltype="cf_sql_bigint" />,
createdAt = <cfqueryparam value="#createdAt#" cfsqltype="cf_sql_date" />
;
</cfquery>
</cffunction>
<!--- ... truncated ... --->
Since the MySQL column for our createdAt data is date, I don't even have to worry about stripping-off the time portion of the input - the database will do that for me automatically. Which has the added benefit of naturally collating several workouts in the same day down into the same activity streak bucket.
It feels strange to store a date that is not in UTC. But, for activity streak / behavioral streak tracking, this makes the most sense. Special shout-out to my co-hosts on the Working Code Podcast who helped me think this through (on next week's episode).
Want to use code from this post? Check out the license.
Reader Comments
IagoLast posted a response to this post over on his blog: UTC considered harmful
He explores the idea of having future dates in which the assocaited timezone is not necessarily stable. It's an interesting read; and, he digs into the differences in how we talk about time.
@All,
I've been letting this rattle around in my brain for the last few days and I'm not loving the idea that some dates are UTC and some are not, but with no differentiation in the naming. I think that non-UTC date should be indicated as such so that there is some signaling to developer that something is different.
I'm considering prefixing such dates with
local. So, instead ofcreatedAt, it would belocalCreatedAt. And instead ofredemptionAt, it wouldlocalRedemptionAt.The term "local" isn't exactly perfect; but, at the very least, it would make the developer (ie, future Me) stop and think about why the column is different. I am leaning on the idea that in ColdFusion there is a
dateConvert()method that uses arguments like"local2utc"and"utc2local". As such, I'm borrowing that prior art to flag that the date as not UTC.Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →