SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tom Thomson / Nanoseconds / Latest Posts

RE: Nanoseconds

Dineshbabu — Mon, 07 Jan 2013 05:04:32 GMT

Difficult one..

RE: Nanoseconds

David Conn — Sat, 17 Nov 2012 22:02:53 GMT

I'm happy that we're starting to get questions with the more modern Data Types. Datetime should have been retired when Date, Time and DateTime2 were made available.David

RE: Nanoseconds

L' Eomot Inversé — Fri, 16 Nov 2012 05:58:30 GMT

[quote][b]seankyleprice (11/16/2012)[/b][hr]I personally think the answer should be 'none of the above'. The accuracy of sysdatetime() depends on the computer hardware and version of Windows on which the instance of SQL Server is running. Queries 2 and 4 may give an accurate value for the number of nanoseconds since when your server believes midnight to have been but not 'reliably' when midnight actually was unless you are confident that the date time of your server is accurate to 100 nanoseconds.[/quote]Excellent point. We can of course take it that the time referred to as midnight in the context of how long has the computer been going since midnight as being the time when it thought midnight was, but that doesn't help because reliability is still affected by the clock drift between then and now. Typical systems today, even those with very good hardware, despite usually having excellent clock management software that (when correctly configured, which may not be all that common) uses data from a reliable time server to determine how much drift there is in the hardware would not be able to run reliably with a drift rate between time server inputs of less that 37 microseconds per annum (that's a few decimal orders of magnitude better than typical, I think), so if "reliable to 100 nanoseconds" really means just that you are right and the correct answer is "none of the above". Even if "reliable to the nearest 100 nanoseconds" means "accurate count of the clock's nominally 100 ns ticks" a correction from the time server might have occurred during the interval and disturbed the tick count so that the result would be unreliable anyway. When I wrote the question I meant "reliable within the capability of the system to determine the time". I should have included those words, or something like them, in the question.

RE: Nanoseconds

seankyleprice — Fri, 16 Nov 2012 05:09:42 GMT

I personally think the answer should be 'none of the above'. The accuracy of sysdatetime() depends on the computer hardware and version of Windows on which the instance of SQL Server is running. Queries 2 and 4 may give an accurate value for the number of nanoseconds since when your server believes midnight to have been but not 'reliably' when midnight actually was unless you are confident that the date time of your server is accurate to 100 nanoseconds.

RE: Nanoseconds

SQLRNNR — Tue, 13 Nov 2012 14:43:37 GMT

[quote][b]Koen Verbeeck (11/8/2012)[/b][hr][quote][b]sanket kokane (11/7/2012)[/b][hr]I got it wrong ...you should mention sql server Version in question .Sysdatetime () not present in sql 2000 and 2005[/quote]So, for which versions do you think the question is meant? Obviously for 2008 and up. There's also a consensus that versions who are no longer supported by Microsoft do not count for Questions of the Day.[/quote]+1Also, if you are testing on an old version no longer under support then at that point you should include version.

RE: Nanoseconds

L' Eomot Inversé — Fri, 09 Nov 2012 13:16:23 GMT

