type - sql time




Best way to convert DateTime to “n Hours Ago” in SQL (6)

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

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


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

Something like this extension method?

public static string Stringfy(this DateTime date)
{
    if ((DateTime.Now - date.Date).TotalDays == 0)
        return "Today";

    if ((DateTime.Now - date.Date).TotalDays == 1)
        return "Yesterday";

    // ...

    return "A long time ago, in a galaxy far far away...";
}

Thanks all for the answers, and sorry for the duplicate question. I did not find the duplicate when I was looking for it because I did not really know what search terms to use.

Anyways, I have my problem solved thanks to the PHP translation of the code used by . I made one tiny change in calculating the delta:

$delta = strtotime(gmdate("Y-m-d H:i:s", time())) - $time;

Since I am storing my dates in MySQL as timestamp in the GMT format, I have to use the same for calculating the CURRENT time. This makes for a timezone neutral comparison, which is exactly what is needed in my case.


Date as simple text (e.g. Today, Yesterday, 1 Week Ago) in .Net

You would indeed have to roll your own method of doing this, like JustLoren said.

This is an extension method I've been using. It is GateKiller script made into an extension method. So full credit to him. You could easily change it to however you want it.

public static string ToTimeSinceString(this DateTime value)
{
    const int SECOND = 1;
    const int MINUTE = 60 * SECOND;
    const int HOUR = 60 * MINUTE;
    const int DAY = 24 * HOUR;
    const int MONTH = 30 * DAY;

    TimeSpan ts = new TimeSpan(DateTime.Now.Ticks - value.Ticks);
    double seconds = ts.TotalSeconds;

    // Less than one minute
    if (seconds < 1 * MINUTE)
        return ts.Seconds == 1 ? "one second ago" : ts.Seconds + " seconds ago";

    if (seconds < 60 * MINUTE)
        return ts.Minutes + " minutes ago";

    if (seconds < 120 * MINUTE)
        return "an hour ago";

    if (seconds < 24 * HOUR)
        return ts.Hours + " hours ago";

    if (seconds < 48 * HOUR)
        return "yesterday";

    if (seconds < 30 * DAY)
        return ts.Days + " days ago";

    if (seconds < 12 * MONTH) {
        int months = Convert.ToInt32(Math.Floor((double)ts.Days / 30));
        return months <= 1 ? "one month ago" : months + " months ago";
    }

    int years = Convert.ToInt32(Math.Floor((double)ts.Days / 365));
    return years <= 1 ? "one year ago" : years + " years ago";
}

How to create a friendly date format (for example “submitted 2 days ago”)

This is a duplicate of this question. It has a flurry of code samples on how to accomplish this, in addition to the code this very site uses. I glanced at it and there seems to be a PHP implementation posted there too.

In addition to all this, if are you using jQuery you can do this client-side with something like the timeago plugin. It has the advantage of updating the text as time passes so if you load a page and it says "posted 5 minutes ago" and look again 5 minutes later, it says "posted 10 minutes ago"





function