DateTime, Date and Time – SQL Smells

Date and Time - Point or Period - An SQL Smell?
Date and Time – Point or Period – An SQL Smell?

Phil Factor identifies several SQL Smells associated with the use of DateTime, Date and Time data-types. Using the wrong types will waste space, harm performance and create “odd” behaviour.

If you are clear about what you are recording, you will avoid these issues. I prefer to say what I want, and let an expert choose the best date-types. In other words, I prefer to separate the analyst and designer roles. That way I avoid suggesting the wrong types.

DateTime, Date or Time – Which do you need?

DateTime columns which contain only Date or Time contribute two of SQL Smells. This wastes space, both in storage and in memory (which will degrade performance).

There is something worse here. Using DateTime in this way suggests we are not clear about what we want. This lack of clarity encourages the designer to “hedge their bet” by using DateTime.

How precise do you need to be?

Business Systems need to record dates and times, but they don’t need great precision. For many business transactions, the nearest second or even minute is adequate. In some cases recording extra precision can be misleading.

Storing Durations

For a “Period” you need to know 3 things: Start, End and Duration. You only need to store two out of the 3. The third value can always be calculated if you have the other two.

Finding if an Event is inside a Period is easier using Start and End
Finding if an Event is inside a Period is easier using Start and End

Storing the “start” and “end” is more flexible. It is straightforward to work out if an event took place within a given period.

Finding if Periods Overlap is easy using Start and End times
Finding if Periods Overlap is easy using Start and End times

Telling if periods overlap is easy too.

If you decide to store a duration you must specify the units you intend and the precision you need. (As Phil puts it “milliseconds? Quarters? Weeks?”). Do not be tempted to store a duration in a “Time” data-type! “Time” is intended for “Time-of-day” not duration.

Dates and Times: Choosing the right data-type – Some simple questions

Choosing appropriate data-types for dates and times is not difficult, if you go about it the right way.

Divide the job into two steps: “The Requirement” and the “The Most suitable technical data-type”. Do the two steps separately, taking into account any local standards and conventions.

The Requirement

  1. Is this an “Event” (a “Point in Time”) a “Period” or a “Duration” (both have beginning and an end)?
  2. Event: What is the best way to represent this data? Should it be a “Date” or a “Time”? Does it really need “Date-time”?
  3. Duration: What are the units and scale of this duration?
  4. How precise do you need the value to be? You may surprise yourself.
  5. How long do you expect this system (and the data in it) to last?

The Most Suitable Technical Data-type

Technical Properties of Date and Time data-types in Microsoft SQL Server
Technical Properties of Date and Time data-types in Microsoft SQL Server

Use the table (based on Microsoft’s Documentation) to choose the best data-type for your needs. This table is for SQL Server. Other database managers will have similar tables.

Microsoft recommends using DATETIME2 in preference to DATETIME for new work (it takes up less space and is more accurate). Providing the maximum date is acceptable, I would consider SMALLDATETIME for business transactions (but you do risk creating a “Year 2000 problem” if the data turns out to have a long life).

If your system will span several time-zones, then you should definitely consider the benefits of using the DATETIMEOFFSET data-type.

Summary

The DateTime, Date and Time data-types can all cause SQL Smells when they are used inappropriately. Problems can be avoided by following some simple guidelines.

Where next?

Phil Factor doesn’t like “SELECT *”. Find out why in the next article.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.