Mysql DATE_ADD 1 hour and I want minutes be 15, or 30-CodePudding

What I want to do is Add 1 hour to current date make the minutes be in 15 minutes or 30 minutes. Example:

'2022-05-19 22:13:28'

I want it to be :

'2022-05-19 23:15:00'

Another example


'2022-05-19 22:24:00'

I want it to be:

'2022-05-19 23:30:00'

CodePudding user response：

Assuming if it already on an even 15 minutes it just gets an hour added, you have to do some math on the time, then re-add it to the date:

select date_add(date(d), interval ceil(time_to_sec(d)/900)*900 3600 second)
from (select '2022-05-19 22:13:28' d union all select '2022-05-19 22:24:00' union all select '2022-05-19 22:15:00' union all select '2022-05-19 21:59:00' union all select '2022-05-19 21:44:00') foo

Dividing by 900 seconds, ceil'ing, and multiplying by 900 rounds it up to the nearest even 15 minutes.

CodePudding user response：

SELECT FROM_UNIXTIME(3600   900 * CEIL(UNIX_TIMESTAMP(datetime_column) / 900))
FROM table

UNIX_TIMESTAMP converts datetime to the seconds amount.

/ 900 calculates the amount of 15min. blocks in it.

CEIL convert incomplete last block to complete one.

* 900 converts blocks amount back to seconds.

3600 adds an hour.

FROM_UNIXTIME converts seconds amount to datetime.