If Time Types were like Spatial Types

Spatial types in SQL Server comply with OGC standards, and are nothing like the other types in SQL Server.

Geography types (mapping types) have a long history and an industry of their own, long before they turned up in our databases. This makes them special. How special?

What if DateTime was implemented in the same way as Spatial Types?

Large corporations would have a Chronology Department, or at least a Chronology Team, and it would employ professional Chronologists. Chronologic Information Systems (ChIS) would be different from databases, and the introduction of chronological concepts into a database would only be acceptable if you implemented the full set of types and methods recommended by the Open Chronological Consortium. (Picture a Mafia, only with pocket watches.)

If any department is discovered doing work that involves date and time, they would be referred to the Chronological Information Systems department, who are the only people in the company authorised to make Chronological Products, such as calendars, schedules, timelines and other time-related artefacts. If you would like to display a form that includes a date-time you won't have any software that does this on your own machine, and will need to ask a Chronologist to help you out. They will also give you advice on the use of colours. For free.

Once Date and time concepts do make it into the database, you might be surprised. There would be no DateTime type. No no, that would be laughably simplistic and not something that real Chronologists would ever consider worthy of existing on its own.

Instead there would be one umbrella type called a chronology, and a chronology can contain any of the following:

• A point in time
• A range in time
• Recurrences of the above
• A collection of any of the above, including collections of collections.

A chronology object has its own Calendar identifier and UTC offset. There are ways of converting between these, but only theoretical chronologists really understand them. Your average working chronologist pretends to understand this stuff, but never uses it.

The chronology object includes standard functions for all of the operations that a chronologist would need to achieve:

The total duration of a recurring appointment, intersected with a time range? Oh you mean good old:

Chrono.STDuration(chronology::STChronoFromText('RANGE(2014-01-01 00:00:00.000,2015-01-01 00:00:00.000)', 1337))

The length of time by which two meetings overlap? Ah, you mean:

Meeting1.Chrono.STOverlap(Meeting2.Chrono).STDuration

And so on.

As always I'd better stop myself now, since...

"I'm having trouble keeping my clever schemes separate from my sarcasm."

(This week's Just Wally cartoon 'inspired' by this Dilbert cartoon.)

My book "Choose Your First Product" is available now.

It gives you 4 easy steps to find and validate a humble product idea.

Learn more.