{"id":84769,"date":"2019-07-15T17:46:11","date_gmt":"2019-07-15T17:46:11","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=84769"},"modified":"2022-04-24T20:33:31","modified_gmt":"2022-04-24T20:33:31","slug":"modeling-time","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/modeling-time\/","title":{"rendered":"Modeling Time"},"content":{"rendered":"

Time is an interesting thing. It moves in only one direction; it\u2019s units of measurement are irregular and constantly in motion. When we go to model it in SQL, it\u2019s usually easier to do lookup tables (calendars) rather than trying to compute it. SQL is not a computational language, but it also must deal with the nature of this dimension. Digital computers are based on discrete<\/em> states, but time by its very nature is a continuum<\/em>. In a continuum, there is always an infinite number of points between any two points in the continuum. This means we\u2019re already in a state of heresy when we try to put time into a digital computer.<\/p>\n

Quantum Models<\/h2>\n

The quantum model is the simplest model, and a timestamp is the most common example. We simply ignore the inconvenience of the continuum and assume that time comes in discrete units or quanta. These quanta are sometimes called \u201cchronons\u201d in the literature. If you want to have a physical instrument for this model, think of a wall clock whose hands jump from one number to another, without sweeping in between them on the face of the clock. Or think about a calendar, whose unit of granularity is a day.<\/p>\n

If you are a fan of kids\u2019 television shows, you might have seen an iCarly episode that featured a new integer between 5 and 6, called \u201cderf<\/a>.\u201d The same gimmick was recently used in an episode of DUST, a science fiction series on YouTube, if you do not to want to admit you watch kids\u2019 television shows. Whether you treat the idea as joke or a scary fantasy, the point is that a temporal model has to be based on discrete values without gaps and a strong ordering for computations.<\/p>\n

But what happens when you have to record a temporal value from the real world which is between quanta with such a scale? You have to assign or ignore a temporal value that is in the set of allowed values. In the Orient, a person\u2019s age in years is a count of the full and partial years that they have lived. A new-born baby is one year old in this system. In the Western world, we only count the full years and use \u201cmonths\u201d if the child has lived less than a year. Or, to put it another way, you can round a temporal value toward the past or toward the future to map it to a quanta value in this model.<\/p>\n

The old DATETIME<\/code> data type in Transact-SQL defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock. The date range is from 1753 January 1 to 9999 December 31. The start date is the beginning of the Gregorian calendar when it was first adopted in Great Britain. The end date is the largest four-digit year that can be represented on it. Other countries adopted it latter times, often years later (for a very detailed history, read THE CALENDAR by David Ewi ng Duncan, ISBN 9781857029796). Today we\u2019re not really on the Gregorian calendar anymore. We use what\u2019s called the Common Era calendar and we\u2019ve replaced A.D. and B.C. with CE and BCE. It\u2019s almost <\/em>like the Gregorian calendar, except it starts at 0001-01-01 and goes through 9999-12-31.<\/p>\n

The time range is 00:00:00 through 23:59:59.997, but the fractional seconds are weird. They are rounded to quanta of 0.000, 0.003, or 0.007 seconds. The reason for this has to do with the original implementation of SQL Server under Sybase. DATETIME<\/code> data was stored in the floating-point format available on the original 16-bit hardware. The mantissa held the date portion, and the exponent held the clock portion. This is why if you look at old SQL Server code you may find a DATETIME<\/code> value CAST<\/code> or CONVERT<\/code>ed to floating-point, manipulated and then cast back to DATETIME<\/code>. If this sounds like a kludge, that’s because it is. But it was a very fast kludge, especially if your machine had floating-point hardware. Since we had no separate DATE<\/code> and TIME<\/code> data types, the convention was to fake having a DATE<\/code> by setting the time portion to 00:00:00.000. You will find a lot of old code for this in legacy systems.<\/p>\n

SQL Server has had ANSI\/ISO compliant date and time datatypes for several years now. You should not ever use the old DATETIME<\/code> data type, and instead you should be moving on to DATETIME2(n)<\/code> and DATE<\/code> datatypes today. I also think it’s probably a good idea to go through and replace those old kludges with the modern datatypes. The new datatypes are not only ANSI\/ISO 8601 conformant but use less storage and have more precision if you really need it. Let’s be honest, most of the time, commercial work is quite happy with precisions to a date or to a fraction of an hour within the date. While it’s nice to be able to go down to nanoseconds, we really don’t use that degree of precision in any common application you will ever run into.<\/p>\n

Another advantage is using the ISO 8601 format which is an international standard with unambiguous specification. Also, this format isn’t affected by the SET<\/code> DATEFORMAT<\/code> or SET LANGUAGE<\/code> setting. This format has several display options; SQL picked one.<\/p>\n

Today, the ANSI\/ISO SQL Standards only allow the ISO 8601 format which looks like “yyyy-mm-dd HH:mm:ss.ssssss”, in the language. Please notice the use of dashes and a space between the date and the time fields. The ISO 8601 standard also specifies a version that drops out the dashes and replaces the space with a “T” as a separator, which SQL Server also allows. Frankly I like the second version better because of the continuous string with no worries about white spaces (Blank? Tab? Newline? Carriage return?) and I’m very glad to see that Microsoft supports it. However, we picked the first version when we were setting up the standards because it looks nicer for people. It is the default in the SQL world.<\/p>\n

Other Date Displays<\/h2>\n

Probably the most esoteric way of representing a date is the Julian system. It’s used by astronomers and it has nothing to do with the Julian calendar. Nobody ever uses that outside of the sciences. The count starts with 4713, January 01 BCE, and the Julian Date for 00:30:00.0 UT January 1, 2013, is 2\u00a0456\u00a0293.520\u00a0833. For details see this article<\/a> and this one<\/a>.<\/p>\n

Another common way of displaying a date is simply the ordinal date format. It consist of “yyyy-ddd” where the “yyyy” is the usual year and “ddd” is the day within the year, taking on the values 001 thru 365 or 366.<\/p>\n

Closed Interval Models<\/h2>\n

Another model is based on a closed interval defined by (start_timestamp<\/code>, end_timestamp<\/code>) pairs that mark the end points of a temporal interval. We assume (start_timestamp<\/code> \u2264 end_timestamp<\/code>). All of the temporal points in the interval are assumed to be in the set of values, even if you cannot actually represent them with your software. The FIPS (Federal Information Processing Standards) require at least four decimal places of seconds in a timestamp in all clocks used by the US government. However, the current hardware and SQL Standards can give seven decimal places, nanoseconds.<\/p>\n

A calculus for temporal reasoning was introduced in 1983 by James F. Allen (“Maintaining knowledge about temporal intervals”, Communications of the ACM. 26(11): 832\u2013843.). It was not designed for SQL, but for general use when describing events. The following 13 base relations capture the possible relations between two intervals in Allen\u2019s Interval Algebra:<\/p>\n

<\/p>\n

The inverse of a relation simply flips the right and left sides of the infixed operators and rewords the definition. Obviously, equality is its own inverse.<\/p>\n

In general, the number of different relations between n intervals is 1, 1, 13, 409, 23917, 2244361… OEIS A055203. The special case shown above is for n=2<\/code>.You have to decide if endpoints are co-located, or merely tangent. It gets messy, but it is a consistent, known model.<\/p>\n

ISO Half Open Interval Model<\/h2>\n

The ISO model of time is based on half open intervals. That means we have a starting point, but we have no ending point for an interval. The duration in the interval approaches but never actually reaches the ending point. To make this a little more explicit, consider a day. It begins at 00:00:00 Hrs (midnight), progresses through 24 hours, but never gets to 24:00:00 Hrs. That final endpoint actually belongs to the start of the next day. DB2, other SQL products, and international conventions will convert 24:00:00 Hrs to midnight of the next day automatically.<\/p>\n

Depending exactly in what precision your times are kept, you can get as close as 23:59:59.9999999 Hrs, assuming that you\u2019re following the precision required by Federal Information Processing Standards. In actual practice, for commercial purposes, you probably are not paying your employees by the nanosecond. Let\u2019s be honest, to be within one minute is probably good enough in most cases.<\/p>\n

The advantage of the half open interval model is that two time periods can be abutted to each other. Each point in time belongs to one and only one interval. If you used closed intervals, then they would abut on at least one point, or could overlap. This makes for a lot of problems when you are trying to determine during which shift a particular billable event occurs.<\/p>\n

In mathematics, this is called the partitioning of a set, and it\u2019s where we get the name of the PARTITION<\/code> BY<\/code> clause in SQL. But unlike mathematics, SQL (or any other digital representation in a computer) can\u2019t actually be a continuum. To give you an idea where I\u2019m going, let\u2019s assume that a day is broken into three shifts in a factory for purposes of timecards.<\/p>\n

CREATE TABLE Shifts\r\n(\r\n    shift_code CHAR(12) NOT NULL PRIMARY KEY \r\n       CHECK (shift_code IN ( \r\n         'first_shift', 'second_shift', 'third_shift' )),\r\n    start_time TIME(0) NOT NULL,\r\n    end_time TIME(2) NOT NULL,\r\n    CHECK (start_time < end_time)\r\n);\r\nINSERT INTO Shifts\r\nVALUES\r\n('first_shift', '09:00:00', '16:59:999'),    ---nine to five\r\n('second_shift', '17:00:00', '19:59:999'),   ---five to eight\r\n('third_shift', '20:00:00', '23:59:59.999'); -- graveyard shift\r\nCREATE TABLE Timecards\r\n(\r\n    emp_name VARCHAR(25) NOT NULL PRIMARY KEY,\r\n    clock_in_time TIME(0) NOT NULL,\r\n    clock_out_time TIME(0) NOT NULL,\r\n    CHECK (clock_in_time <= clock_out_time)\r\n\t...\r\n);<\/pre>\n
This is a pretty skeletal timecard system created for illustrating the basic principles of this article. A real schema would have a lot more details.<\/p>\n
For example, employee A enters a time in\/out block 08:30:00\u2013 17:30:00. That block would have to be parsed as something like<\/p>\n
SHIFT CODE third_shift = 0.5 hours<\/p>\n
SHIFT CODE first_shift = 8 hours<\/p>\n
SHIFT CODE second_shift = 0.5 hours<\/p>\n
We can use decimal representation for hours\u2026 common in payroll and time tracking. Many decades ago, I had a similar problem. We rounded the durations to 15 minute blocks (xx:00:00 thru xx:14:59, xx:15:00 thru xx:29:59, xx:30:00 thru xx:44:59, xx:45:00 thru xx:59:59) as per union rules. 24 hrs per day * 4 blocks per hour = 96 blocks per day, 5 days per work week = 480, 50 work weeks per year = 24,000; a decade of lookups = 240,000 rows. Put a running total of blocks in each row, then subtract the starting block count from the ending block count.<\/p>\n
We found that a simple lookup table was easier than trying to do temporal math. After all, SQL is a data language, not a computational language. Use a spreadsheet and load the table, so you are done for a decade.<\/p>\n
The OVERLAPS() Predicate<\/h2>\n
ANSI\/ISO Standard SQL defines the OVERLAPS() <\/code>predicate as the result of the following expression:<\/p>\n
(S1 > S2 AND NOT (S1 >= T2 AND T1 >= T2))<\/p>\n
OR (S2 > S1 AND NOT (S2 >= T1 AND T2 >= T1))<\/p>\n
OR (S1 = S2 AND (T1 <> T2 OR T1 = T2))<\/p>\n
where S1 and S2 are the starting times of the two time periods and T1 and T2 are their termination times. The rules for the OVERLAPS()<\/code> predicate sound like they should be intuitive, but they are not. The principles that we wanted in ANSI\/ISO Standard SQL were:<\/p>\n
1. A time period includes its starting point but does not include its end point. We have already discussed this temporal model.<\/p>\n
2. If the time periods are not “instantaneous”, they overlap when they share a common time period.<\/p>\n
3. If the first term of the predicate is an INTERVAL<\/code> and the second term is an instantaneous event (a <datetime> data type), they overlap when the second term is in the time period (but is not the end point of the time period). That follows the half-open model.<\/p>\n
4. If the first and second terms are both instantaneous events, they overlap only when they are equal.<\/p>\n
5. If the starting time is NULL<\/code> and the finishing time is a <datetime> value, the finishing time becomes the starting time and we have an event. If the starting time is NULL<\/code> and the finishing time is an INTERVAL<\/code> value, then both the finishing and starting times are NULL<\/code>.<\/p>\n
Please consider how your intuition reacts to these results, when the granularity is at the YEAR-MONTH-DA<\/code>Y level. Remember that a day begins at 00:00:00 Hrs.<\/p>\n
(today, today) OVERLAPS (today, today) = TRUE<\/p>\n
(today, tomorrow) OVERLAPS (today, today) = TRUE<\/p>\n
(today, tomorrow) OVERLAPS (tomorrow, tomorrow) = FALSE<\/p>\n
(yesterday, today) OVERLAPS (today, tomorrow) = FALSE<\/p>\n
Keeping Contiguous Events<\/h2>\n
Alexander Kuznetsov wrote this programming idiom for History Tables<\/a> in T-SQL, but it generalizes to any SQL. It builds a temporal chain from the current row to the previous row with a self-reference. This idiom easy to use, once the DDL is in place.<\/p>\n
This is easier to show with code:<\/p>\n
CREATE TABLE Tasks\r\n(\r\n    task_id INTEGER NOT NULL,\r\n    task_score CHAR(1) NOT NULL,\r\n    previous_end_date DATE, -- null means first task\r\n    current_start_date DATE\r\n        DEFAULT CURRENT_TIMESTAMP NOT NULL,\r\n    CONSTRAINT previous_end_date_and_current_start_in_sequence \r\n       CHECK (previous_end_date <= current_start_date),\r\n     --DEFERRABLE INITIALLY IMMEDIATE,\r\n    current_end_date DATE,  -- null means unfinished current task\r\n    CONSTRAINT current_start_and_end_dates_in_sequence \r\n       CHECK (current_start_date <= current_end_date),\r\n    CONSTRAINT end_dates_in_sequence \r\n       CHECK (previous_end_date <> current_end_date),\r\n    PRIMARY KEY (\r\n                    task_id,\r\n                    current_start_date\r\n                ),\r\n    UNIQUE (\r\n               task_id,\r\n               previous_end_date\r\n           ),               -- null first task\r\n    UNIQUE (\r\n               task_id,\r\n               current_end_date\r\n           ),               -- one null current task\r\n    FOREIGN KEY (\r\n                    task_id,\r\n                    previous_end_date\r\n                ) -- self-reference\r\n    REFERENCES Tasks (\r\n                         task_id,\r\n                         current_end_date\r\n                     )\r\n);<\/pre>\n
Well, that looks complicated! Let’s look at the code column by column. Task<\/code>_id explains itself. The previous_end_date<\/code> will not have a value for the first task in the chain, so it is NULL<\/code>-able. The current_start_date<\/code> and current_end_date<\/code> are the same data elements, temporal sequence and PRIMARY<\/code> KEY<\/code> constraints we had in the simple history table schema.<\/p>\n
The two UNIQUE<\/code> constraints will allow one NULL<\/code> in their pairs of columns and prevent duplicates. Remember that UNIQUE<\/code> is NULL<\/code>-able, not like PRIMARY<\/code> KEY<\/code>, which implies UNIQUE<\/code> NOT<\/code> NULL<\/code>.<\/p>\n
Finally, the FOREIGN<\/code> KEY<\/code> is the real trick. Obviously, the previous task must end when the current task started for them to abut, so there is another constraint. This constraint is a self-reference that makes sure this is true. Modifying data in this type of table is easy but requires some thought. The foreign key reference is to be disabled when this table is first constructed and then restarted afterwards. In ANSI\/ISO Standard SQL, this is done with deferrable constraints, but in T-SQL you have to explicitly turn the constraint on and off during the session.<\/p>\n
DATETIMEOFFSET<\/h2>\n
DATETIMEOFFSET<\/code> is a data type giving you the ability to work with time zones. Here\u2019s the format:<\/p>\n
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]<\/p>\n
The final option on this date format is the number of hours and minutes that the time zone difference differs from UTC. Time zones can be a little crazy, usually for political reasons rather than computational considerations. For example, for many years Japan and Korea had different zones. Columbia did not want to be in the same time zone as United States when Caesar Chavez was their dictator. You can find the currently recognized time zones here<\/a>. SQL Server doesn’t care if the time zone offset exists in the real world or not, it just needs to be computationally valid.<\/p>\n
To generate the current local non-offset date and time value in the target SQL Server instance, you use functions such as SYSDATETIME<\/code> (returns DATETIME2<\/code>) or GETDATE<\/code> (returns DATETIME<\/code>). To generate the current local date and time value with the offset, use SYSDATETIMEOFFSET<\/code>. This example returns the date and time, including the offset, for US Central Standard Time when running on my computer:<\/p>\n
PRINT SYSDATETIMEOFFSET();<\/pre>\n
This command returns:<\/p>\n
<\/p>\n
To return the current time zone offset, you use functions like DATENAME<\/code> (returns a string with hours and minutes offset) or DATEPART<\/code> (returns an integer with minutes offset) with the TZoffset<\/code> part (tz<\/code> in short). The displacement will make allowances for daylight saving time, according to Microsoft conventions. Here\u2019s an example which returns -300 since the offset is -5 hours:<\/p>\n
DECLARE @Date DATETIMEOFFSET = SYSDATETIMEOFFSET();\r\nPRINT DATEPART(tz,@Date);<\/pre>\n
AT TIME ZONE<\/h2>\n
The AT<\/code> TIME<\/code> ZONE<\/code> function was introduced in SQL Server 2016. It replaces both TODATETIMEOFFSET<\/code> and SWITCHOFFSET<\/code>. It has the following syntax:<\/p>\n
<value> AT TIME ZONE '<standard time zone name>'<\/pre>\n
Here\u2019s an example:<\/p>\n
DECLARE @Date DATETIMEOFFSET = GETDATE();\r\nPRINT @Date;\r\nSET @Date = @Date AT TIME ZONE 'Pacific Standard Time';\r\nPRINT @Date;<\/pre>\n
<\/p>\n
When the input value is a nonoffset date and time value, the function behaves similar to TODATETIMEOFFSET<\/code>; when the input value is a date and time value with an offset, the function behaves similar to SWITCHOFFSET<\/code>. Moreover, you don\u2019t need to worry about clock switching; rather, just specify the target standard time zone name (for instance, for Pacific Time always specify ‘Pacific Standard Time’), and SQL Server will figure out dynamically the target time zone offset based on the Windows time zone conversion rules. To get the full list of supported standard time zone names, plus their current offset from UTC and whether it\u2019s currently on daylight saving time, query the sys.time_zone_info<\/code> function:<\/p>\n
SELECT * FROM sys.time_zone_info;<\/pre>\n
The query returns 139 rows, but here is a sample:<\/p>\n
<\/p>\n
I also strongly recommend that you read this article<\/a>, so have some idea just how complicated things can get.<\/p>\n
The Most Important Thing to take Home from this article<\/h2>\n
Use the TIME<\/code>, DATE<\/code>, DATETIME2<\/code> and DATETIMEOFFSET<\/code> data types for new work. These types align with the SQL Standard. They are more portable. DATE<\/code>, TIME<\/code> DATETIME2<\/code> and DATETIMEOFFSET<\/code> provide more decimal seconds precision. DATETIMEOFFSET<\/code> provides time zone support for globally deployed applications, but think in UTC.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
There are those who argue that time doesn\u2019t really exist; it\u2019s just an illusion. That debate won\u2019t keep you from storing and manipulating dates and time in SQL Server. In this article, Joe Celko discusses some of the many ways that SQL Server treats temporal data types.…<\/p>\n","protected":false},"author":96214,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[],"coauthors":[6781],"class_list":["post-84769","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84769","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=84769"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84769\/revisions"}],"predecessor-version":[{"id":84775,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84769\/revisions\/84775"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=84769"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=84769"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=84769"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=84769"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}