Converting seconds to hours and mins
-
Hi All Perhaps someone can advise on a better solution to my problem ? I have a table full of call (telephone) records, each record has the duration of the call made the duration is in seconds i.e Call table Call id, duration, telephonenumber 1 89 0122222222222 2 135 0192929292929 3 31 0121122211111 4 5....etc etc what i need to do i go through each record and convert the duration to a time format so for call id 1, the result would be 01:29, for 2, result would be 02:15 for 3, result would be 00:31 for 4, result would be 00:05 at the moment, below is what i am doing within a cursor for each row, and although it works fine it is a really slow way of doing things, and when i have to go through a few million records you can imagine this takes quite a while Can anyone suggest a quicker alternative ? Hope that all makes sense ? Simon set @CounterVariable = 1 set @hours = 0 set @mins = 0 set @time_counter = 0 WHILE (@CounterVariable <= @dur) BEGIN --- count the hours set @CounterVariable = @CounterVariable + 1 set @time_counter = @time_counter +1 IF @time_counter = 60 BEGIN set @hours = @hours + 1 set @time_counter = 0 END END --- mins is remainder (seconds) set @mins = @time_counter IF @hours < 10 and @time_counter < 10 -- build the time string BEGIN set @dur_time = '0'+ CAST(@hours as varchar(2)) + ':' + '0' + CAST(@mins as varchar(2)) END ELSE IF @hours >= 10 and @time_counter < 10 BEGIN set @dur_time = CAST(@hours as varchar(2)) + ':' + '0' + CAST(@mins as varchar(2)) END ELSE IF @hours < 10 and @time_counter>= 10 BEGIN set @dur_time = '0'+ CAST(@hours as varchar(2)) + ':' + CAST(@mins as varchar(2)) END ELSE IF @hours >=10 and @time_counter>= 10 BEGIN set @dur_time = CAST(@hours as varchar(2)) + ':' + CAST(@mins as varchar(2)) END
-
Hi All Perhaps someone can advise on a better solution to my problem ? I have a table full of call (telephone) records, each record has the duration of the call made the duration is in seconds i.e Call table Call id, duration, telephonenumber 1 89 0122222222222 2 135 0192929292929 3 31 0121122211111 4 5....etc etc what i need to do i go through each record and convert the duration to a time format so for call id 1, the result would be 01:29, for 2, result would be 02:15 for 3, result would be 00:31 for 4, result would be 00:05 at the moment, below is what i am doing within a cursor for each row, and although it works fine it is a really slow way of doing things, and when i have to go through a few million records you can imagine this takes quite a while Can anyone suggest a quicker alternative ? Hope that all makes sense ? Simon set @CounterVariable = 1 set @hours = 0 set @mins = 0 set @time_counter = 0 WHILE (@CounterVariable <= @dur) BEGIN --- count the hours set @CounterVariable = @CounterVariable + 1 set @time_counter = @time_counter +1 IF @time_counter = 60 BEGIN set @hours = @hours + 1 set @time_counter = 0 END END --- mins is remainder (seconds) set @mins = @time_counter IF @hours < 10 and @time_counter < 10 -- build the time string BEGIN set @dur_time = '0'+ CAST(@hours as varchar(2)) + ':' + '0' + CAST(@mins as varchar(2)) END ELSE IF @hours >= 10 and @time_counter < 10 BEGIN set @dur_time = CAST(@hours as varchar(2)) + ':' + '0' + CAST(@mins as varchar(2)) END ELSE IF @hours < 10 and @time_counter>= 10 BEGIN set @dur_time = '0'+ CAST(@hours as varchar(2)) + ':' + CAST(@mins as varchar(2)) END ELSE IF @hours >=10 and @time_counter>= 10 BEGIN set @dur_time = CAST(@hours as varchar(2)) + ':' + CAST(@mins as varchar(2)) END
Hi, Here some sql to calculate Hours, Minutes, seconds using only built in sql functions and in one sql select statement: select floor(duration / 3600) as h, floor( (duration - floor (duration /3600) )/60) as m, duration - floor(duration / 3600) * 3600 - floor( (duration - floor (duration /3600) )/60) * 60 as sec then u can concatenate all these fields HTH. Hayder marzouk
-
Hi All Perhaps someone can advise on a better solution to my problem ? I have a table full of call (telephone) records, each record has the duration of the call made the duration is in seconds i.e Call table Call id, duration, telephonenumber 1 89 0122222222222 2 135 0192929292929 3 31 0121122211111 4 5....etc etc what i need to do i go through each record and convert the duration to a time format so for call id 1, the result would be 01:29, for 2, result would be 02:15 for 3, result would be 00:31 for 4, result would be 00:05 at the moment, below is what i am doing within a cursor for each row, and although it works fine it is a really slow way of doing things, and when i have to go through a few million records you can imagine this takes quite a while Can anyone suggest a quicker alternative ? Hope that all makes sense ? Simon set @CounterVariable = 1 set @hours = 0 set @mins = 0 set @time_counter = 0 WHILE (@CounterVariable <= @dur) BEGIN --- count the hours set @CounterVariable = @CounterVariable + 1 set @time_counter = @time_counter +1 IF @time_counter = 60 BEGIN set @hours = @hours + 1 set @time_counter = 0 END END --- mins is remainder (seconds) set @mins = @time_counter IF @hours < 10 and @time_counter < 10 -- build the time string BEGIN set @dur_time = '0'+ CAST(@hours as varchar(2)) + ':' + '0' + CAST(@mins as varchar(2)) END ELSE IF @hours >= 10 and @time_counter < 10 BEGIN set @dur_time = CAST(@hours as varchar(2)) + ':' + '0' + CAST(@mins as varchar(2)) END ELSE IF @hours < 10 and @time_counter>= 10 BEGIN set @dur_time = '0'+ CAST(@hours as varchar(2)) + ':' + CAST(@mins as varchar(2)) END ELSE IF @hours >=10 and @time_counter>= 10 BEGIN set @dur_time = CAST(@hours as varchar(2)) + ':' + CAST(@mins as varchar(2)) END
I may be too simplistic here, but I tend to agree with Hayder marzouk (presuming I'm understand what you're trying to accomplish here). I used a slightly different formula than he did: his didn't work quite right when I tried it in Access .. probably my fault. In MSAccess, this works: CREATE PROCEDURE pcSeconds2FormattedTime( seconds INTEGER ) AS SELECT (@seconds\3600) & ':' & ((@seconds - (@seconds \3600)*3600 )\60) & ':' & @seconds - (3600*(@seconds\3600) + 60*((@seconds - (@seconds \3600)*3600 )\60)) FROM dual; (presuming you have something called "dual"). If you don't have the '\' operator (integer division), then use the FLOOR function as suggested. Also, in a real db system, use the real concatenation operator '||', not the '&' ampersand. Depending on whether the SQL compiler will do "common subexpression elimination", this may or may not run any faster than your method.
-
I may be too simplistic here, but I tend to agree with Hayder marzouk (presuming I'm understand what you're trying to accomplish here). I used a slightly different formula than he did: his didn't work quite right when I tried it in Access .. probably my fault. In MSAccess, this works: CREATE PROCEDURE pcSeconds2FormattedTime( seconds INTEGER ) AS SELECT (@seconds\3600) & ':' & ((@seconds - (@seconds \3600)*3600 )\60) & ':' & @seconds - (3600*(@seconds\3600) + 60*((@seconds - (@seconds \3600)*3600 )\60)) FROM dual; (presuming you have something called "dual"). If you don't have the '\' operator (integer division), then use the FLOOR function as suggested. Also, in a real db system, use the real concatenation operator '||', not the '&' ampersand. Depending on whether the SQL compiler will do "common subexpression elimination", this may or may not run any faster than your method.