Pages

Monday, June 17, 2013

SQL Server: Friendly Time Differences

Ok, so I always like the Facebook smart date displays, like
posted by Bob Loblaw about 12 minutes ago
So I set about recreating this on my own sites in SQL Server.  Here is a fairly easy way to produce this kind of friendly time differences.

Select *
,iif(
datediff(DAY, errdate, getdate()) >0 ,
cast(datediff(DAY, errdate, getdate()) as varchar(90))+' days  ',
''
)+
iif(
datediff(HOUR, errdate, getdate()) >0 ,
cast(datediff(HOUR, errdate, getdate()) % 24 as varchar(90))+' hours  ',
''
)+
iif(
datediff(MINUTE, errdate, getdate()) >0 ,
cast(datediff(MINUTE, errdate, getdate()) % 60 as varchar(90))+' minutes  ',
''
)+
iif(
datediff(SECOND, errdate, getdate()) >0 ,
cast(datediff(SECOND, errdate, getdate()) % 60 as varchar(90))+' seconds ago.',
''
)
[friendlytime]
from errorlog
--where errdate > DATEADD(HOUR, -1, GETDATE())
order by errdate


This produces a nice, friendly time difference that is better than a list of raw dates for human interpretation.


errdatepageerrmessagefriendlytime
1961-06-17 11:07:21.820TESTTHIS IS A TEST ERROR MESSAGE18993 days  0 hours  20 minutes  36 seconds ago.
2010-06-17 11:06:05.470TESTTHIS IS A TEST ERROR MESSAGE1096 days  0 hours  21 minutes  52 seconds ago.
2012-06-17 11:04:41.123TESTTHIS IS A TEST ERRORMESSAGE365 days  0 hours  23 minutes  16 seconds ago.
2013-06-17 11:07:59.197TESTTHIS IS A TEST ERROR MESSAGE20 minutes  58 seconds ago.

Alternately, if you don't care to display all the way down to seconds when the time span is many days, you can nest it like this to only show the most relevant time gap.

iif(
datediff(DAY, errdate, getdate()) >0 ,
cast(datediff(DAY, errdate, getdate()) as varchar(90))+' days ago',
iif(
datediff(HOUR, errdate, getdate()) >0 ,
cast(datediff(HOUR, errdate, getdate()) as varchar(90))+' hours ago',
iif(
datediff(MINUTE, errdate, getdate()) >0 ,
cast(datediff(MINUTE, errdate, getdate()) as varchar(90))+' minutes ago',
iif(
datediff(SECOND, errdate, getdate()) >0 ,
cast(datediff(SECOND, errdate, getdate()) as varchar(90))+' seconds ago.',
'now'
)
)
)
) [friendlytime2]

This produces a result as follows:

18993 days ago
1096 days ago
365 days ago
38 minutes ago
...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

No comments:

Share This!

Contact Us

Name

Email *

Message *