This post continues our look at date and time data types in SQL Server.
SQL Server 2008 introduced new data types to handle dates and times in a more intelligent way than the DATETIME and SMALLDATETIME types that we looked at previously.
What is the time?
This week, we look at the TIME data type. It is formatted as HH:mm:ss.nnnnnnn, where HH is hours between 0 and 23, mm is minutes between 0 and 59, ss is seconds between 0 and 59, and n represents 0 or more fractional seconds, up to a maximum of seven decimal places.
With a maximum length of 5 bytes, TIME can store a value with a granularity of up to 100 nanoseconds.
What’s a nanosecond?
Imagine if you took a second, and split it up into 1,000 equal parts. Those parts are called milliseconds (one one-thousandth of a second). Imagine taking one millisecond and splitting it up into 1,000 equal parts. Now you have a microsecond (one millionth of a second). A nanosecond is one one-thousandth of a microsecond. There are one billion nanoseconds in a single second.
SQL Server and Azure SQL Database use TIME (and, as we’ll see in other posts, DATETIME2) to store time values as small as 100 nanoseconds, or 0.1 microseconds. That’s one ten-millionth of a second.
With up to 5 bytes to play with, we can calculate how many possible values this data type can store. 2 ^ (8 * 5) gives us 1,099,511,627,776 values, or just over 1 x 10^12. According to Microsoft Docs, TIME stores values from midnight to 100 nanoseconds before midnight on any given day. With ten million values per second, and 86,400 seconds in a day, we have 864 billion possible values, which means a small amount of overhead in the data type.
What if we don’t need 100 nanosecond accuracy?
One of the most interesting aspects of the TIME data type is that it has a variable length. As discussed previously, this is called a scale. In other words, we’re telling the storage engine that we only need a certain accuracy, or granularity, for our time values. The storage engine can then store these values more efficiently, depending on the scale.
Scale of a TIME value
When declaring a TIME data type in SQL Server or Azure SQL Database, we can define the scale as follows:
| Scale | Accuracy (in seconds) | Bytes used |
TIME (no scale) |
0.0000001 seconds | 5 bytes |
TIME(0) |
1 second | 3 bytes |
TIME(1) |
0.1 seconds | 3 bytes |
TIME(2) |
0.01 seconds | 3 bytes |
TIME(3) |
0.001 seconds | 4 bytes |
TIME(4) |
0.0001 seconds | 4 bytes |
TIME(5) |
0.00001 seconds | 5 bytes |
TIME(6) |
0.000001 seconds | 5 bytes |
TIME(7) |
0.0000001 seconds | 5 bytes |
Note that if we leave out the scale, the default uses a scale of 7, with full 100-nanosecond accuracy.
Next week, we will look at the DATETIME2 data type. In the meantime, you can find me on Twitter at @bornsql.
Photo by Ricky Kharawala on Unsplash.