Deciding what data types to use to store date and time information can be more complicated that other data types. Not only are there multiple date-time data types, each type stores a different amount of data, and is may be a more apprpropriate data type in different situations.
Appropriate Uses for Date Data Types
Three standard date data types have already been introduced:
- date: date only
- datetime: date and time in single column (where time includes milliseconds and nanoseconds)
- smalldatetime: date and time, where thee time is limited to hours, minutes and seconds
More information on the various date-time data types in MSSQL can be found at the following link: Date and Time Data Types and Functions.
Each of these data type may be appropriate in different situations. For example, when wanting to store something like a date of birth, or an event date, where only a date is needed, the simple date datatype is sufficient.
In cases where the time is also useful - such as an meeting date and time, for instance - a smalldatetime provides the ability to store both the date and time.
The datetime data type allows for a more granular time value, which includes milliseconds. This might be useful to store finishing times for runners in a race, for example. Another common use is in manufacturing and other industrial cases, where a table might bbe used to track when items where produced or stamped, for example.
Another Special Date Data Type
All of the above date data types are considered "local" date-time types. In other words, the data and time that is stored pertains to a single time zone. If a meeting date-time of "2019-06-01 10:00:00" is stored for a meeting in New York City, the time is not relevant to someone in California. If the time is for an online conference call, users in California would have to know the time is based on Eastern Standard Time (EST). If they want to join the meeting, they would have to calculate the "local" time for them as 7:00am.
But dates and times can be a problem in a globally-connected world. If users from all over the world want to join an online meeting - how do they know what they time should be? Perhaps the web page says "Start Time: 1:00pm". 1:00pm based on whose time?
There are many, many situations like this where a date-time depends on where the user is located. This means that the date-time value needs to be stored in such a way that it can be adjusted, as needed.
The datetimeoffset data type is the appropriate data type for these situations. A datetimeoffset date-time is stored as the date and time in Greenwich, England. This is referred to as Coordinated Universal Time or UTC. Storing the date in this way allows applications in any time zone to easily adjust the date based on the "local" time and the number of hours it differs from UTC.
Consider an online training seminar that will be held in Seattle, Washington, USA on June 1st, 2019, at 2:00pm. The company presenting the seminar posts this date and time on their web site. In the database, however, the company has designed the events table to store the date and time as a datetimeoffset column. So the value in the datebase table is "2019-06-01 22:00:00" This is the UTC date and time. (Seattle is on Pacific Standard Time (PST), 8 hours earlier, or -8 hours from UTC during the summer, so 10:00pm UTC = 2:00pm PST).
For web site users, the event date is adjusted based on the time zone of the user's browser. This means that a user in New York City will see the same date as 5:00pm EST (New York is -5 hours from PST in the summer.)
What is a user from China wants to join the seminar? China is +8 hours from UTC. In this case, that means that a user in China would want to join the seminar on June 2nd, at 6:00am!
Calcuating UTC and Local Time
While it may seem confusing to calculate what the local date-time is for a UTC date-time, the process is mostly built into most software applications. In SQL Server, for example, the database only needs to know the time zone name in order to make that calculation. The database will determine the number of hours difference of the time zone from UTC, and will take into consideration whether daylight savings time is in effect in that timezone. All web browsers, provide their current time zone name to applications that want it.
The example above illustrates why it is so important to consider how dates and times should be stored in a database. For any cases, where users may need to see or use dates and times across time zones, date-time values should be stored as a datetimeoffset data type, instead of another date type.