Alex, Further to Anvar's suggestions: - in addition to time_to_sec() recommend you look at sec_to_time() - which saves the repeated division to produce 'elapsed time' - alternatively take a look at unix_timestamp() and from_unixtime() which would allow the recording of all times to the second/as seconds, and convert to time/date presentation formats as required - regardless, unless you want large values expressed as hours (even beyond 24 hours) all elapsed time calculations must be divided by 86400 so that the integral part can be passed into a 'date' function, and the remainder into a 'time' function
Times/dates can be stored ready for calculation or ready for display - it is always easier to convert a calculated field for display, than to perform arithmetic on a formatted-for-display field! Regards, =dn ----- Original Message ----- From: "Anvar Hussain K.M." <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: 11 January 2002 04:05 Subject: Re: Date Field + Time Field = Datetime Field? > 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 > > --------------------------------------------------------------------- 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
