Let’s talk about date and time…

In a billing system, dates and times are a critical part of the system, and yet dealing with date and time certainly has its challenges. Let’s start by saying that Kill Bill has a granularity of a day– which means it will not invoice for something smaller than a day. Let’s take a look at where those dates and time elements appear in Kill Bill.

This all begins at the API level; In most operations, Kill Bill allows to specify both either a date or a fully qualified date, time and timezone, that is, a well defined point in time, or datetime for short. Specifying a date is often easier for the user because one may want to cancel a subscription on the 25th of the month and not necessarily exactly at 2014-10-25T5:48:56.000Z. However, specifying a date also means that this date will be subject to interpretation, because it is imprecise and also varies for different people on different place on the earth. So the user of the API also needs to know how the date is interpreted– more details below. Then, the data generated by the system will also start exposing some dates; for instance, the next date at which the subscription will be invoiced, or the date range for the some specific invoice. Finally, Kill Bill is also driven by future notifications; for instance, a future notification to charge a recurring subscription on its next billing date will happen on a specific point of time, but the point of time needs not to be precise, as long as it is within the 24 hours period of time of that next billing date.

Kill Bill has been designed to support international accounts. Each account — user — will specify a timezone, which will drive the conversion from a datetime into a date when required. Kill Bill will interpret any dates as a 24 hours period of time as seen in the timezone of the account. So, the account timezone is used to convert a datetime into date when necessary. For instance a datetime of 2014-10-01T5:48:56.000Z for a specific account with a PDT (-7:00) timezone would lead to a date of 2014-09-30. Note that in theory, a conversion from a date to a datetime is not possible because the precision is lost. More on that later…

Kill Bill will store both datetimes and dates. Why?

  • Since Kill Bill is an event driven solution — as opposed to most batch oriented billing system –a lot of the operations are driven by future notifications, and so, storing a datetime is mandatory, because the system needs to know at which point of time it should process it,
  • When a user looks at the data generated, for instance its last invoice, she wants to see invoicing period in terms of dates and not some interval between two well defined point of times. We had the choice to store well defined interval of times or to store the resulting dates in the account timezone. We chose the later because this avoids additional transformation and also simplifies the interpretation of the data on disk.

So, that’s that… What is all the fuss about dates and times?

First of all, there is complexity at the API level: If an API takes a date as an input, for instance when changing the current plan of a subscription, then how do we end up with events that are triggered at well defined moment in time? If a conversion from a date to a datetime is not possible how can that work? The way to solve that puzzle is to chose a time, we call it reference time, and Kill Bill will use the time part of the created date of the first subscription. The original date is appended with the reference time in such a way that making the reverse conversion from that new datetime would give us the original date in the account timezone. There are also subtleties, where we don’t want that created datetime to be in the future when the original provided date overlaps with the present. For example, let’s assume the current time is 2014-10-25T5:48:56.000Z; we now receive a cancellation date of 2014-10-24 for a PDT (-7) account, which translates into an interval of time between 2014-10-24T07:00:00.000Z and 2014-10-25T06:59:59.000Z, but choosing a reference time greater than 5:48:56.000 would bring that datetime  in the future, so Kill Bill will prevent it and pick a reference time between 07:00:00.000 and 5:48:56.000Z.

Second, there are a lot of subtleties with date operations. For instance a month is not a well defined interval of time in the sense that it can’t be converted to some number of seconds, since different months have a different number of days. Also, when it comes to arithmetic operations there are a few ‘gotchas': For instance when starting from a given date, if one adds a month and then subtracts a month, there is no guarantee to come back on the same day (August 31st -> September 30th -> August 30th). And there is even worst, because adding a month to January 31th will bring you to either Februrary 28th or February 29th depending on the years. In java we are blessed to have a solid library (Joda) to deal with dates (Thanks Stephen!), but this is not quite the case in ruby…

Third, comes the complexity from the environment. Your server runs with a specific timezone, which you can certainly configure. When starting your JVM, you can also tune the system default timezone — although  for Java the choice  does not include UTC, only GMT, but that level of details is our next point. Then comes your database server, which also has some tuning parameters, each of which could have effect on how date, datetime are stored and retrieved.

Fourth comes the serialization of the dates/datetimes. For all networking operations, we rely on JSON, which makes it easier since we only deal with strings, and we know how to convert them into date or datetime — as long as datetime are fully qualified which means they must include the timezone. On the database level, one also need to be very careful on the choices to represent the dates. Mysql supports various types (date, datetime, timestamp, ..) and those will have specific properties and be sensitive to different tuning parameters. Your database schema is also just one part of the equation because then you need to be very cautious when transforming data from your running code to the disk and vice versa. The JDBC driver offer a lot of different APIs, and choosing the correct one, and using it correctly is not as straightforward as it seems and often requires writing tests to verify the behavior is indeed the one expected. Kill Bill ddl schema relies on ‘date’ and ‘datetime’, both of which are not sensitive to the mysql timezone session/global setting, and yet make it easy to ‘select’ the data from the database without applying any transformation functions.

And finally, there is your code… Kill Bill is using a custom Clock that is injected all throughout the system. This has the advantage i) to unify any convenient set of operations that are used throughout the system and also ii)  very importantly, allows you to mock the time for when writing tests. Most of our tests rely on the ability to play with time, which is quite powerful.

Let’s also remember that time is not a simple topic in itself and so this is reflected in the implementation of the various software components: From the leap seconds which lead to choosing between UTC and GMT (when available), to server time synchronization, there is a lot of room for inaccuracies. In the end, the correct approach needs to rely on solid design principles, carefully written and tested implementation and pragmatic choices to harden the system (leap second for instance).

Ah, and of course when we send Kill Bill in a rocket ship into space, then we’ll also have to deal with general relativity… but that will be another post.

Leave a Reply