Scott Beeson · May 13, 2016

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 566
Discussion (4)1
Log in or sign up to continue

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


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

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.