14

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

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Ana
  • 584
  • 2
  • 6
  • 16
  • 2
    possible duplicate of [MYSQL Date Time Round To Nearest Hour](http://stackoverflow.com/questions/9680144/mysql-date-time-round-to-nearest-hour) – Tomasz Kowalczyk Oct 10 '13 at 08:38
  • 1
    Also see this: http://stackoverflow.com/questions/2480637/round-minute-down-to-nearest-quarter-hour – Tomasz Kowalczyk Oct 10 '13 at 08:38
  • @TomaszKowalczyk: The first question is about rounding to whole hours which has rather simple solutions. The latter one is PHP. – Kaivosukeltaja Oct 10 '13 at 08:46
  • You haven't done research in the subject you're asking. Answer marked as duplicate is easily adaptable, answer in PHP as well. Even the question mentioned inside your question is easily solved (hint: use `DATE_FORMAT()`, Luke) – Tomasz Kowalczyk Oct 10 '13 at 08:52

5 Answers5

22
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 |
+---------------------+---------------------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57
10

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))
halfer
  • 19,824
  • 17
  • 99
  • 186
AL̲̳I
  • 2,441
  • 4
  • 29
  • 50
5

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

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
2

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 |
+---------------------+
Kaivosukeltaja
  • 15,541
  • 4
  • 40
  • 70
1

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`
zavg
  • 10,351
  • 4
  • 44
  • 67