[quote][b]john.arnott (11/9/2012)[/b][hr]Would method 1 be reliable even in the early morning hours? As SYSDATETIME() is non-deterministic, could it not use two slightly different values for the two calls? I get the feeling that you, Tom, considered that and have an explanation of the definition of "deterministic."[/quote]Actually, you give me too much credit - I didn't even consider that for method 1 (not sure whether that was because I was counting it as unreliable anyway or because I didn't think of it - it's a while since I submitted the question).I did consider it for method 4, because I think I read somewhere that CTEs generally work by text substitution, so that if the same CTE is referred to more than once in a query the two instances may deliver different values. Of course the optimizer may decide to common up textually equal expressions, provided that doing so doesn't screw up store management, but I don't know whether it would do that or not; and of course recomputing the content of a CTE could lead to some new variations of queries whose semantics could be altered according to whether intermediate results are spooled or not (so if it's an update query, new instances of the Harlequin Problem - of course according to some people where insert and delete queries have their semantics modified in a similar manner that would be the Harlequin Problem too) so it's sometimes essential to spool a CTE. Spooling every CTE would be a performance nightmare, so that presumably doesn't happen. All that leaves me non the wiser about whether this particular query will have a problem, so I decided to test method 4 fairly thoroughly. On my machine, with a reasonable amount of background work going on, I invoked this method 10000 times in a loop to write a row into a table with the time and the nanoseconds since midnight (the final select of method 4 was replace by [code]insert #pig select (1000000000 * CAST(secs.ss as bigint)+CAST(datediff(NS,secstart.start,t) as bigint)) , t from secs cross join dy cross join secstart cross join noww;[/code]to do this - for some reason in my notes for the test all occurrences of now have two ws, presumably I wanted to avoid the reserved [???] word) and then counting the number of rows where the nsec column was the same as the nanosec count calculated by method 2 from the t column), and also counting the number of rows where it was different, delivered counts of 10000 for same, and 0 for different. This was repeated at several times of day and with several different background workloads added (AV and spyware scan, database backups, intense numeric stuff, massive automatic text edits, indexing files - [google desktop], network dowloads, varios combinations of these) with the same results.What that tells me is that on my machine either the optimiser decides to calculate that CTE just once, or the time difference between the two times it calculates it is always less that 100NS, and I think the latter implies that the query (up to the point of having the output ready to write to the log) takes less time than the minimum interval (if there is such a thing in this verson of NT) that a thread is given when selected before it will be interupted. So for me method 4 is reliable. The scary thing is that I have no real proof that it is actually reliable on any other machine, or that it will be reliable in SQLK 2012 (I'm using 2008 R2), or that it will be reliable if I substitute some version of Windows 7 for Windows XP Pro. Despite that, I'm sure that it is indeed reliable, at least in SQL 2008 R2. Of course this risk can be eliminated completely by having the query work off a variable previously initialised to sysdatetime(), and that applies equally to method 1 (for the few hours each day that it works), so these methods can be made safe (for method 1, safe for a few hours per day) by converting them into two statements each instead of 1. But of course I prefer method 2 to method 4, because it's simpler and even in its single statement form doesn't carry this risk.edit: Why avoid "now" as a reserved word? It isn't a reserved word in T-SQL. I must have been thinking of some other language (maybe ISO SQL - it has more reserved words than T-SQL - but I don't think so).

RE: Nanoseconds

john.arnott — Fri, 09 Nov 2012 09:59:52 GMT

Would method 1 be reliable even in the early morning hours? As SYSDATETIME() is non-deterministic, could it not use two slightly different values for the two calls? I get the feeling that you, Tom, considered that and have an explanation of the definition of "deterministic."

RE: Nanoseconds

Revenant — Fri, 09 Nov 2012 08:06:32 GMT

I took me twenty minutes of reading BoL and head scratching... Thanks, Tom!

RE: Nanoseconds

call.copse — Fri, 09 Nov 2012 03:44:21 GMT

I'll just weigh in here to say it was a fair question from my perspective because it asked about the METHOD, and the intent of this was clear, if not the exact syntax. Also because I got it right this time.

RE: Nanoseconds

L' Eomot Inversé — Thu, 08 Nov 2012 12:37:39 GMT

RE: Nanoseconds

Matt Marston — Thu, 08 Nov 2012 10:33:04 GMT

If you ever did need to do this and you are uncomfortable depending on the binary format and the complex SELECT in option 4 scares you (it does me). Here is another approach making use of DATEPART:[code="sql"]SELECT( ( -- hours * 60 + minutes = total minutes; DATEPART(hour, SYSDATETIME()) * 60 + DATEPART(minute, SYSDATETIME()) -- total minutes * 60 + seconds = total seconds; ) * 60 + DATEPART(second, SYSDATETIME()) -- total seconds * 1,000,000,000 + nanoseconds = total nanoseconds;) * CONVERT(bigint, 1000000000) + DATEPART(nanosecond, SYSDATETIME());[/code]

RE: Nanoseconds

Steve Jones - SSC Editor — Thu, 08 Nov 2012 10:04:00 GMT

Typos corrected. I'll award back points to this time.

RE: Nanoseconds

bitbucket-25253 — Thu, 08 Nov 2012 10:03:57 GMT

[quote]L' Eomot InverséI am indeed getting old (although I can't seem to catch up with Ron), [/quote]Seems like I will have to invoke DBCC TIMEWARP:[url]http://www.sqlservercentral.com/Forums/Topic860953-61-1.aspx[/url]But I am having this problem[quote]those parameters are not optional, the 137 parameters are required parameters - and from what I recall the next 255 are optional parameters that can be used to modify how the 137 required parameters are interpreted[/quote]I will soon figure those parameters out and invoke same so that you can catch up with me.

RE: Nanoseconds

Meow Now — Thu, 08 Nov 2012 09:56:31 GMT

I guess #4 only because it was a long winded answer. After trying to run the options, I have to agree that #2 should not be a valid answer until the typo is fixed. After all, some typos are intentional on here. Good to know in case I ever build those time-traveling nanorobots.

RE: Nanoseconds

malleswarareddy_m — Thu, 08 Nov 2012 08:15:44 GMT

I select method 4 only. so I given wrong answer.

RE: Nanoseconds

George M Parker — Thu, 08 Nov 2012 08:15:02 GMT

An incomplete answer that generates a syntax error cannot possibly be a correct answer, therefore only option 4 is correct. :-D--Method 2:--use the internal structure of datetime(2)cast(cast(reverse(substring(cast(@t as binary(9)),2,5)) as binary(5)) as bigint)*100

