Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: convert time to decimal format

    hi,
    can anyone knows how to convert time to decimal format?
    e.g. 08:30 = 8.5

    i can convert time to decimal, is this the correct conversion?
    Code:
    sum(time_to_sec( coding_log.adjustment_time )/3600)
    but it has .01 difference from the source table.

    can anyone please.
    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try /3600.0 instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2002
    Location
    Germany
    Posts
    17
    You schould try to work on integers as long as possible.

    Due to the nature of floating point representation there is an increasing rounding error the more you work on floats.

    Try

    sum(time_to_sec( coding_log.adjustment_time ))/3600

    and if this doesn't help and you can change your application do the division outside the query.

    Andreas

  4. #4
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    great!thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  5. #5
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Quote Originally Posted by r937
    try /3600.0 instead
    hi,
    what does the /3600.0 do?
    because if i sum a time format of time i will get an exact time with no decimal (of course).
    but when i sum a decimal format of time there are so many decimal.
    i cant round them off because it makes a big difference. even a .01

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see post #3 -- keep integer format as long as possible

    i.e. don't do it like i said

    SUM the integers, and then divide the SUM by 3600.0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    hi,
    e.g.
    Code:
    sum of converted time to integer
    6.3834
    2.6167
    = 9.0001
    supposed to be it should be 9.00

    if i convert time to integer it always has .0001 difference for 4 decimal places and .01 for 2 decimal places, there is always a .01, but in my time format it is exact 9:00

    please kindly check my script
    Code:
    select
    `date`,
    last_name,
    tl,
    `client`,
    service,
    sum(coalesce(adjustment,0)) as 'Total Adjustment',
    sum(coalesce(`total break`,0)) as 'Total Break',
    sum(`over time`) as 'Over time',
    `total man hours` - `over time` as 'reg hrs',
    sum(`total man hours`) as 'Total Man Hours',
    `time start`,
    `time finished`,
    `data process`
    
    from(
    SELECT
    key_entry_log.`date`,
    key_entry_log.last_name,
    `user`.tl,
    batch_log.`client`,
    batch_log.service,
    key_entry_log.adjustment_time as 'Adjustment',
    key_entry_log.break_hours as 'Total Break',
    
        sum(
             time_to_sec(
                          if(
                             key_entry_log.time_start and key_entry_log.time_end <= '16:00', 0,
                              if(
                                  key_entry_log.time_start <= '16:00' and key_entry_log.time_end >= '16:00',
                                  subtime(
                                          key_entry_log.time_end,'16:00'
                                          ),
                                  if(
                                      key_entry_log.time_start and key_entry_log.time_end > '16:00',
                                      subtime(
                                              key_entry_log.time_end, key_entry_log.time_start
                                              ),
                                      0)
                                  )
                               )
                            )
              )/3600
                           as 'Over Time',
        sum(
             time_to_sec(
                      SUBTIME(
                              key_entry_log.time_end,key_entry_log.time_start
                               )
                         )
              )/3600
                         as 'Total Man Hours',
    
    
    
    
    min(
        key_entry_log.time_start
        ) as 'Time Start',
    max(
        key_entry_log.time_end
        ) as 'Time Finished',
    "key_entry_log" as 'Data Process'
    
    FROM
    key_entry_log
    left join `user` on (`user`.last_name = key_entry_log.last_name)
    inner join batch_log on (batch_log.job_number = key_entry_log.job_no)
    where batch_log.total_batches is not null && key_entry_log.last_name = 'corporal' && month(key_entry_log.`date`) = 7
    group by
    key_entry_log.`date`,
    key_entry_log.last_name,
    batch_log.`client`) as q
    
    group by
    q.`date`,
    q.last_name,
    q.`client`
    
    order by
    q.`date` desc,
    q.last_name
    Last edited by homer.favenir; 08-19-08 at 22:15.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

Posting Permissions

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