CODEWITHSUNDEEP
mysqldatetimetimestamp
Ana
Ana

Reputation: 614

MYSQL Round the datetime to 15 minute

I want to update the datetime round to 15 minutes in a MYSQL database table.

For Example:

If the dateTime is 2013-10-08 10:36:00, I want to convert it into 2013-10-08 10:30:00 Similarly,2013-10-08 10:22:00 to 2013-10-08 10:15:00

I have seen this answer but it converts datetime into seconds and return time only, I want date as well.

Thanks

Upvotes: 16

Views: 18380

Answers (5)

AL̲̳I
AL̲̳I

Reputation: 2461

The answer you have seen is quite useful, try this

SELECT SUBSTRING_INDEX(datetime_field, ' ', -1) AS old_time,SEC_TO_TIME((TIME_TO_SEC(datetime_field) DIV 900) * 900) AS rounded_time, datetime_field FROM yourtable

You can get time from the datetime_field as substring and replace it with the rounded time.

If you want to update the datetime you can reply it and update it with update function:

UPDATE yourtable SET `datetime_field` =  REPLACE(datetime_filed,SUBSTRING_INDEX(datetime_field, ' ', -1),SEC_TO_TIME((TIME_TO_SEC(datetime_field) DIV 900) * 900))

Upvotes: 11

Strawberry
Strawberry

Reputation: 33935

SELECT NOW() x,FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(NOW())/900)*900) y;
+---------------------+---------------------+
| x                   | y                   |
+---------------------+---------------------+
| 2013-10-10 09:50:20 | 2013-10-10 09:45:00 |
+---------------------+---------------------+

Upvotes: 26

Paul Maxwell
Paul Maxwell

Reputation: 35563

using unix_timestamp allows quite simpe arithmetic, change 15 in the code below to some other number if needed (such as 30)

select from_unixtime(round(unix_timestamp('2013-10-08 10:36:00')/(60*15))*(60*15)); 

= October, 08 2013 10:30:00+0000

select from_unixtime(round(unix_timestamp('2013-10-08 10:22:00')/(60*15))*(60*15)); 

= October, 08 2013 10:15:00+0000

see: http://forums.mysql.com/read.php?20,131939,201089#msg-201089

Upvotes: 5

zavg
zavg

Reputation: 11061

You can modify answer you found (if you satisfied with it) just by concatenating date to the time:

SELECT CONCAT(DATE(time_field), 
              ' ',
              SEC_TO_TIME((TIME_TO_SEC(time_field) DIV 900) * 900))
FROM `your_table`

Upvotes: 1

Kaivosukeltaja
Kaivosukeltaja

Reputation: 15735

Use the same method as in the question you linked, but use UNIX_TIMESTAMP functions instead. This will round either down or up to the nearest 15 minutes, remove the + 450 part if you want to round only down.

mysql> select FROM_UNIXTIME(((UNIX_TIMESTAMP('2013-08-07 12:05') + 450) DIV 900) * 900) AS roundtime;
+---------------------+
| roundtime           |
+---------------------+
| 2013-08-07 12:00:00 |
+---------------------+

Upvotes: 2

Related Questions