在 SQL 中将 DateTime 转换为“n 小时前”的最佳方法

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

提问人：Jared 提问时间：9/9/2008 最后编辑：SqlRyanJared 更新时间：10/25/2021 访问量：13560

问：

我编写了一个 SQL 函数，用于将 SQL 中的日期时间值转换为更友好的“n 小时前”或“n 天前”等类型的消息。我想知道是否有更好的方法可以做到这一点。

（是的，我知道“不要在 SQL 中这样做”，但出于设计原因，我必须这样做）。

这是我写的函数：

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
SQL DateTime 函数

评论

答：

1赞 Craig 9/9/2008 #1

您的代码看起来功能正常。至于更好的方法，那将是主观的。您可能想查看此页面，因为它处理 SQL 中的时间跨度。

7赞 ConroyP 9/9/2008 #2

正如你所说，我可能不会在 SQL 中这样做，但作为一个思想练习，有一个 MySQL 实现：

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

基于 MySQL 日期和时间手册页上看到的类似示例

3赞 JosephStyons 9/9/2008 #3

在 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
1赞 Jasmine 9/9/2008 #4

这个怎么样？您可以扩展此模式以执行“年”消息，并且可以输入“1 天”或“1 小时”的检查，这样它就不会显示“1 天前”......

我喜欢SQL中的CASE语句。

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')
2赞 Hafthor 9/9/2008 #5

我的尝试 - 这是针对 MS SQL 的。它支持“ago”和“from now”，复数化，它不使用舍入或 datediff，而是截断 - datediff 在 8/30 和 9/1 之间给出 1 个月的差异，这可能不是你想要的。四舍五入给出 9/1 和 9/16 之间的 1 个月差异。同样，可能不是你想要的。

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

评论

0赞 John 9/29/2009
你试过这个吗？我不相信你可以从 t-sql 中的函数调用'getDate（）'
0赞 Hafthor 9/30/2009
我确实试过了。刚刚在 SQL 2005、2008 和 2008R2 CTP 上再次尝试。工程。GETDATE 使函数具有非确定性，这在一定程度上限制了它的使用。来自 SQL2000 BOL：“在 Microsoft SQL Server 2000 中，不能在两种类型的 Transact-SQL 表达式中指定非确定性函数： * 如果computed_column_expression引用任何非确定性函数，则无法在计算列上创建索引。* 如果视图引用任何非确定性函数，则无法在视图上创建聚簇索引。
0赞 Hafthor 10/1/2009
可以肯定的是，这在 SQL 2000 上不起作用，因为非确定性 UDF 是 2005 年的新功能，但即使有针对性的黑客攻击。最坏的情况是，如果传入 GETDATE（），您可以修改 UDF 以添加第二个日期时间参数。
1赞 user176505 9/21/2009 #6

感谢上面发布的各种代码。

正如 Hafthor 所指出的，原始代码在舍入方面存在局限性。我还发现他的代码踢出的一些结果与我的预期不符，例如周五下午->周一早上将显示为“2 天前”。我想我们都会在 3 天前打电话，即使 3 个完整的 24 小时时段还没有过去。

所以我修改了代码（这是 MS SQL）。免责声明：我是一个新手TSQL编码员，所以这很笨拙，但有效！

我做了一些覆盖 - 例如，任何长达 2 周的内容都以天表示。任何超过2个月的时间都以周为单位表示。超过这个范围的任何事情都是以月为单位，等等。似乎是表达它的直观方式。

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
0赞 Eric Bynum 9/15/2015 #7

上面的帖子给了我一些好主意，所以这里是另一个功能，适用于使用 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

并实现：

    SELECT TIME_ELAPSED = DBO.FN_TIME_ELAPSED(AUDIT_TIMESTAMP)
    FROM SOME_AUDIT_TABLE
0赞 Nitin Luhar 10/25/2021 #8 
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

评论

0赞 Community 10/25/2021
您的答案可以通过其他支持信息进行改进。请编辑以添加更多详细信息，例如引文或文档，以便其他人可以确认您的答案是正确的。您可以在帮助中心找到有关如何写出好答案的更多信息。

上一个：在 python 中找出 2 次之间差异的最简单方法是什么？

下一个：在测试期间覆盖 DateTime.Now 的好方法是什么？