Jim, Sunday, November 03, 2002, 8:44:43 PM, you wrote: JH> I am working on an analysis that is very much dependent on calculating JH> time differences in seconds. The most simple example:
JH> I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ JH> seconds (INT). The data in these DATETIMES looks fine YYYY-MM-DD HH:MM:SS JH> as expected and actual differences between time_end and time_begin are JH> usually on the order of 20-120 seconds. JH> I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; JH> When I browse the resulting data, the elapsed seconds often do, but do JH> not always, agree with my own calculation of the elapsed seconds. For JH> example, the first 12 rows look like: JH> My calc elapsed_time JH> 45 85 JH> 11 11 JH> 16 16 JH> 9 9 JH> 22 62 JH> 14 14 JH> 73 73 JH> 59 99 JH> 65 105 JH> 20 20 JH> 12 12 JH> 43 4083 JH> That last one is a bit off!! There appeared to be a common problem with a JH> 40-second difference, but obvioulsy that last one throws that pattern out JH> the window! JH> Anyhow, I have Googled quite a bit and have looked through JH> http://www.mysql.com/doc/en/Date_and_time_functions.html several times and JH> did not see a different function of syntax for the UPDATE that I want to JH> do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 JH> I can simply use +/- operators JH> By way of troubleshooting, I created 6 other elapsed_time variables using JH> small, medium and big int, and float, double and decimal to see if the JH> result was different, but the result was the same for each. You can't apply arithmetic directly on DATETIME columns. You should use TIME_TO_SEC(time_end)-TIME_TO_SEC(time_begin) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com --------------------------------------------------------------------- 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
