Best way to convert DateTime to "n Hours Ago" in SQL

Question

I wrote a SQL function to convert a datetime value in SQL to a friendlier "n Hours Ago" or "n Days Ago" etc type of message. And I was wondering if there was a better way to do it.

(Yes I know "don't do it in SQL" but for design reasons I have to do it this way).

Here is the function I've written:

CREATE FUNCTION dbo.GetFriendlyDateTimeValue
(
    @CompareDate DateTime
)
RETURNS nvarchar(48)
AS
BEGIN
    DECLARE @Now DateTime
    DECLARE @Hours int
        DECLARE @Suff nvarchar(256)
    DECLARE @Found bit

    SET @Found = 0
    SET @Now = getDate()
    SET @Hours = DATEDIFF(MI, @CompareDate, @Now)/60

    IF @Hours <= 1
    BEGIN
        SET @Suff = 'Just Now'
        SET @Found = 1
        RETURN @Suff
    END

    IF @Hours < 24
    BEGIN
        SET @Suff = ' Hours Ago'
        SET @Found = 1
    END

    IF @Hours >= 8760 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 8760
        SET @Suff = ' Years Ago'
        SET @Found = 1
    END

    IF @Hours >= 720 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 720
        SET @Suff = ' Months Ago'
        SET @Found = 1
    END

    IF @Hours >= 168 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 168
        SET @Suff = ' Weeks Ago'
        SET @Found = 1
    END

    IF @Hours >= 24 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 24
        SET @Suff = ' Days Ago'
        SET @Found = 1
    END

    RETURN Convert(nvarchar, @Hours) + @Suff
END

ConroyP · Accepted Answer · 2008-09-08T17:21:43.330

As you say, I probably wouldn't do it in SQL, but as a thought exercise have a MySQL implementation:

CASE
    WHEN compare_date between date_sub(now(), INTERVAL 60 minute) and now() 
        THEN concat(minute(TIMEDIFF(now(), compare_date)), ' minutes ago')

    WHEN datediff(now(), compare_date) = 1 
        THEN 'Yesterday'

    WHEN compare_date between date_sub(now(), INTERVAL 24 hour) and now() 
        THEN concat(hour(TIMEDIFF(NOW(), compare_date)), ' hours ago')

    ELSE concat(datediff(now(), compare_date),' days ago')
END

Based on a similar sample seen on the MySQL Date and Time manual pages

JosephStyons · Answer 2 · 2008-09-08T17:39:59.353

In Oracle:

select
  CC.MOD_DATETIME,
  'Last modified ' ||
  case when (sysdate - cc.mod_datetime) < 1
       then round((sysdate - CC.MOD_DATETIME)*24) || ' hours ago'
       when (sysdate - CC.MOD_DATETIME) between 1 and 7
       then round(sysdate-CC.MOD_DATETIME) || ' days ago'
       when (sysdate - CC.MOD_DATETIME) between 8 and 365
       then round((sysdate - CC.MOD_DATETIME) / 7) || ' weeks ago'
       when (sysdate - CC.MOD_DATETIME) > 365   
       then round((sysdate - CC.MOD_DATETIME) / 365) || ' years ago'
       end
from 
  customer_catalog CC

Hafthor · Answer 3 · 2008-09-30T15:32:09.580

2

My attempt - this is for MS SQL. It supports 'ago' and 'from now', pluralization and it doesn't use rounding or datediff, but truncation -- datediff gives 1 month diff between 8/30 and 9/1 which is probably not what you want. Rounding gives 1 month diff between 9/1 and 9/16. Again, probably not what you want.

CREATE FUNCTION dbo.GetFriendlyDateTimeValue( @CompareDate DATETIME ) RETURNS NVARCHAR(48) AS BEGIN
declare @s nvarchar(48)
set @s='Now'
select top 1 @s=convert(nvarchar,abs(n))+' '+s+case when abs(n)>1 then 's' else '' end+case when n>0 then ' ago' else ' from now' end from (
 select convert(int,(convert(float,(getdate()-@comparedate))*n)) as n, s from (
  select 1/365 as n, 'Year' as s union all
  select 1/30, 'Month' union all
  select 1, 'Day' union all
  select 7, 'Week' union all
  select 24, 'Hour' union all
  select 24*60, 'Minute' union all
  select 24*60*60, 'Second'
 ) k
) j where abs(n)>0 order by abs(n)
return @s
END

