You can't just subtract datetime fields. Check the date and time functions in the manual.
Jim Hogan wrote:
Hello!
I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example:
I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine YYYY-MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds.
I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin;
When I browse the resulting data, the elapsed seconds often do, but do not always, agree with my own calculation of the elapsed seconds. For example, the first 12 rows look like:
My calc elapsed_time
45 85
11 11
16 16
9 9
22 62
14 14
73 73
59 99
65 105
20 20
12 12
43 4083
That last one is a bit off!! There appeared to be a common problem with a 40-second difference, but obvioulsy that last one throws that pattern out the window!
Anyhow, I have Googled quite a bit and have looked through http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators
By way of troubleshooting, I created 6 other elapsed_time variables using
small, medium and big int, and float, double and decimal to see if the
result was different, but the result was the same for each.
I'm guessing (and hoping!!) that I am missing something *very* basic in the structure of my UPDATE and will absolutely appreciate any pointers that could fix it.
--------------------------------------------------------------------- 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
