Rounding time to 6 minutes with mySQL
I have a field DATETIME
in mySQL ( fromStamp
) with a valid date / time stamp. I need to be able to round minutes to the next 6 minute interval. Seconds should be ignored or treated as: 00
So, if my DATETIME 2013-01-31 13:07:17
, I need the result 2013-01-31 13:12:00
EXCEPT between: 00 and: 06 and: 31 and: 36, I need to round DOWN .
:00-06 round DOWN to 00
:07-12 round UP to 12
:13-18 round UP to 18
:19-24 round UP to 24
:24-30 round UP to 30
:31-36 round DOWN to 30
:37-42 round UP to 42
:43-48 round UP to 48
:49-54 round UP to 54
:55-00 round UP to 00
I found a sane way to selectively round using:
SELECT
fromStamp,
CONCAT(DATE_FORMAT(`fromStamp`, '%Y-%m-%d'), ' ',
SEC_TO_TIME((TIME_TO_SEC(`fromStamp`) DIV 360) * 360))
FROM `table`
WHERE (
DATE_FORMAT(`fromStamp`, '%i') BETWEEN 0 AND 6
OR DATE_FORMAT(`fromStamp`, '%i') BETWEEN 31 AND 36
);
Is there a good way to round everything else to the next 6 minute interval? (I feel like I'm missing something simple)
UPDATE
So, I'm going with @mellamokb's solution, but his comment on seconds is right, it really comes into play.
DATETIME 2013-01-22 12:24:13 rounds until 12:30, which is not so necessary, so I'll first do:
UPDATE table SET fromStamp = CONCAT(DATE_FORMAT(fromStamp, '%Y-%m-%d %h:%i'), ':00');
to get rid of the seconds and then its request, which gets exactly what I'm looking for.
source to share
Rounding is very similar logic, except for adding 1
to move to the next nearest interval 6-minute
. I also subtracted 1
from TIME_TO_SEC
as a correction factor, otherwise boundary values such as 06:00
are shifted to the next 6-minute interval, i.e. 12:00
...
SEC_TO_TIME( ( (TIME_TO_SEC(`fromStamp`) - 1) DIV 360 + 1) * 360))
^^^ add one
To combine the two, use an operator CASE
to control which form of rounding is used for each record:
SELECT
fromStamp,
CASE WHEN
DATE_FORMAT(`fromStamp`, '%i') BETWEEN 0 AND 6
OR DATE_FORMAT(`fromStamp`, '%i') BETWEEN 31 AND 36
THEN
CONCAT(DATE_FORMAT(`fromStamp`, '%Y-%m-%d'), ' ',
SEC_TO_TIME((TIME_TO_SEC(`fromStamp`) DIV 360) * 360))
ELSE
CONCAT(DATE_FORMAT(`fromStamp`, '%Y-%m-%d'), ' ',
SEC_TO_TIME(((TIME_TO_SEC(`fromStamp`) - 1) DIV 360 + 1) * 360))
END
FROM `table`
( DEMO )
source to share
I think the simplest way to deal with this odd type of rounding based on the criteria provided would be:
SELECT `fromStamp`
, DATE_FORMAT(`fromStamp`,'%Y-%m-%d %H') + INTERVAL
CASE
WHEN TIME_FORMAT(`fromStamp`,'%i') <= 6 THEN 0 -- round DOWN
WHEN TIME_FORMAT(`fromStamp`,'%i') <= 12 THEN 12 -- round up
WHEN TIME_FORMAT(`fromStamp`,'%i') <= 18 THEN 18 -- round up
WHEN TIME_FORMAT(`fromStamp`,'%i') <= 24 THEN 24 -- round up
-- WHEN TIME_FORMAT(`fromStamp`,'%i') <= 30 THEN 30 -- round up
WHEN TIME_FORMAT(`fromStamp`,'%i') <= 36 THEN 30 -- round DOWN
WHEN TIME_FORMAT(`fromStamp`,'%i') <= 42 THEN 42 -- round up
WHEN TIME_FORMAT(`fromStamp`,'%i') <= 48 THEN 48 -- round up
WHEN TIME_FORMAT(`fromStamp`,'%i') <= 54 THEN 54 -- round up
WHEN TIME_FORMAT(`fromStamp`,'%i') >= 55 THEN 60 -- round up
END MINUTE AS rnd_fromStamp
This seems like the easiest way to express what's going on for future reviewers looking at this statement and wondering, "What is plastic, does this statement do with a Stamp?"
There may be slightly faster (more efficient) algorithms out there, but I don't think any of them will be more elegant or easy to read and understand like this.
source to share