X minutes/hours/days ago . how to?

[FourDegreez]

Does anyone have a simple solution to turn a date field into text that reads like "12 minutes ago" or "4 hours ago" or "2 days ago", etc.? Specifically, I'm using MySQL. I bet there's a way to do it right in the select statement, but it's a bit beyond my know-how. I'm not looking for all the detail "10 days, 5 hours and 16 minutes ago", only the largest applicable unit, rounded off. Hopefully someone can get me going in the right direction.

[phranque]

i can't think of a way to do that in sql.
if you're into perl you could check out the [search.cpan.org ], which is the reverse of what you want but might give you some ideas.

it would probably be simple to write such a perl function using the approximate mode of DateCalc in the Date::Manip perl module [search.cpan.org].

something like:

use Date::Manip;
$date1 = ParseDate($string1);
$date2 = ParseDate($string2);
$delta = DateCalc($date1,$date2,\$err,1);
# $delta is YY:MM:WK:DD:HH:MM:SS the years, months, etc. between the two
($y, $m, $w, $d, $h, $m, $s) = split/:/), $delta;
if($y){
$time_ago = $y . " years ago";
}elsif($m){
$time_ago = $m . " months ago";
}elsif($w){
$time_ago = $w . " weeks ago";
}elsif($d){
$time_ago = $d . " days ago";
}elsif($h){
$time_ago = $h . " hours ago";
}elsif($m){
$time_ago = $m . " minutes ago";
}elsif($s){
$time_ago = $s . " seconds ago";
}

some of the above i stole from the pod and some i made up so use at your own risk...

[ZydoSEO]

I don't have MySQL installed, but perhaps you can translate the example below to MySQL. I did it in SQL*Server...

1) Created a table w/ a date field in it:

CREATE TABLE tmpDateTest
(mydate DATETIME)

2) Inserted a row in the table with the current date time:

INSERT INTO tmpDateTest (mydate) VALUES (GETDATE())

3) Wrote a SQL script (could be placed in a stored procedure) to extract the date value previously inserted into the tmpDateTest table and place it in a SQL variable (could be passed in as a parameter to a stored procedure). I then retrieved the current date/time and placed it into a SQL variable. I placed both in a variable since I'd be doing multiple compares and I didn't want the current date/time to change between compares. Then I used DATEDIFF to compare the two dates.

DECLARE @lvDate DATETIME
DECLARE @lvNow DATETIME

SELECT @lvDate=mydate FROM tmpDateTest

SELECT @lvNow=getdate()

SELECT
DATEDIFF(Year, @lvDate, @lvNow) YearsAgo,
DATEDIFF(Month, @lvDate, @lvNow) MonthsAgo,
DATEDIFF(Day, @lvDate, @lvNow) DaysAgo,
DATEDIFF(Hour, @lvDate, @lvNow) HoursAgo,
DATEDIFF(Minute, @lvDate, @lvNow) MinutesAgo,
DATEDIFF(Second, @lvDate, @lvNow) SecondsAgo

This returns a single row with the difference in the two dates described as years, months, days, hours, minutes, and seconds.

When I did the test it was 11 minutes and some change difference between the two dates. Your PhP code could look at years first. If YearsAgo > 0 then build your string with either ' year ago' or ' years ago' depending on whether it is 1 or >1, respectively. If YearsAgo = 0 then repeat for MonthsAgo... then DaysAgo... etc. until you find a value that is >0.

[edited by: ZydoSEO at 5:46 am (utc) on Jan. 20, 2008]

[rocknbil]

Maybe I don't understand the problem. Since "now" is always shifting, if you want "x [seconds¦minutes¦hours¦days¦months¦years] ago", you would just use date_sub:

select date_sub(now(), interval $how_many_days day);
select date_sub(now(), interval $how_many_seconds second);

On a static field, where field is my_date,

select date_sub(my_date, interval $how_many_days day) from your_table;

If you want to replace that value with your past date,

update my_table set my_date date_sub(my_date, interval $how_many_days day) where id='$some_id';

The last would of course require you select the id first.

?