A wise word is needed...

[Lithium]

Let's say I have a DATETIME field and i need to calculate the interval between that field and current time.

Using php is not an option, as it needs to be done entirely on top of the DB.

I've reached this result:

 

mysql> SELECT SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(NOW(), '2011-07-22 23:59:59')));
+------------------------------------------------------------------+
| SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(NOW(), '2011-07-22 23:59:59'))) |
+------------------------------------------------------------------+
| 34:54:36                                                         |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

 

Still as you can see hours go up to 34, and I need to effectivelly trim to display it in a format quite near the following:

 

1 day 10:54:36 

 

Any help or some pointers would be appreciated.

[Lithium]

Bring Up My Post

[Anonymous]

DROP FUNCTION IF EXISTS time_since;

CREATE FUNCTION time_since( s INT )
      RETURNS CHAR(20) DETERMINISTIC
      RETURN CONCAT
      (
          FLOOR((UNIX_TIMESTAMP() - s) / 86400),
          ' day',
          IF(FLOOR((UNIX_TIMESTAMP() - s) / 86400) = 1, '', 's'),
          ' ',
          SEC_TO_TIME((UNIX_TIMESTAMP() - s) % 86400)
      );

SELECT time_since( UNIX_TIMESTAMP('2011-07-22 23:59:59') );


25 days 18:21:09

[Lithium]

Brilliant! thx much :)