将DateTime转换为“n Hours Ago”的最佳方式在SQL中 [英] Best way to convert DateTime to "n Hours Ago" in SQL

问题描述

我写了一个SQL函数，将SQL中的datetime值转换为更友好的n Hours Ago或n Days Ago等消息类型。我想知道是否有更好的方式来做到这一点。

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.

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

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

这是我写的功能：

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

推荐答案

正如你所说，我有问题ably不会在SQL中执行，但是作为一个思想练习有一个MySQL实现：

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

根据 MySQL日期和时间手册页

这篇关于将DateTime转换为“n Hours Ago”的最佳方式在SQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！