Best way to convert DateTime to "n Hours Ago" in SQL
Solution 1
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
Solution 2
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
Solution 3
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
Solution 4
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.
Solution 5
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')
Jared
I've been in front of a computer every day since age 8 and have getting paid for it for about 16 years.
Updated on June 11, 2022Comments
-
Jared almost 2 years
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
-
John over 14 yearsDid you try this? I don't believe you can call 'getDate()' from a function in t-sql
-
Hafthor over 14 yearsI 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 over 14 yearsPretty 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().
-
Admin over 2 yearsYour 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.