CODEWITHSUNDEEP
mysqltimenumbersrounding
carlosgmercado
carlosgmercado

Reputation: 284

How i can round to the nearest 60 sec interval in MySQL

I got a table that have records of calls. The detail have the the phone_number and call_time in milliseconds. Which could be the SQL statement to have a column with the rounded call_time to the nearest top 60th second? Example:

phone_number | call_time | rounded_time
=====================================
787-468-8965 |  45786    |   60
787-564-8945 | 128907    |   180

Thanks

Upvotes: 4

Views: 1062

Answers (1)

Mark Byers
Mark Byers

Reputation: 838216

  • Convert to minutes.
  • Using the CEILING function to round up to the next minute.
  • Convert back to seconds.

Try this:

CREATE VIEW yourview AS
SELECT
    CEILING(call_time / 60000) * 60,
    -- etc...

Upvotes: 4

Related Questions