RE: Nanoseconds

vk-kirov — Thu, 08 Nov 2012 08:10:14 GMT

[quote][b]Lon-860191 (11/8/2012)[/b][hr]Good question, but I would question if a method is reliable if it does not work less than 1/3 of the time daily, method 2 overloaded at 8 am since this means that it would be only good during after midnight but only through early morning.[/quote]Can you prove this by posting an example of T-SQL code?It seems to me that the method works fine even for the last 100 nanoseconds of the day:[code="sql"]declare @t datetime2;set @t = '2012-11-08T23:59:59.9999999';select @t, cast(cast(reverse(substring(cast(@t as binary(9)),2,5)) as binary(5)) as bigint)*100;[/code][code="plain"] ---------------------- --------------------2012-11-08 23:59:59.99 86399999999900(1 row(s) affected)[/code]

RE: Nanoseconds

mtassin — Thu, 08 Nov 2012 07:50:27 GMT

I was going to say... method two.[code]cast(cast(reverse(substring(cast(@t as binary(9)),2,5)) as binary(5)) as bigint)*100[/code]Lacks a select, lacks a declare for @t, etc... and the question was , which methods will give us the nanoseconds since midnight. Hard to tell if the statement was or wasn't intentionally properly written.

RE: Nanoseconds

Lon-860191 — Thu, 08 Nov 2012 07:48:56 GMT

Good question, but I would question if a method is reliable if it does not work less than 1/3 of the time daily, method 2 overloaded at 8 am since this means that it would be only good during after midnight but only through early morning.

RE: Nanoseconds

Sean Lange — Thu, 08 Nov 2012 07:30:43 GMT

[quote][b](Bob Brown) (11/8/2012)[/b][hr]This is absolutely the worse question I have encountered on QotD. Think I will pass on future Tom questions.[/quote]If [b][i]this[/i][/b] is the worst QotD you have encountered, I would encourage you to peruse more QotD.Nice question Tom. I got tripped up by method 2 and answered only 4 but as always I learned something new.

RE: Nanoseconds

(Bob Brown) — Thu, 08 Nov 2012 06:06:10 GMT

This is absolutely the worse question I have encountered on QotD. Think I will pass on future Tom questions.

RE: Nanoseconds

L' Eomot Inversé — Thu, 08 Nov 2012 05:22:56 GMT

