c# - DateTime, the Epoch and DocumentDb

Question

So I read this very interesting blog on working with datetime in Azure DocumentDb. The problem being that, right now, Azure DocumentDb does not support range search on datetime fields. The reason for that is that DocumentDb is based on json and that has no datetime type, therefore one usually puts it in a string of xml datetime format.

(obviously Mongo does not have that issue, it's bson format adds the datetime type (among others))

Anyway, the article describes storing the datetime in json in an epoch (unix) time, essentially storing the datetime as an amount of seconds since 01-01-1970. One problem of epoch is that it does not take leap seconds into account, but I can live with that for now.

My question is that I would also like to store birth dates in such a format. Now I could just take 01-01-1900 as a start date and store the amount of days since that date in an int. While I am pretty sure this would work well, it feels like epoch is a well established concept, but the one for birthdays feels like I am building my own conventions, which is something I generally like to avoid.

Is there any established standard for standardizing date storage as a number? Which date should be the baseline date?

Answer 1

First of all, an update: DocumentDB now supports range indexes on both strings and numbers. You have to set up the indexes correctly for it to work.

Now, to give you a recommendation. I've been successful storing ISO-8601 timestamps as strings. This is the default format used by the DocumentDB SDK for handling DateTime so it's less work than converting to an integer.

ISO-8601 date/time strings have several properties that match your needs.

1. The alpha-numeric sort order is chronological so it works perfectly as expected with query clauses using >, <, >=, <=, and BETWEEN assuming you have a range index of appropriate precision (-1 for full precision);
2. They are human readable so if you are browsing a table, the data makes sense;
3. This format allows for the specification of lower granularity date/time. For instance, you should say "2015-03" to mean the month of march, or "2015-03-24" to mean March 24, 2015. You can then issue a query with this filter "startedOn >= 2015-03-24 AND startedOn < 2015-03-25" to find everything that started on March 24, 2015. This works even when startedOn is stored as a full ISO-8601 string like "2015-03-24T12:34:56.789Z" due to the nature of string comparison.

I've written about this approach here.