Hi Alex, I don't think your problem will solved by making the time columns to full datetime columns as there is no functions to subtract two datetime values directly.
But you can can keep the time columns and go on like this: convert the time into seconds using time_to_sec function. subtract fromtime from totime. this will yield the difference in seconds now you can take the elapsed time in hours,minutes or seconds. The sql might be something like: Select (time_to_sec(totime) - time_to_sec(fromtime)) / 60 / 60 as hours from table. Section 7.4.11 of the manual explains date and time functions. Anvar At 06:00 PM 10/01/2002 +0000, you wrote: >I've been working with a timesheet database, where all the employees of my >small business enter in the hours they work on projects. I've been >storing, for each record, a date of work, a start time, and a finish time. >When I attempted to write a Perl script to display invoices, though, I ran >into the issue that subtracting one time from another yields inconsistent >results. Thus, I'm going to convert to datetime fields, which would store >just the start and finish times as datetime. >I'd like to automate the switchover, so I don't have to go through and >maually update. I thought at first that "UPDATE time_worked SET dtstart = >concat(datework, start);" would work, but that gave me a syntax error. I >can't seem to find anything in the manual that would help, either. >Is there any way to do this without going through and manually updating? >I'd really appreciate any hints you could give. FYI, I'm running MySQL >3.23.37. >Thanks, >Alex Kirk > >--------------------------------------------------------------------- >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
