On Mon, 4 Nov 2002, gerald_clark wrote: > You can't just subtract datetime fields. Check the date and time functions > in the manual.
Thanks, I should know I run the risk of posting a RTFM/FAQ when I'm tired! I got off track by misinterpreting "In MySQL Version 3.23, you can use + and - instead of DATE_ADD() and DATE_SUB()" in the manual when all I needed was UNIX_TIMESTAMP() (that I misinterpreted when I looked at it the first time). Anyhow, I can *nearly* "just subtract datetime fields" and all is right with the world. Jim On Mon, 4 Nov 2002, gerald_clark wrote: > 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 > --------------------------------------------------------------------- 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
