Written by

Lead Technical Analyst at Missouri Health Connection

Question Scott Beeson · May 13, 2016

SQL to calculate time since last message?

#Caché #SQL

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

Add reply

Comments

Stefan Wittmann · May 13, 2016

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

0 0

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

0 0

Scott Beeson May 13, 2016 to David Loveluck

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

0 0

Scott Beeson · May 13, 2016

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.

0 0