带有 MSSQL 2008 的 Twitter 样式时间轴

Question

我正在尝试比较日期并显示 MSSQL 2008 的 Twitter 样式格式（2 秒前、1 分钟前、2 天前等）的差异。任何帮助将不胜感激。提前谢谢。

Answer 1

这会对你有帮助吗？您可能需要在几天和几周内添加更多条件

declare @dt datetime =getdate();
declare @posttime datetime =dateadd(SECOND,-2,getdate());
declare @diff bigint;
set @diff=datediff(SECOND,@posttime,@dt)
select case when @diff<60 then
            convert(varchar(2),@diff)+' seconds ago' 
            when @diff between 60 and 3599 then
                convert(varchar(2),@diff/60)+' minutes ago' 
            else
                convert(varchar(2),@diff/3600)+' hours ago' 
        end
--Next post which was about 1 minute ago
set @posttime=dateadd(SECOND,-62,getdate());
set @diff=datediff(SECOND,@posttime,@dt)
select 
    case when @diff<60 then
                convert(varchar(2),@diff)+' seconds ago' 
            when @diff between 60 and 3599 then
                convert(varchar(2),@diff/60)+' minutes ago'         
            else
                convert(varchar(2),@diff/3600)+' hours ago' 
            end

Answer 2

这将帮助你

DECLARE @D DATETIME 
SET @D = CAST('2014-02-17 13:10:50.053' AS DATETIME)
SELECT @D 
SELECT GETDATE() 
SELECT CASE WHEN    DATEDIFF(HH,@D,GETDATE()) > 48 THEN CAST( DATEDIFF(D,@D,GETDATE()) AS VARCHAR(20))+'  DAYS AGO'
            WHEN    DATEDIFF(HH,@D,GETDATE()) > 24 THEN CAST( DATEDIFF(D,@D,GETDATE()) AS VARCHAR(20))+'  DAY AGO'
            WHEN    DATEDIFF(HH,@D,GETDATE()) > 1 THEN CAST( DATEDIFF(HH,@D,GETDATE()) AS VARCHAR(20))+' HOURS AGO'
            WHEN    DATEDIFF(HH,@D,GETDATE()) = 1 THEN CAST( DATEDIFF(HH,@D,GETDATE()) AS VARCHAR(20))+' HOUR AGO'

            WHEN    DATEDIFF(MINUTE,@D,GETDATE()) > 1 THEN CAST( DATEDIFF(MINUTE,@D,GETDATE()) AS VARCHAR(20))+' MINUTES AGO'
            WHEN    DATEDIFF(MINUTE,@D,GETDATE()) = 1 THEN CAST( DATEDIFF(MINUTE,@D,GETDATE()) AS VARCHAR(20))+' MINUTE AGO'
            WHEN    DATEDIFF(SECOND,@D,GETDATE()) > 1 THEN CAST( DATEDIFF(SECOND,@D,GETDATE()) AS VARCHAR(20))+' SECS AGO'
            WHEN    DATEDIFF(SECOND,@D,GETDATE()) = 1 THEN CAST( DATEDIFF(SECOND,@D,GETDATE()) AS VARCHAR(20))+' SEC AGO'
       END TIMESPAN