On Wed, 2002-12-11 at 04:44, Tom Roos wrote:
> hi
>
> i want to build a query in which i have 2 datetime fields which i subtract
> from one another. what is the result set? is it in (milli)seconds, is it a
> unix timestamp? what type of convertion do i have to apply to report the
> difference in minutes?
I usually convert to unix time (seconds since 1970) then subtract. Then
use sec_to_time to get hours:mins:sec, or convert to a displayable value
in the client software.
select unix_timestamp('2002-12-02 15:30:00') -
unix_timestamp('2002-11-15 21:11:08') as tdiff;
+---------+
| tdiff |
+---------+
| 1448332 |
+---------+
select sec_to_time(unix_timestamp('2002-12-02 15:30:00') -
unix_timestamp('2002-11-15 21:11:08')) as tdiff;
+-----------+
| tdiff |
+-----------+
| 402:18:52 |
+-----------+
Or to get minutes, rather than using sec_to_time, divide by 60.
You can't just subtract the datetime values from each other because
they'll be converted to integers (that is, '2002-12-02 15:30:00' becomes
20021202153000) and then subtraction doesn't work as each place in the
resultant integer doesn't fully hold all digits 0 through 9.
Fortunately, date comparsion (using > and <) still work because the date
is arranged with the most signifcant portion being further left.
Andy.
---------------------------------------------------------------------
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
