8

Alternatively, how did Microsoft make time travel possible?

Consider this code:

DECLARE @Offset datetimeoffset = sysdatetimeoffset();
DECLARE @UTC datetime = getUTCdate();
DECLARE @UTCFromOffset datetime = CONVERT(datetime,SWITCHOFFSET(@Offset,0));
SELECT
    Offset = @Offset,
    UTC = @UTC,
    UTCFromOffset = @UTCFromOffset,
    TimeTravelPossible = CASE WHEN @UTC < @UTCFromOffset THEN 1 ELSE 0 END;

@Offset is set before @UTC, yet it sometimes has a later value. (I've tried this on SQL Server 2008 R2 and SQL Server 2016. You have to run it a few times to catch the suspect occurrences.)

This does not appear to be simply a matter of rounding or lack of precision. (In fact, I think the rounding is what "fixes" the issue occasionally.) The values for a sample run are as follows:

  • Offset
    • 2017-06-07 12:01:58.8801139 -05:00
  • UTC
    • 2017-06-07 17:01:58.877
  • UTC From Offset:
    • 2017-06-07 17:01:58.880

So datetime precision allows the .880 as a valid value.

Even Microsoft's GETUTCDATE examples show the SYS* values being later than the older methods, despite being SELECTed earlier:

SELECT 'SYSDATETIME()      ', SYSDATETIME();  
SELECT 'SYSDATETIMEOFFSET()', SYSDATETIMEOFFSET();  
SELECT 'SYSUTCDATETIME()   ', SYSUTCDATETIME();  
SELECT 'CURRENT_TIMESTAMP  ', CURRENT_TIMESTAMP;  
SELECT 'GETDATE()          ', GETDATE();  
SELECT 'GETUTCDATE()       ', GETUTCDATE();  
/* Returned:  
SYSDATETIME()            2007-05-03 18:34:11.9351421  
SYSDATETIMEOFFSET()      2007-05-03 18:34:11.9351421 -07:00  
SYSUTCDATETIME()         2007-05-04 01:34:11.9351421  
CURRENT_TIMESTAMP        2007-05-03 18:34:11.933  
GETDATE()                2007-05-03 18:34:11.933  
GETUTCDATE()             2007-05-04 01:34:11.933  
*/

I presume this is because they come from different underlying system information. Can anyone confirm and provide details?

Microsoft's SYSDATETIMEOFFSET documentation says "SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API" (thanks srutzky), but their GETUTCDATE documentation is much less specific, saying only that the "value is derived from the operating system of the computer on which the instance of SQL Server is running".

(This isn't entirely academic. I ran into a minor issue caused by this. I was upgrading some procedures to use SYSDATETIMEOFFSET instead of GETUTCDATE, in hopes for greater precision in the future, but I started to get odd ordering because other procedures were still using GETUTCDATE and occasionally "jumping ahead" of my converted procedures in the logs.)

  • 1
    I don't know that there is any explanation other than either rounding down or rounding up. When you have to round any value to one of lesser precision, and when in some cases you have to round down, and others you have to round up (simple math rules in play here), you can expect that in some cases the rounded value will be either lower or higher than the original, more precise value. If you want to ensure that the less precise value is always before (or always after) the more precise one, you could always manipulate it by 3 milliseconds using DATEADD(). – Aaron Bertrand Jun 7 '17 at 18:11
  • (Also, why not just change ALL procedures at the same time to use the newer, more precise value?) – Aaron Bertrand Jun 7 '17 at 18:37
  • I don't think it can be as simple an answer as rounding because if you convert the datetimeoffset value of 2017-06-07 12:01:58.8801139 -05:00 directly to datetime, it picks 2017-06-07 12:01:58.880, not 2017-06-07 12:01:58.877. So either GETUTCDATE internally rounds differently or they are different sources. – Riley Major Jun 7 '17 at 19:45
  • I'd prefer to do them incrementally to change as few things as possible at a time. scribnasium.com/2017/05/deploying-the-old-fashioned-way I'll eventually do them in a batch or live with the inconsistency in the logs. – Riley Major Jun 7 '17 at 19:46
  • 2
    In addition I'd like to add the time travel is always possible in computers. You should never code expecting that time is always going forward. The reason is drift and corrections to the system time, mostly driven by the Windows Time Service, but also from user adjustment. Milliseconds drift and correction is actually very often encountered. – Remus Rusanu Jun 8 '17 at 7:39
9

The issue is a combination of datatype granularity / accuracy and source of the values.

First, DATETIME is only accurate / granular to every 3 milliseconds. Hence, converting from a more precise datatype such as DATETIMEOFFSET or DATETIME2 won't just round up or down to the nearest millisecond, it could be 2 milliseconds different.

Second, the documentation seems to imply a difference in where the values come from. The SYS* functions use the high-precision FileTime functions.

SYSDATETIMEOFFSET documentation states:

SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API.

while the GETUTCDATE documentation states:

This value is derived from the operating system of the computer on which the instance of SQL Server is running.

Then, in the About Time documentation, a chart shows the following two (of several) types:

  • System Time = "Year, month, day, hour, second, and millisecond, taken from the internal hardware clock."
  • File Time = "The number of 100-nanosecond intervals since January 1, 1601."

Additional clues are in the .NET documentation for the StopWatch class (emphasis in bold italics mine):

  • Stopwatch Class

    The Stopwatch measures elapsed time by counting timer ticks in the underlying timer mechanism. If the installed hardware and operating system support a high-resolution performance counter, then the Stopwatch class uses that counter to measure elapsed time. Otherwise, the Stopwatch class uses the system timer to measure elapsed time.

  • Stopwatch.IsHighResolution Field

    The timer used by the Stopwatch class depends on the system hardware and operating system. IsHighResolution is true if the Stopwatch timer is based on a high-resolution performance counter. Otherwise, IsHighResolution is false, which indicates that the Stopwatch timer is based on the system timer.

Hence, there are different "types" of times that have both different precisions and different sources.

But, even if that is a very loose logic, testing both types of functions as sources for the DATETIME value proves it. The following adaptation of the query from the question shows this behavior:

DECLARE @Offset DATETIMEOFFSET = SYSDATETIMEOFFSET(),
        @UTC2 DATETIME = SYSUTCDATETIME(),
        @UTC DATETIME = GETUTCDATE();
DECLARE @UTCFromOffset DATETIME = CONVERT(DATETIME, SWITCHOFFSET(@Offset, 0));
SELECT
    Offset = @Offset,
    UTC2 = @UTC2,
    UTC = @UTC,
    UTCFromOffset = @UTCFromOffset,
    TimeTravelPossible = CASE WHEN @UTC < @UTCFromOffset THEN 1 ELSE 0 END,
    TimeTravelPossible2 = CASE WHEN @UTC2 < @UTCFromOffset THEN 1 ELSE 0 END;

Returns:

Offset                 2017-06-07 17:50:49.6729691 -04:00
UTC2                   2017-06-07 21:50:49.673
UTC                    2017-06-07 21:50:49.670
UTCFromOffset          2017-06-07 21:50:49.673
TimeTravelPossible     1
TimeTravelPossible2    0

As you can see in the above results, UTC and UTC2 are both DATETIME datatypes. @UTC2 is set via SYSUTCDATETIME() and is set after @Offset (also taken from a SYS* function), but prior to @UTC which is set via GETUTCDATE(). Yet, @UTC2 appears to come before @UTC. The OFFSET part of this is completely unrelated to anything.

HOWEVER, to be fair, this still isn't proof in a strict sense. @MartinSmith traced the GETUTCDATE() call and found the following:

enter image description here

I see three interesting things in this call stack:

  1. Is starts with a call to GetSystemTime() which returns a value that is only precise down to the milliseconds.
  2. It uses DateFromParts (i.e. no formula to convert, just use the individual pieces).
  3. There is a call to QueryPerformanceCounter towards the bottom. This is a high-resolution difference counter that could be being used as a means of "correction". I have seen some suggestion of using one type to adjust the other over time as long intervals slowly get out of sync (see How to get timestamp of tick precision in .NET / C#? on S.O.). This does not show up when calling SYSDATETIMEOFFSET.

There is much good info here regarding the different types of time, different sources, drift, etc: Acquiring high-resolution time stamps.

Really, it is not appropriate to compare values of different precisions. For example, if you have 2017-06-07 12:01:58.8770011 and 2017-06-07 12:01:58.877, then how do you know that the one with less precision is greater than, less than, or equal to the value with more precision? Comparing them assumes that the less precise one is actually 2017-06-07 12:01:58.8770000, but who knows if that is true or not? The real time could have been either 2017-06-07 12:01:58.8770005 or 2017-06-07 12:01:58.8770111.

However, if you have DATETIME datatypes, then you should use the SYS* functions as the source as they are more accurate, even if you lose some precision as the value is forced into a less precise type. And along those lines, it seems to make more sense to use SYSUTCDATETIME() rather than call SYSDATETIMEOFFSET() only to adjust it via SWITCHOFFSET(@Offset, 0).

  • On my system GETUTCDATE seems to call GetSystemTime and SYSDATETIMEOFFSET calls GetSystemTimeAsFileTime though both apparently boil down to the same thing blogs.msdn.microsoft.com/oldnewthing/20131101-00/?p=2763 – Martin Smith Jun 8 '17 at 6:59
  • 1
    @MartinSmith (1/2) I have spent some time on this ad am out of time today for doing any more. I don't have an easy way to test the C++ Win API that is being called and is referenced in that blog you mentioned. I can convert back and forth between FileTime and DateTime in .NET, but DateTime is not a SystemTime as it can handle full precision, but it was loseless. I can't prove/disprove that GetSystemTime calls GetSystemTimeAsFileTime , but I did notice to interesting things in the call stack you posted in the question comment: 1) it uses DateFromParts so most likely truncated into ms (cont) – Solomon Rutzky Jun 8 '17 at 16:18
  • @MartinSmith (2/2) instead of rounded (since SystemTime only goes down to milliseconds), and 2) there is a call to QueryPerformanceCounter towards the bottom. This is a high-resolution difference counter that could be being used as a means of "correction". I have seen some suggestion of using one type to adjust the other over time as long intervals slowly get out of sync. There is much good info here: Acquiring high-resolution time stamps. If they do start as the same time, the loss is from 1 of the 2 steps above. – Solomon Rutzky Jun 8 '17 at 16:24
  • I figured Offset was a red herring but didn't create a more abstract test. I should have once I saw the Microsoft docs show the discrepancy. Thanks for confirming that. – Riley Major Jun 9 '17 at 14:28
  • My challenge is that I have multiple procs doing this. They all use GETUTCDATE now. If I change some to any of the SYS* functions (with Offset or UTC directly), they start to get out of order with the others using the GET* functions. But I can either live with it or change them all at once. Not a huge deal. It just sparked my curiosity. – Riley Major Jun 9 '17 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.