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.
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
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:
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.