Get the next xx:30 time (the next half-past hour) after a specified time

Question

I'm trying to find a MySQL query which will obtain the time that is the next half-past-the-hour from a specified datetime. I explicitly mean the next half-past-the-hour, not the next half-hourly point.

So for instance:

• If the datetime was "2009-10-27 08:15:24", the answer would be "2009-10-27 08:30:00"
• If the datetime was "2009-10-27 08:49:02", the answer would be "2009-10-27 09:30:00".

I came across this page which refers to SQL Server, and towards the end of that thread there is a similar sort of problem. But it's not quite the same, and it relies on a function that MySQL doesn't have.

Here is a fuller list of examples and expected return values:

2009-10-27 08:15:24  should return  2009-10-27 08:30:00
2009-10-27 08:49:02  should return  2009-10-27 09:30:00
2009-10-27 23:49:10  should return  2009-10-28 00:30:00
2009-10-27 10:30:00(.000001)  should return  2009-10-27 11:30:00

(Note how, in the fourth example, because the exact half-past (10:30:00.0000000) has already gone, the next half-past-the-hour point is found.)

I tried using this kind of thing:

SELECT IF( (MINUTE(NOW()) < 30), HOUR(NOW()), (HOUR(NOW()) + 1) )

(after which addition of a CONCATed string would take place), but it would fail because of the changeover to another day, and it feels inherently 'hacky'.

Can anyone suggest a suitable sort of algorithm? I wouldn't expect a full answer (though that would be nice!), but suggestions as to the kind of algorithm would be helpful. I've been drawing over bits of paper for two hours now! I have a hunch that using modulo might be useful but I'm not sufficiently familiar with using it.

The answer will be fed to a PHP class later, but I'd rather implement this at SQL level if possible, as the rest of query also performs other date comparison functions efficiently.

Answer 1

This is a little messy, but works:

select from_unixtime( floor((unix_timestamp(MY_DATE)+(30*60))/(60*60))*(60*60) + (30*60) )

It pushes the time forward 30 minutes, then truncates to the top of the hour, then adds 30 minutes to it. Because it's working unix timestamps (seconds since 1970), you don't have to worry about the boundaries of days, months, years, etc.

Answer 2

I can't help but notice that this would be much easier at the PHP level :-) That said, here's what you can do:

1. Add 30 minutes to your datetime using DATE_ADD(); this will move to the next hour if it's already past half-hour
2. Create a new datetime value by extracting date / hour and hard coding minutes / seconds. CONVERT(), ADDTIME() and MAKETIME() all help.

The end result is:

select ADDTIME(
 CONVERT(DATE(DATE_ADD(yourDateTime, INTERVAL 30 MINUTE)), DATETIME), # date part
 MAKETIME(HOUR(DATE_ADD(yourDateTime, INTERVAL 30 MINUTE)), 30, 0) # hour + :30:00
) from ...

Answer 3

Use the MAKETIME(hour,minute,second) function to construct the desired value.