edited Sep 30 '08 at 15:32

answered Sep 08 '08 at 18:23

Hafthor

16,358
9
56
65

Did you try this? I don't believe you can call 'getDate()' from a function in t-sql – John Sep 28 '09 at 20:57
I did try it. Just tried it again on SQL 2005, 2008 and 2008R2 CTP. Works. GETDATE makes the function non-deterministic which limits its use a little. From SQL2000 BOL: "In Microsoft SQL Server 2000, nondeterministic functions cannot be specified in two types of Transact-SQL expressions: * An index cannot be created on a computed column if the computed_column_expression references any nondeterministic functions. * A clustered index cannot be created on a view if the view references any nondeterministic functions." – Hafthor Sep 29 '09 at 19:55
Pretty sure this would not work on SQL 2000 since non-deterministic UDFs were new for 2005, but there are hack-arounds for that even. Worst case, you could mod the UDF to add a second datetime parameter were you'd pass in GETDATE(). – Hafthor Sep 30 '09 at 18:48

score 1 · Answer 4 · answered Sep 21 '09 at 10:55

Thanks for the various code posted above.

As Hafthor pointed out there are limitations of the original code to do with rounding. I also found that some of the results his code kicked out didn't match with what I'd expect e.g. Friday afternoon -> Monday morning would show as '2 days ago'. I think we'd all call that 3 days ago, even though 3 complete 24 hour periods haven't elapsed.

So I've amended the code (this is MS SQL). Disclaimer: I am a novice TSQL coder so this is quite hacky, but works!!

I've done some overrides - e.g. anything up to 2 weeks is expressed in days. Anything over that up to 2 months is expressed in weeks. Anything over that is in months etc. Just seemed like the intuitive way to express it.

CREATE FUNCTION [dbo].[GetFriendlyDateTimeValue]( @CompareDate DATETIME ) RETURNS NVARCHAR(48) AS BEGIN
declare @s nvarchar(48)

set @s='Now'
select top 1 @s=convert(nvarchar,abs(n))+' '+s+case when abs(n)>1 then 's' else '' end+case when n>0 then ' ago' else ' from now' end from (
 select convert(int,(convert(float,(getdate()-@comparedate))*n)) as n, s from (
  select 1/365 as n, 'year' as s union all
  select 1/30, 'month' union all
  select 1/7, 'week' union all
  select 1, 'day' union all
  select 24, 'hour' union all
  select 24*60, 'minute' union all
  select 24*60*60, 'second'
 ) k
) j where abs(n)>0 order by abs(n)

if @s like '%days%'
BEGIN
 -- if over 2 months ago then express in months
 IF convert(nvarchar,DATEDIFF(MM, @CompareDate, GETDATE())) >= 2
 BEGIN
  select @s = convert(nvarchar,DATEDIFF(MM, @CompareDate, GETDATE())) + ' months ago'
 END

 -- if over 2 weeks ago then express in weeks, otherwise express as days
 ELSE IF convert(nvarchar,DATEDIFF(DD, @CompareDate, GETDATE())) >= 14
 BEGIN
  select @s = convert(nvarchar,DATEDIFF(WK, @CompareDate, GETDATE())) + ' weeks ago'
 END

 ELSE
  select @s = convert(nvarchar,DATEDIFF(DD, @CompareDate, GETDATE())) + ' days ago'
END

return @s
END

score 1 · Answer 5 · answered Sep 08 '08 at 17:12

1

Your code looks functional. As for a better way, that is going to get subjective. You might want to check out this page as it deals with time spans in SQL.

answered Sep 08 '08 at 17:12

Craig

11,614
13
44
62

score 1 · Answer 6 · edited Sep 08 '08 at 19:28

