SQL to calculate time since last message?

This is a bit weird.  I'm trying to use `DATEDIFF()` to calculate the time since the last message on an interface.  For some reason, when no messages are received, the number steadily decreases.  This is the opposite of what should happen.  Here is my current query and a few sequential result sets:

SELECT getDate() as now, max(TimeCreated) as latest, DATEDIFF (s,getdate(), max(TimeCreated)) as difference
FROM EnsLib_HL7.Message

 

now                  latest               difference  
-------------------  -------------------  ----------  
2016-05-13 11:05:31  2016-05-13 08:51:16  9945
2016-05-13 11:08:16  2016-05-13 08:51:16  9780    
2016-05-13 11:08:36  2016-05-13 08:51:16  9760 

  • 0
  • 0
  • 390
  • 2
  • 2

Answers

Not sure why this is happening. This is working fine for me:

SELECT now() as tnow, max('2016-05-13 08:51:16') as latest, DATEDIFF (s,now(), max('2016-05-13 08:51:16')) as difference
FROM Sample.Person

tnow                                   latest                                  difference
2016-05-13 13:38:08    2016-05-13 08:51:16    -17212
2016-05-13 13:38:42    2016-05-13 08:51:16    -17246
2016-05-13 13:38:56    2016-05-13 08:51:16    -17260

I am using the function now().

I think you might be getting local times and UTC times mixed up.

TimeCreated is stored in UTC and now() gives you local time so your arithmetic could be giving a negative number

 

Dave

That was the problem, I just posted above.  I guess the software I use just displayed it in local format automatically.

Comments

Apparently MAX() was converting the timestamp to UTC or something.  I had to use %EXTERNAL.  This works:

SELECT DATEDIFF(s, %EXTERNAL(MAX(TimeCreated)), getdate()) as difference
FROM EnsLib_HL7.Message

I don't really understand it, but apparently this guy does.