{"id":98,"date":"2010-03-05T17:15:53","date_gmt":"2010-03-05T16:15:53","guid":{"rendered":"http:\/\/www.smartersoftware.de\/wordpress\/?p=98"},"modified":"2010-03-05T17:09:29","modified_gmt":"2010-03-05T16:09:29","slug":"wild-stuff-nines-complement-date-format","status":"publish","type":"post","link":"http:\/\/www.smartersoftware.de\/wordpress\/2010\/03\/wild-stuff-nines-complement-date-format\/","title":{"rendered":"Wild stuff: Nines complement date format"},"content":{"rendered":"
\n

For German readers: Es gibt eine deutsche Version dieses Blogeintrags<\/a>.<\/p>\n<\/blockquote>\n

 
Photo from <\/font><\/em>Picture Taker 2<\/font><\/em><\/a> at flickr.com<\/font><\/em><\/p>\n

This is the first in what I hope to be a series of some wild stuff I have to deal with: Strange formats, encodings and data modeling decisions that I or others come across.  <\/p>\n

This post is about a date format that we are calling Nine\u2019s complement<\/em>. I couldn\u2019t find anything about this on wikipedia, and googling I could only find some mention in an obscure IBM documentation.<\/p>\n

What we found<\/h2>\n

Here\u2019s what we found: In one of SAP\u2019s screens you have a \u2018valid to\u2019 date field. You can enter some date until the business information was valid (like \u201831.12.2006\u2019). When we looked for the corresponding field in the database, the column was easy to find (called VALIDTO), but it contained values like \u201879938768\u2019 that surely didn\u2019t look like a date. Also the column was defined as a CHAR(8) field. CHAR(8) is not unusual in itself for a date column, storing the date in a format like \u2018YYYYMMDD\u2019 so you can sort a date by sorting a number. In that way, \u201831.12.2006\u2019 would be stored as \u201820061231\u2019.<\/p>\n

It took some asking around to figure out what was going on with the strange values we were seeing in the database. What you do is you take the YYYYMMDD-format and subtract that from 99999999. Here is the whole story:<\/p>\n\n\n\n\n\n\n\n\n
Date<\/td>\n31.12.2006<\/td>\n<\/tr>\n
as YYYYMMDD<\/td>\n20061231<\/td>\n<\/tr>\n
 <\/td>\n <\/td>\n<\/tr>\n
8 Nines: <\/td>\n99999999<\/td>\n<\/tr>\n
minus Date-String<\/td>\n20061231<\/td>\n<\/tr>\n
Date in Nine\u2019s Complement<\/u><\/td>\n79938768<\/u><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

In order to convert the values found in the database back into a proper date, you can perform the same operations in reverse (i.e. subtract the value from \u20188 Nines\u2019, and you get a date in the YYYYDDMM-format).<\/p>\n

But why?<\/h2>\n

The \u201cofficial\u201d reason behind this format is that you reverse the sorting order of the dates, so that the most recent date comes first. This way, you can simulate a descending sort order in a database that does not support this concept.<\/p>\n

As we were working with an SAP system and SAP has built an abstraction layer on top of the underlying databases, that may well be the case here. Some of the code dates back to the early 1980s, and databases were quite different back then. Sometimes I think that they even built there own database independent indexing mechanism, although these days most indexes defined in SAP correspond to indexes on the database level.<\/p>\n

However, it is still not clear to me why defining a descending sort order in an index is important . I’ve asked a question on StackOverflow<\/a> about why you would defined a descending order in an index definition, and the answers pointed to reasonably obscure situations that do not apply to the situation we were seeing. Unless there is stuff like clustered tables involved, the ordering of an index shouldn\u2019t be important, if you\u2019re doing an order, a binary search or traversing a b-tree, the order would not have a material effect on performance.<\/p>\n

It would be quite interesting to evaluate the performance impacts of a \u201cnormal\u201d date storage and a regular ascending index. I\u2019m quite confident that there will not be much of a difference. So I can only assume that if there was a reason in the past to do this, the reasoning no longer applies. However, my guess is that someone optimized something that didn\u2019t need to be optimized. Probably some \u201cold COBOL mainframe programmer\u201d from back in the days when all you could do was sequential access to a file \u2026<\/p>\n","protected":false},"excerpt":{"rendered":"

For German readers: Es gibt eine deutsche Version dieses Blogeintrags.   Photo from Picture Taker 2 at flickr.com This is the first in what I hope to be a series of some wild stuff I have to deal with: Strange formats, encodings and data modeling decisions that I or others come across.  This post is […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[23],"class_list":["post-98","post","type-post","status-publish","format-standard","hentry","category-english","tag-wildthings"],"_links":{"self":[{"href":"http:\/\/www.smartersoftware.de\/wordpress\/wp-json\/wp\/v2\/posts\/98","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.smartersoftware.de\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.smartersoftware.de\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.smartersoftware.de\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.smartersoftware.de\/wordpress\/wp-json\/wp\/v2\/comments?post=98"}],"version-history":[{"count":5,"href":"http:\/\/www.smartersoftware.de\/wordpress\/wp-json\/wp\/v2\/posts\/98\/revisions"}],"predecessor-version":[{"id":106,"href":"http:\/\/www.smartersoftware.de\/wordpress\/wp-json\/wp\/v2\/posts\/98\/revisions\/106"}],"wp:attachment":[{"href":"http:\/\/www.smartersoftware.de\/wordpress\/wp-json\/wp\/v2\/media?parent=98"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.smartersoftware.de\/wordpress\/wp-json\/wp\/v2\/categories?post=98"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.smartersoftware.de\/wordpress\/wp-json\/wp\/v2\/tags?post=98"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}