How about this? You could expand this pattern to do "years" messages, and you could put in a check for "1 day" or "1 hour" so it wouldn't say "1 days ago"...

I like the CASE statement in SQL.

drop function dbo.time_diff_message    
GO

create function dbo.time_diff_message (
    @input_date datetime
)
returns varchar(200)    
as    
begin    
declare @msg varchar(200)    
declare @hourdiff int

set @hourdiff = datediff(hour, @input_date, getdate())    
set @msg = case when @hourdiff < 0 then ' from now' else ' ago' end    
set @hourdiff = abs(@hourdiff)    
set @msg = case when @hourdiff > 24 then convert(varchar, @hourdiff/24) + ' days' + @msg
                else convert(varchar, @hourdiff) + ' hours' + @msg
            end

return @msg
end

GO    
select dbo.time_diff_message('Dec 7 1941')

score 0 · Answer 7 · answered Sep 14 '15 at 19:58

The posts above gave me some good ideas so here is another function for anyone using SQL Server 2012.

    CREATE FUNCTION [dbo].[FN_TIME_ELAPSED] 
    (
        @TIMESTAMP DATETIME
    )
    RETURNS VARCHAR(50)
    AS
    BEGIN

    RETURN 
    (
        SELECT TIME_ELAPSED = 
            CASE
                WHEN @TIMESTAMP IS NULL THEN NULL
                WHEN MINUTES_AGO < 60 THEN CONCAT(MINUTES_AGO, ' minutes ago')
                WHEN HOURS_AGO < 24 THEN CONCAT(HOURS_AGO, ' hours ago')
                WHEN DAYS_AGO < 365 THEN CONCAT(DAYS_AGO, ' days ago')
                ELSE CONCAT(YEARS_AGO, ' years ago') END
        FROM ( SELECT MINUTES_AGO = DATEDIFF(MINUTE, @TIMESTAMP, GETDATE()) ) TIMESPAN_MIN
        CROSS APPLY ( SELECT HOURS_AGO = DATEDIFF(HOUR, @TIMESTAMP, GETDATE()) ) TIMESPAN_HOUR
        CROSS APPLY ( SELECT DAYS_AGO = DATEDIFF(DAY, @TIMESTAMP, GETDATE()) ) TIMESPAN_DAY
        CROSS APPLY ( SELECT YEARS_AGO = DATEDIFF(YEAR, @TIMESTAMP, GETDATE()) ) TIMESPAN_YEAR
    )
    END
    GO

And the implementation:

    SELECT TIME_ELAPSED = DBO.FN_TIME_ELAPSED(AUDIT_TIMESTAMP)
    FROM SOME_AUDIT_TABLE

score 0 · Answer 8 · edited Oct 25 '21 at 11:01

0

CASE WHEN datediff(SECOND,OM.OrderDate,GETDATE()) < 60 THEN
  CONVERT(NVARCHAR(MAX),datediff(SECOND,OM.OrderDate,GETDATE())) +' seconds ago'
WHEN datediff(MINUTE,OM.OrderDate,GETDATE()) < 60 THEN
  CONVERT(NVARCHAR(MAX),datediff(MINUTE,OM.OrderDate,GETDATE())) +' minutes ago'
WHEN datediff(HOUR,OM.OrderDate,GETDATE()) < 24 THEN
  CONVERT(NVARCHAR(MAX),datediff(HOUR,OM.OrderDate,GETDATE())) +' hours ago'
WHEN datediff(DAY,OM.OrderDate,GETDATE()) < 8 THEN
  CONVERT(NVARCHAR(MAX),datediff(DAY,OM.OrderDate,GETDATE())) +' Days ago'
ELSE FORMAT(OM.OrderDate,'dd/MM/yyyy hh:mm tt') END AS TimeStamp

edited Oct 25 '21 at 11:01

Toni

1,555
4
15
23

answered Oct 25 '21 at 05:59

Nitin Luhar

1
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 25 '21 at 06:12

Best way to convert DateTime to "n Hours Ago" in SQL

8 Answers8

Linked