[quote][b]Koen Verbeeck (11/8/2012)[/b][hr]Nice question Tom, but I doubt if I'll ever use it :-)(ps: you must be getting old if you can't count to four anymore :-P)[/quote]I am indeed getting old (although I can't seem to catch up with Ron), but I can still count four: zero, one, two, three :-D. It's just that I sometimes forget to add one when I've counted something. :laugh:

RE: Nanoseconds

kalyani.k478 — Thu, 08 Nov 2012 05:21:31 GMT

Great Question

RE: Nanoseconds

sanket kokane — Thu, 08 Nov 2012 04:34:22 GMT

[quote][b]Koen Verbeeck (11/8/2012)[/b][hr][quote][b]sanket kokane (11/7/2012)[/b][hr]I got it wrong ...you should mention sql server Version in question .Sysdatetime () not present in sql 2000 and 2005[/quote]So, for which versions do you think the question is meant? Obviously for 2008 and up. There's also a consensus that versions who are no longer supported by Microsoft do not count for Questions of the Day.[/quote]ohhh sorry .. I still have Bunch of servers running on 2000 :-)I will must say its nice question .Got to learn something new

RE: Nanoseconds

L' Eomot Inversé — Thu, 08 Nov 2012 04:13:58 GMT

[quote][b]matthew.flower (11/8/2012)[/b][hr]While I haven't tried them (have work to do) I am not convinced that any of them "work reliably" when you consider changes to and from daylight savings. In the UK at least daylight savings changes happen at either 1am which becomes 2am or 2am which becomes 1am, so 2am back to midnight can be 1 hour or 3 hours at least once a year. Unless I am missing something none of these methods convert both midnight and the current time to UTC before doing the difference between them so I would argue that the correct answer is none of them.[/quote]You are absolutely correct. The wording needs to be changed to say "except on the days when daylight saving time begins or ends".

RE: Nanoseconds

L' Eomot Inversé — Thu, 08 Nov 2012 04:07:48 GMT

My apologies to all for the incompleteness of option 2. Although I agree with "Arturius" though that it is pretty obvious what is missing and don't think it should have confused anyone, I'll ask Steve to correct it.

RE: Nanoseconds

Koen Verbeeck — Thu, 08 Nov 2012 03:10:56 GMT

[quote][b]derek.colley (11/8/2012)[/b][hr]...Of course, if this argument [b]doesn't[/b] apply to DATETIME2 (rather than DATETIME) then I offer you a hearty apology and withdraw my comments. Still a great QotD. Thank you.[/quote]It doesn't apply to DATETIME2, only to DATETIME :-DApology accepted. :cool:

RE: Nanoseconds

Stuart Davies — Thu, 08 Nov 2012 03:04:26 GMT

[quote][b]sqlnaive (11/8/2012)[/b][hr]This was a good question. I gave wrong answer or better to say incomplete answer (opted for only 4). Method 2 I was not sure because of code written was not complete. from my perspective, losing 2 points is not a big deal than gaining small but important piece of knowledge.[/quote]+ 1Still a good question though - maybe I took the supplied options a little bit too literaly

RE: Nanoseconds

matthew.flower — Thu, 08 Nov 2012 02:44:36 GMT

While I haven't tried them (have work to do) I am not convinced that any of them "work reliably" when you consider changes to and from daylight savings. In the UK at least daylight savings changes happen at either 1am which becomes 2am or 2am which becomes 1am, so 2am back to midnight can be 1 hour or 3 hours at least once a year. Unless I am missing something none of these methods convert both midnight and the current time to UTC before doing the difference between them so I would argue that the correct answer is none of them.

RE: Nanoseconds

sqlnaive — Thu, 08 Nov 2012 02:40:45 GMT

This was a good question. I gave wrong answer or better to say incomplete answer (opted for only 4). Method 2 I was not sure because of code written was not complete. from my perspective, losing 2 points is not a big deal than gaining small but important piece of knowledge.

RE: Nanoseconds

derek.colley — Thu, 08 Nov 2012 01:44:11 GMT

This was a great question and I learned something new.I do, however have a question of my own. I was under the impression that due to the internal timekeeping processes that SQL Server relies upon, it is unwise to rely on precise timings in the range 0-3ms (0-3000ns)? In other words, when comparing dates or using dates as boundaries, we should bear in mind that the range .997 to 1 is unreliable, due to these timing issues?There's a Stack Overflow post about this rounding error here -> [url]http://stackoverflow.com/questions/3584850/sql-server-datetime-parameter-rounding-warning[/url] and some other references available.With this knowledge in mind, the question specifies '... which, if any, will work [b]reliably[/b]?' My answer would be none - since you are measuring in 1/10ths of a ms (100ns) intervals and the rounding error may invalidate any calculations.Of course, if this argument [b]doesn't[/b] apply to DATETIME2 (rather than DATETIME) then I offer you a hearty apology and withdraw my comments. Still a great QotD. Thank you.

RE: Nanoseconds

Carlo Romagnano — Thu, 08 Nov 2012 01:11:02 GMT

Boh!!!!!I do not understand what the question is about, so, how can I answer correctly?The method 2 is incomplete or bad written.:cool:I do not like qotd too long.

RE: Nanoseconds

Koen Verbeeck — Thu, 08 Nov 2012 00:46:15 GMT

Nice question Tom, but I doubt if I'll ever use it :-)(ps: you must be getting old if you can't count to four anymore :-P)

RE: Nanoseconds

Koen Verbeeck — Thu, 08 Nov 2012 00:45:33 GMT

[quote][b]sanket kokane (11/7/2012)[/b][hr]I got it wrong ...you should mention sql server Version in question .Sysdatetime () not present in sql 2000 and 2005[/quote]So, for which versions do you think the question is meant? Obviously for 2008 and up. There's also a consensus that versions who are no longer supported by Microsoft do not count for Questions of the Day.

RE: Nanoseconds

Stewart "Arturius" Campbell — Wed, 07 Nov 2012 23:54:53 GMT

Interesting question, thanks, TomWhile method 2 appears incomplete, it is not difficult to deduce what it should be: given that all other methods refer to sysdatetime(), which is returned as a datetime2 data type, we can presume the actual method should have been [code="sql"]declare @t datetime2 = sysdatetime()select cast(cast(reverse(substring(cast(@t as binary(9)),2,5)) as binary(5)) as bigint)*100[/code]

RE: Nanoseconds

vinu512 — Wed, 07 Nov 2012 23:06:58 GMT

[quote]--Method 2:--use the internal structure of datetime(2)cast(cast(reverse(substring(cast(@t as binary(9)),2,5)) as binary(5)) as bigint)*100[/quote] There is a variable being used "@t".....which is nowhere declared.....When you post example code with the questions then you should make sure that the code is correct.

RE: Nanoseconds

sanket kokane — Wed, 07 Nov 2012 22:22:04 GMT

I got it wrong ...you should mention sql server Version in question .Sysdatetime () not present in sql 2000 and 2005

Nanoseconds

L' Eomot Inversé — Wed, 07 Nov 2012 20:57:16 GMT

Comments posted to this topic are about the item [B]Nanoseconds[/B]