Retrieving seconds since last record

Question

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

When I execute the same code on my own table that has a date value, I get a negative number of seconds back. — GendoIkari, May 13 '16 at 14:45

score 4 · Accepted Answer · answered May 13 '16 at 17:51

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.

score 0 · Answer 2 · answered May 13 '16 at 14:45

This is curious. What is happening is that the TimeCreated is in the "future". DATEDIFF(x, <exp1>, <exp2>) calculates the difference of <exp2> - <exp1>.

So, if TimeCreate is supposed to be in the past, then you can try:

SELECT DATEDIFF(second, max(TimeCreated), getdate())
FROM Ens.MessageHeader;

You'll then get a negative number and wonder what is happening. As more seconds go by, the negative number goes toward zero, because the current date/time is getting closer to max(TimeCreated).

My guess is that you have a time zone issue. Or, you have some bias that influences TimeCreated. You may want to review other ways of getting the current date/time value.

Retrieving seconds since last record

2 Answers2