* Son Nguyen
> mysql> select date_created from forums;
> +---------------------+
> | date_created |
> +---------------------+
> | 2002-04-04 19:27:03 |
> +---------------------+
> 1 row in set (0.00 sec)
>
> mysql> SELECT CONCAT(HOUR(now()-date_created), 'H',
> MINUTE(now()-date_created), 'M', SECOND(now()-date_created), 'S') AS
> dated_created from forums;
>
> +---------------+
> | dated_created |
> +---------------+
> | 282H24M24S |
> +---------------+
> 1 row in set (0.00 sec)
>
> mysql> select now();
>
> +---------------------+
> | now() |
> +---------------------+
> | 2002-04-07 01:51:38 |
> +---------------------+
> 1 row in set (0.00 sec)
>
> Please ignore me for the 11 second different between the "select CONCAT
> ..." statement and the "select now()" The thing I would like to ask is
> something wrong with the function HOUR(now()-date_created) Why it
> yielded a wrong number of hours for the subtraction function ???
The HOUR() function is not working the way you seem to think.... from the
manual:
`HOUR(time)'
Returns the hour for `time', in the range `0' to `23'.
mysql> select HOUR('10:05:03');
-> 10
What you probably need to do is to transform both datetime values to
seconds, find the difference, and then calculate the hours. Something like
this:
SELECT
@d1:=unix_timestamp(now()),
@d2:=unix_timestamp(date_created),
@d3:=@d1-@d2,
@h:=floor(@d3/3600),
@m:=floor((@d3-@h*3600)/60),
@s:=@d3-(@h*3600)-(@m*60),
CONCAT(@h,'H',@m,'M',@s,'S')
from forums;
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
