CODEWITHSUNDEEP
mysqlsql
hch
hch

Reputation: 101

Convert and round (Seconds to Minutes) with SQL

I have a field on my table which represents seconds, I want to convert to minutes

Select (100/60) as Minute from MyTable
-> 1.66

How can I get 1 minute and 40 seconds 00:01:40 and then round to 00:02:00 and if 00:01:23 round to 00:01:30

Using Mysql.

Upvotes: 4

Views: 13170

Answers (4)

MatBailie
MatBailie

Reputation: 86798

There are two ways of rounding, using integer arithmetic and avoiding floating points, a value to the nearest thirty seconds...

  • ((seconds + 15) DIV 30) * 30
  • (seconds + 15) - (seconds + 15) % 30

The latter is longer, but in terms of cpu time should be faster.


You can then use SEC_TO_TIME(seconds) to get the format hh:mm:ss, and take the right 5 characters if you really need hh:mm.


If you wanted to avoid SEC_TO_TIME(seconds), you can build up the string yourself.

  • minutes = total_seconds DIV 60

  • seconds = total_seconds % 60

  • final string = LPAD(minutes, 2, '0') | ':' | LPAD(seconds, 2, '0')

Upvotes: 6

hch
hch

Reputation: 101

Desired result :

A = 30
B = 60
C = 90
D = 120

select 

(25 + 15)-(25 + 15) % 30 as A,

(32 + 15)-(32 + 15) % 30 as B,

(90 + 15)-(90 + 15) % 30 as C,

(100 + 15)-(100 + 15) % 30 as D

Result :

A = 30

B = 30

C = 90

D = 90

I try with this:

select 

30* ceil(30/30) as A,

30* ceil(32/30) as B,

30* ceil(90/30) as C,

30* ceil(100/30) as D

Result :

A = 30

B = 60

C = 90

D = 120

Thank you for your help !

Upvotes: 0

n1_
n1_

Reputation: 4397

You can simply write your own function http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

But I'd rather do that in a programing language (PHP, Python, C), not on the database side.

Upvotes: -3

dku.rajkumar
dku.rajkumar

Reputation: 18588

i am not sure about how to round it but you can convert seconds into time i.e hh:mm:ss format using SEC_TO_TIME(totaltime)

Upvotes: 3

Related Questions