Hey group, The answer to my question was yes, thanks Paul.
For the groups knowledge, the scenario was that I was trying to subtract two datetime values and sum up the differences. mySQL stores the datetimes in a text like integer format. For example 2001-01-02 is stored as an integer 20010102. I won't ask why. It appears to be an almost meaningless format except there are functions available to make it meaningful. I overlooked the UNIX_TIMESTAMP which converts the stored datetime into the number of seconds since the epoch (1970). I saw the word UNIX and moved on to the next function which was my demise. >SELECT UserId, SUM((UNIX_TIMESTAMP(LogoutDate) - > UNIX_TIMESTAMP(LoginDate))) / 3600 > AS secs FROM tbl_name GROUP BY UserId; Works great. Mark Rissmann At 4:21 PM -0800 11/25/01, Mark Rissmann wrote: >>Thanks Mr. DuBois, >> >>I must have missed a function (or banged my head to many times). How do >>I convert 2 dates to seconds, find the difference, and sum them in 1 sql >>statement? The login and logout times can cross days. >This expression gives you the user and number of logged-in seconds >for each row: >SELECT UserId, UNIX_TIMESTAMP(LogoutDate) - UNIX_TIMESTAMP(LoginDate) AS > secs FROM tbl_name; >To sum up the seconds for each user, do something like this: >SELECT UserId, SUM(UNIX_TIMESTAMP(LogoutDate) - UNIX_TIMESTAMP(LoginDate)) >AS secs FROM tbl_name GROUP BY UserId; >To get hours, divide SUM() by 3600. >>It appears the that mySQL stores the date in a integer format (basically >>a string like format) making time math very difficult. 20011105120000 >>doesn't really mean anything numerically. >> >>In Interbase and Delphi DateTime is stored as a double. The integral >>part of a TDateTime value is the number of days that have passed since >>12/30/1899. The fractional part of a TDateTime value is fraction of a 24 >>hour day that has elapsed. (In case you have any pull with the mySQL >>crew ;) ) Which makes date/time math very easy. >Converting the dates to second elapsed since 1970-01-01 00:00:00 >allows you to do much the same thing. >-----Original Message----- >From: Paul DuBois > >At 2:45 PM -0800 11/25/01, Mark Rissmann wrote: >>Hey guys, >> >>Can anybody out there give me a helping hand? >> >>I have a table of the following format >> >>UserId LoginDate(datetime) LogoutDate(datetime) >> >>1 2001-11-25 11:00:00 2001-11-25 13:45:00 >>2 2001-11-26 22:25:00 2001-11-27 02:00:00 >> >>I want to write a query that will calculate the hours worked for each >>record and then sum them up? > >Convert each of them to seconds since the epoch (beginning of 1970), >take the difference to get number of seconds worked. Divide by the >appropriate value, e.g., 60 to get minutes worked. > >> >>This is going directly to a report engine so I have little control over >>it (like running multiple queries) except I am able to manipulate the >>final value and format it. >> >>Thanks for any help. >> >>Mark Rissmann > > >--------------------------------------------------------------------- >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
