Update: tzTime (documentation) is now available seperately from Lumenize. The examples below still work because tzTime is a dependency of Lumenize and included in that package (as well as Rally's App SDK v2.0p6 or greater). However, Lumenize includes a lot more functionality around time-series and temporal model aggregations, OLAP Cube, etc. If you just want the Time object, just grab tzTime.
What's the correct query clause to get all the transactions from March? The naive answer is "2012-03-01" <= eventTimestamp <= "2012-03-31" but what if the person worked in the eastern US and wanted timezone taken into account? Then the correct answer is "2012-03-01T05" <= eventTimestamp < "2012-04-01T04". Notice how the timeshift for the beginning of March is different from the end. Getting this right is mind bendingly difficult. This blog post and the Lumenize/tzTime libraries were written to make this easy.
Soon after I started generating visualizations for other folks, I got feedback that timezone precision is critical if the insight that the visualization purports to provide is to be trusted. A due date of March 5 is at a different moment in time in India than it is in New York. If your chart says that someone missed their deadline, it better be right. Similarly, if you are excluding holidays and weekends, then the timezone must be considered.
I was working in Java at the time and I adopted the wonderful Joda-Time library to get this level of timezone precision. However, I've since switched almost all of my development over to web technolgies in particular CoffeeScript and JavaScript running on either node.js or in the browser. I got frustrated trying to get the same level of timezone precision using JavaScript's native Date object.
With the move to single-page apps where all of the code is run in the browser and the server essentially acts as a data store, this problem must be more and more common.
So, I developed the Time object in my Lumenize library to provide Joda-Time like functionality in CoffeeScript/JavaScript. This post serves as an explanation of the general concepts that went into the thinking behind Lumenize.Time as well as give you a set of practices that you should use whether or not you are using Lumenize.
-
Record all event data in UTC (aka Zulu) time. The advantage to this approach is that it means all date-time values in the database are in the same time stream and easily comparable. Event A either occured before or after event B. The timezone from which it was recorded shouldn't alter that fact. Recording it in a timezone specific way might change the apparent order.
-
Make any adjustments into the user's timezone at render time. This is necessetated by #1 but the nice thing about this is that the user can change timezones. If I make a phone call appointment when I'm in the east but I happen to be on a trip to the west coast, I want it to show up in local time zone. This is trickier said than done so refer to the Lumenize examples in the "Putting it all together" section below.
-
Record date-time data in the granularity intended by the user. If the user tells you that the due date for the project is March 5th, 2013, then you should only record, 2013-03-05. I am currently working with an organization that has recorded 2013-03-05T23:59:59.000 in their database to indicate this due date. Nevermind that this approach misses the last second of the day. The big problem here is that they are trying to specify a date with a date-time record. There is implied precision in the recorded data that is just not present in the information provided by the user.
-
Start dates are inclusive, but end dates are exclusive. The main advantage here is that it allows you to chain together successive time ranges using the single value as the boundary between one range and the next. To find out if a particular event occured in the month of March, 2012, you would say,
"2012-03" <= eventTimestamp < "2012-04". And, the predicate for the next month starts with the literal for the prior month"2012-04" <= eventTimestamp < "2012-05".Another big advantage is that with our messy Gregorian calendar, it's much easier to identify the start of a time range than the end of one. Consider that months can be either 28, 29, 30, or 31 days long. Consider leap years, etc. Time can also be infinitessimally small. Does the day end on 23:59:59.000 or 23:59:59.999 or 23:59:59.999 + some fraction of a millisecond?
This approach also allows math to work. The number of days between March 10 and March 15 is 15-10 = 5 if you consider the 15 to be an "Ending Before" date and you exclude it. If you think of the 15th as inclusive, then you are really dealing with a 6 day time period and the math is 15-10+1. How often will you forget to include the +1? These off-by-one errors have a small but annoying impact on projection and status calculations.
-
For the end of a time period, or a due date, prompt the user with "Ending Before" or "Due Before". This makes it very clear to the user that the due/end date is excluded… that work is due BEFORE the specified date starts… not somewhere in the middle or the end of the day. This recommendation goes with #4.
-
Label the field in your code and database "endBefore" not "end". This makes the meaning of the date very clear to the developer. Sure, you know what it means, but will the next developer? (S)he could just look at the UI to figure it out, but will (s)he?
-
Shift boundaries from user timezone to GMT before making a comparison or retrieving data from the database. The example at the beginning of recommendation #4 was not not quite right. If you want to be precise, the definition of "March" is going to be timezone dependent. On the east coast you would query for
"2012-03-01T05" <= eventTimestamp < "2012-04-01T04". Note, the beginning of March is in standard time (Z-05:00) and the end of march is in daylight savings time (Z-04:00). Getting nuance like this correct is mind bendingly hard. This is where Lumenize can really help. -
Use Joda-Time for Java and the Lumenize.Time object for JavaScript/CoffeeScript. The native types for Java and JavaScript are poorly designed for productive development with timezone precision. The Java Standard Library has two date types: Date and Calander. The first was considered so bad by developers that they created the second, which was considered so poorly designed that the 3rd-party library Joda-Time is the recommendation. The JavaScript Date object is almost as bad. It tries to be smart and sense the local timezone. But what if you want to render a chart in New York showing the iteration boundaries for your team in India. Do you add 11.25 hours (yes, India has quarter-hour timezone shifts) or subtract? Getting this right is brain-hurt hard for all but a few developers. Another bad decision is their decision to make the month segment be zero indexed. Yes, in JavaScript, February is represented with 1 not 2.
While somewhat different in design and targeted use case, both Lumenize and Joda-Time use the same comprehensive timezone rules files hosted by the Internet Assigned Numbers Authority, which give you the ability to work with timezone precision. Also, they both allow you to work with dates independent of timezone OR within a particular timezone that you explicitly specify, which could be the local timezone setting discerned by your browser, or not.
-
Persist your date-time data in the database as Strings in canonical ISO-8601 format. This recommendation is enabled by a convenient property. The sort order for strings in canonical ISO-8601 format is chronological. Your database may provide a Date type. I'm telling you not to use it. Here's why:
Main advantages:
- Allows for coarser granularity. See recommendation #3 above.
- Allows for specification of time periods, durations, and recurring dates. The ISO-8601 spec includes standard ways of specifying these things as strings. If your application needs a field to serve as either a single date or a recurring date, you're all set.
- Human readable. I'm mostly thinking of NoSQL databases but a string is always readable. The date-time specific type may not be while debugging.
- Compatibility. Not every database implements a Date type and the ones that do are not all the same. I'm mostly thinking of NoSQL databases but even SQL databases have their issues with date-time types. MySQL support zero dates, and Microsoft SQL has incompatible date-time functions. Even the most installed SQL database of all time, SQLite (which is also the basis for Web SQL Database embeded in your browser) does not have a Date or Time type. SQLite recommends that you use either an ISO-8601 string or store a big integer representing milliseconds. BTW, my objections to using milliseconds all the time is that it doesn't have the two other advantages of human readability and allowing for coarser granularity that strings allow.
Potential objections and the counterarguments:
- It will be slow. Using Strings might be slower than using the native Date format but don't assume so. A query "WHERE YEAR(myDateColumn) = 2012" is a very slow full table scan. Even if you avoid this very common mistake, queries against strings can't be truly slow because most database queries are string comparisons. Your database is optimized for it. But even if it is slightly slower, I'd rather optimize for developer speed.
- I won't be able to use the database provided date and time functions. If you are using SQLite, you actually can. They are designed to work on strings. But even for SQLite and other databases, if you are trying to have timezone precision, they are of limited value. YEAR(2013-01-01T01:00:00.000Z) returns 2013, but it's really 2012 in America/New_York. You have to use my recommendation #7 to avoid this.
- My ORM tool won't work with it. In most langauges, most ORMs deserialized to the native Date type which as we've previously discussed are generally broken. Joda-Time provides PersistentLocalTimeAsString functionality for Hibernate. You may be able to find something similar for your stack or be able to write one yourself but, even if you can't get your ORM to automatically persist them as Strings, do so manually, and use Joda-Time or Lumenize whenever you need to work with them.
##Never promise a top 10 list when you can only come up with 9##
Nobody wants a top-9 list anyway. Don't you just love it when a meeting ends early!? You should feel like that now… maybe not. If I know you code-heads, you've already read the section below, but if not, then do so now. It gives you concrete examples of what I've been talking about above.
You can find more detailed examples at the tzTime documentation, but here is a quick rundown of examples that are discussed above.
{Time} = require('tzTime')
Let's work in US east coast timezone. Note, how we specify a location in the tz database rather than an abreviation like EST (Eastern Standard Time). Folks often say something like, "let's meet at 9am EST" even when they are talking about a date in the middle of the summer. They invariably mean 9am EDT (Eastern Daylight Time) but they could have avoided the oxymoron by just saying "east coast time". The tz database avoids this problem by using major cities in each of the over 200 timezones.
tz = 'America/New_York'
You can give ChartTime a string with whatever granularity you like. Here, we are only specifying the month because we are going to build the strings we need to query a database for all events that occured in the month of March, 2012.
marchStartsAt = new Time('2012-03')
The constructor for the Time object is very flexible. Above it takes a partial ISO-8601 string but you can give it JavaScript Date objects, data specifications as JavaScript objects (e.g. {month: 2012, day: 3}), a rata die number, and even relative strings like 'next month'.
The Lumenize.Time object has convenient math operations that are also granularity specific.
marchEndsBefore = marchStartsAt.add(1, 'month')
To get the dates into a format that we could use to submit a query for the month of March, we call getISOStringInTZ() specifying the timezone.
console.log("""
In the #{tz} timezone, in ISO-8601 format, March
starts at : #{marchStartsAt.getISOStringInTZ(tz)}
ends before: #{marchEndsBefore.ISOStringInTZ(tz)}
""")
The above CoffeeScript code will output this below. Notice how the beginning of March is in standard time (Z-05:00) and the end of march (beginning of April) is in daylight savings time (Z-04:00).
# In the America/New_York timezone, in ISO-8601 format, March
# starts at : 2012-03-01T05:00:00.000Z
# ends before: 2012-04-01T04:00:00.000Z
You can also go in the other direction and move between granularities.
someEventInNYTime = new Time('2012-04-01T04:00:00.000Z', 'minute', tz)
oneMinuteBefore = someEventInNYTime.add(-1)
console.log(someEventInNYTime.inGranularity('month').toString())
# 2012-04
console.log(oneMinuteBefore.inGranularity('month').toString())
# 2012-03
Lumenize includes other functionality for creating timelines that knockout weekends and holidays. You can even knock out non-work hours for hour or finer granularity. It has functionality for doing aggregations (pivot-table and full OLAP cube) calculations. Tons of examples, great documentation, and a ton of tests to confirm this tricky stuff is all calculated correctly. So, give it try the next time you need timezone precision.