DateTime, Epoch, and DocumentDb

Question

So, I read this very interesting datetime blog in Azure DocumentDb . The problem is that Azure DocumentDb does not currently support range searching in datetime fields. The reason for this is that DocumentDb is based on json and does not have a datetime type, so it usually fits in a datetime format string in xml format.

(obviously Mongo doesn't have this problem, bson format adds datetime type (among others))

In any case, the article describes storing datetime in json in the epoch (unix), essentially storing datetime as the number of seconds from 01-01-1970. One of the problems of the era is that it does not take into account the seconds of the jump, but I can live with it at the moment.

My question is that I would also like to store birth dates in this format. Now I can just take 01-01-1900 as the start date and save the number of days from that date in int. Although I’m sure this will work well, it seems that the era is a well-established concept, but one of the birthdays seems to be building my own agreements, which I usually avoid.

Is there an established standard for standardizing storing dates as numbers? What date should be the base date?

Answer 1

First of all, the update: DocumentDB now supports index indices for strings and numbers. You must properly configure indexes for them to work.

Now to give you a recommendation. I managed to save the ISO-8601 timestamps as strings. This is the default format used by the DocumentDB SDK to handle DateTime, so it works less than conversion to integer.

ISO-8601 date and time strings have several properties that suit your needs.

• The alphanumeric sorting order is chronological, so it works fine, as expected, with sentences using>, <,> =, <= and BETWEEN if you have a range index of the appropriate accuracy (-1 for full accuracy);
• They are human-readable, so if you are viewing a table, the data makes sense:
• This format allows you to specify the date and time of less granularity. For example, you should say that “2015-03” means the month of the march, or “2015-03-24” means March 24, 2015. Then you can send a request with this filter "startOn> = 2015-03-24 And started On <2015-03-25" to find everything that started on March 24, 2015. This works even when started. It is stored as a full line of ISO-8601, for example, "2015-03-24T12: 34: 56.789Z" to the character string comparison.

I wrote about this approach here .

Answer 2

Teo's answer is correct, except that I suspect that I am “well-established,” billions of Microsoft Excel, LibreOffice, and Lotus 1-2-3 spreadsheets with their own era can significantly exceed the use of Unix Time. Or a billion Apple Cocoa devices and computers with their own era.

Keep in mind that a couple of dozen different eras have been used in various computer environments. Unix time is far from being lonely or even dominant.

Also keep in mind that Unix time certainly does not exist. Variations include the use of whole seconds, milliseconds, microseconds, or nanoseconds.

When possible, use the savvy time data type. Be sure to study the document and experiment to clearly understand its behavior.

If you cannot use the data type, return to using the string in various ISO 8601 formats. Some of these standard formats are sorted alphabetically in chronological order, especially for date-only values: YYYY-MM-DD.

Second seconds are ignored in every date tracking system that I know of. Their goal is to make our watch with a calendar, so for business purposes, Leap Second should be ignored in a sense.

Working with a date is a surprisingly complex and slippery business. Search for StackOverflow to find many problems. Try to avoid overturning your own decisions. For C # in particular, check out the Noda Time library .

Answer 3

In my experience, I have not come across a more "established" standard than the UNIX era. However, some architectural / technological aspects of time storage were discussed earlier: Timestamps and time zones in Java and MySQL

I would ask why you risk using your own convention? This is a risk, because: that if for some time you want to add hours to your account of the day, you may be able to order people based on when they were born during the day. The question can be extended to: what if at some point you want to measure more general or finer-grained moments; you will have to translate your entire function, possibly to many levels of your application, into a more general mechanism / agreement. Another (similar) question: will you always measure once-in-a-lifetime events for people in your database or will they be able to create new, unlimited events? As the number of events increases, the risk of collision increases, and the number of days will not be as suitable as the timestamp measured in seconds or milliseconds.

UNIX time is mostly ubiquitous; you have special methods for getting it in most programming languages. The architecture for saving time, which I will always support and implement in my projects, is as follows: http://www.currentmillis.com/tutorials/system-currentTimeMillis.html

As also pointed out in my answer to the question mentioned above, the advantages of storing time in milliseconds since the UNIX era are:

• Clarity of architecture: the server side works with UTC, on the client side time through the local time zone
• database simplicity: you save a number (milliseconds), not complex data structures like DateTimes
• programming efficiency: in most programming languages ​​you have date / time objects that can take milliseconds from the era of the Epoch when building (which allows you to automatically convert to the time zone on the client side)

---

Because you mentioned C #, DateTime.MinValue comes to mind. It will be mostly year 0 (midnight, January 1).

In addition, it will be some kind of code that will allow you to get millions from your chosen key date (whatever that is), but note that 1900 is still different from the .NET “era” (DateTime.MinValue)

// Unix Epoch (DateTime.UtcNow - new DateTime (1970, 1, 1)).TotalMilliseconds // NTP Epoch (DateTime.UtcNow - new DateTime (1900, 1, 1)).TotalMilliseconds