I thought this would be simple, but I have a problem.
SELECT DATEDIFF (s,getdate(), max(TimeCreated)) as difference
FROM Ens.MessageHeader
My expectation was that this would show the difference between the current time and the timestamp of the most recent record. If I execute it, I get something lik e 15057 which could possibly be right. However, the next time I execute it, it is lower. There haven't been any new records. Right now it's at 15024. Still no new records. The most recent timestamp is currently 2016-05-13 08:51:16
The idea is that I can watch this counter and when it resets to zero I know there is a new message.
What am I missing?
Update I added some additional data to the query to help see what is going on:
SELECT getDate() as now, max(TimeCreated) as latest, DATEDIFF (s,getdate(), max(TimeCreated)) as difference
FROM Ens.MessageHeader
Here are a couple result sets:
now latest difference
------------------- ------------------- ----------
2016-05-13 09:50:45 2016-05-13 08:51:16 14431
2016-05-13 09:52:29 2016-05-13 08:51:16 14327
2016-05-13 09:52:50 2016-05-13 08:51:16 14306
The gotcha here has to do with the order of the arguments in your call to DATEDIFF() AND the fact that TimeCreated is a UTC timestamp that gets converted to local time in some situations, but it looks like it is not being converted when inside DATEDIFF. So your query as written looks like it is returning MAX(TimeCreated + UTC_offset) - getdate(), which is a positive number that is getting smaller.
As @Gordon Linoff's answer states, you should have MAX(TimeCreated) as the second argument to DATEDIFF as that is the starting time for the diff. You also likely want to wrap that in the %EXTERNAL function to make 110% sure that it's being converted to local time before running the comparison:
SELECT DATEDIFF(second, %EXTERNAL(MAX(TimeCreated)), getdate())
FROM Ens.MessageHeader
The above query gives me the results I'd expect as we are comparing the two times in the right order and in the same time zone!
All that said, I am not sure whether the DATEDIFF() behaviour about taking the pre-conversion value is intended or a bug.