Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Problem in SUM total hours without compute the seconds

    Hi..

    I notice that I got problem in my code:

    Code:
    SELECT sec_to_time(SUM(time_to_sec(Rendered))) FROM reg_att WHERE EMP_NO = '09300139';

    in this query the output is : 103:52:27 // total hours.

    but it's wrong it should only be 103:51 //if did not count the seconds.

    Now my question is what would be the syntax to correct the result.


    this is the sample data:

    08:00:00
    08:00:00
    08:00:00
    08:00:00
    08:00:00
    08:00:00
    08:00:00
    08:00:00
    08:00:00
    08:00:00
    08:00:00
    07:56:42
    07:55:45
    Thank you so much.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    SUM( (time_to_sec(Rendered) DIV 60) * 60 )

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    you mean this:

    Code:
    SELECt SUM( (time_to_sec(Rendered) / 60) * 60 ) from reg_att WHERE EMP_NO = '09300139';
    the output is:

    373947.0000

    Thank you

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    No!

    I mean to use DIV.

    in "MySQL 5.6 Reference Manual"
    • DIV

    Integer division. Similar to FLOOR(), but is safe with BIGINT values.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  6. #6
    Join Date
    Dec 2010
    Posts
    136
    I resolve my problem by changing my code for getting the rendered:

    Code:
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('10:00:00') AND time_to_sec('10:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('14:35:00')
    AND DATE_FORMAT(LOGIN, '%W') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
    then sec_to_time(CEIL((time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '04:00:00')) +
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '14:35:00')))) / 60) * 60)
    Thank you
    Last edited by newphpcoder; 04-24-12 at 21:03.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •