mysql - Summing timestamp returning incorrect value and format -


i looking pull scheduled hours in given time period. our start , end schedule times datetimes converted them timestamps. when dont sum them looks correct, when sum them on time period, output isnt in timestamp format , numbers incorrect.

the query using:

select sal.public_name, sum(timediff(timestamp(end_time), timestamp(start_time))) bi.support_agents_list sal join bi.support_sp_shifts_scheduled ss on ss.agent_sp_id = sal.sp_id join bi.support_sp_shifts s  on s.pk_id = ss.pk_id date(start_time) between '2014-01-29' , '2014-01-31' group sal.public_name 

a few examples of results getting:

agent 1: 53000 - when should 5.5 hours or 5:30

agent 2: 196000 - when should 20 hours

any thoughts on this? prefer output in hour count 5 hours , 30 min formatted 5.5 rather 5:30.

try instead of sum

   date_format(timediff(timestamp(end_time), timestamp(start_time)),     '%k hours, %i minutes, %s seconds') thesum 

like that

 select sal.public_name,     date_format(timediff(timestamp(end_time), timestamp(start_time)), '%k hours, %i minutes, %s seconds') thesum  bi.support_agents_list sal 

Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -