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
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 |
+---------------------+---------------------+
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))
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
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 |
+---------------------+
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`
DATE_FORMAT(), Luke)