Time, its treatment in database

Why quintessential

Time as a unit of measure is not surprising to anyone. We have been measuring it since birth of human cognizance. Philosophically the quantity called time has been at the centerstage of everyone’s lives. However, as a fundamental dimension it was in the recent past we have taken cognizance of time. As a developer who earns a living by writing code for businesses. The word dimension is interpreted differently based on the context. To quote an example if we are working in the world of graphics, let us say 2-dimensional, we will have x, y pixel as dimension. Whereas, if we were working on a model to detect insurance fraud we will be talking about dimensions as social interconnectedness, claim, geographic, and time as dimensions. In fact in many cases we might even loosely use the word factor and add the adjective principle or prime or primary to sound similar to dimension. Amongst all this chaotic and loose interpretation one thing that is consistently understood without yielding to misinterpretation is time. It is such an quintessential representation of the word dimension that no matter which field of work or body of knowledge already known and documented you pick, you will have reference of it; invariably.

In the world of software development

We have couple of tools in our arsenal that deal with this quintessential dimension. To begin with we have the most humble and atomic tool – Data type. If you are versatile with the languages, you will concur time as a data type is present in all languages with varied degree of specificity. For example some language make treat the time data type superficially whereas some other programming language might treat it in depth that you could control not just storage you can also specify accuracy in presentation and offset it for different time zones. Rising upabove data type you will notice functions that operate on such data type. Even there we have a spectrum of support in different languages. Above that you will have libraries that encapsulate these data types and functions and offer you much higher level of value that a developer working for business can understand and operate upon. Above that you can notice software and features in software that is built specifically to work with temporal dimensions. Puzzled on the scale of things?

Let us give those words shape; by picking any one language and throwing in examples for all those levels. Let us pick SQL and more specifically T-SQL. By reading the last sentence if you have developed any apprehension, stop here. Remember there is life for developer beyond Python, Java and C#. As a full-stack you must be conversant and treat database query languages honorably. We from the dotcom era had to develop similar respect to front end language like JavaScript, and HTML. But for generation of developers after the dotcom era I guess that will not resonate. These days such languages are considered bleeding edge and more cool work with isn’t it?

Treatment of time in data types

As data type we have spectrum of capabilities in handling time in T-SQL. We have base data type for date and time separately. Combined together we have datetime. To capture the how we measure time in real world i.e., with offset for UTC we have datetimeoffset. Now, within the bucket of datetime we have datetime2 and smalldatetime which have been developed out of history and offer different scale of precision to operate this data type. If you take a look at the official documentation  for these data types you will be perplexed that there is so much to notice and notice about this type.

Once you understand the basics you will look for the representation. That is a whole lot another page in its documentations. With different culture and way of depicting time, these datatypes have stretched themselves to cater to these wide audiences.

Then you have the case of portability. Data within the databases that support T-SQL is great but it has no value until it is transported to applications which work with them. To do such transport these data types offer the serialization approaches and mapping to data types in other programming languages where these data types are consumed.

If we go in-depth in this aspect we will find ourselves a rabbit hole in the wonderland of time.

Treatment of time in functions

T-SQL has inbuilt functions for various data types. Needless to say it has for the date and time based data types as well. Spread of features there offers the basic operations of determining the part of time dimension to performing mathematical operations. Official documentation categorization of these functions are

Amongst this 6 buckets of date and time data type there are on average of 2-3 functions. Such is the need and I am pretty sure when you worked different projects you would have created user defined function that are built atop of these functions to tailor your specific need.

Tables; temporal tables

This space just keeps getting interesting. Temporal tables are coolest complex data types and we would say higher order database objects over data type and function made available in SQL server since 2016. Let us get introduced to this gem of feature on dealing with time. You have worked with data at a point in time. When you need to capture the change you capture the data again at point in time then store them in table. You definitely are familiar with the 4 administrative columns that all the tables have CreatedBy, CreatedDateTime, ModifiedBy, ModfiedDateTime. These columns have been part of our tables for time immemorial. Temporal table makes them more system and specifically database engine feature than being a user-defined column that needs to be defined on table. Temporal table captures the change in data as system-versioned table. You will create one using T-SQL like this –

The last 3 columns, [ValidFrom], [ValidTo] and PERIOD FOR are the language specification and not convention anymore. The convention related to [CreatedBy] and [ModifiedBy] has a different approach to adopt language specification but we will use a different article to talk about that.

If the syntax above have triggered your alarms; Snooze it for some time. You will query such tables as follows –

Does it snooze your alarms? Well, in case you are caught between sentences on what is happening? Let us make it explicit. The temporal table needs 2 tables. One to store the active or recent data. Another to capture the history of the data. Now, one reason why developers getting introduced to this get alarmed is because of the explicit mention of 2 tables. The question that pops up in their heads is does it mean querying becomes complex. That complexity is taken care by the snippet –

Temporal table are system versioned time snapshots of data. Though while creating the table we mention where to put the historical value, querying syntax is seamless. There are cautions that one must exercise for these and never plug real-time data to temporal table.

The next level of software that has imbibed time at its core offering is a ocean of topic. We will pick that in our future dispatch.