SQL to calculate time since last message?

Question

Question

Scott Beeson · May 13, 2016

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

Discussion (4)0

Log in or sign up to continue

David Loveluck · May 13, 2016

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

1 0

score 0 · Answer 1 · 2016-05-13T13:41:45-04:00

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().

score 0 · Answer 2 · 2016-05-13T15:05:51-04:00

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

score 0 · Answer 3 · 2016-05-13T15:05:07-04:00

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.