CODEWITHSUNDEEP
mysqlsqltime
Chris Illusion
Chris Illusion

Reputation: 287

MySQL creating a rounded timestamp

I just got stuck on one SQL query, where I need to create a timestamp with certain parts re-set.

I'll be needing to add interval of 30 minutes on one query and 6 hours on second query. However, the rest of the time should be 0. Example, I have the following datetime: 2013-07-04 00:04:57 And I want to add 30 minutes, what I need it to be is: 2013-07-04 00:34:00. Same goes to adding the hours, if I had the same time and added 6 hours, I'd need to get: 2013-07-04 06:00:00 instead of 2013-07-04 06:04:57.

I was experimenting with TIMESTAMPADD(HOUR, 6, UTC_TIMESTAMP()), but I couldn't find a way to "round" the minutes/seconds. Additionally, I have been experimenting with: MAKETIME(HOUR(UTC_TIMESTAMP()), MINUTE(ADDTIME(UTC_TIMESTAMP(), '0:30:0')),0) which works pretty well, except that the date part is missing.

Can you please help me solve this problem? Thank you very much!

Upvotes: 0

Views: 136

Answers (2)

Anthony Atkinson
Anthony Atkinson

Reputation: 3248

You can replace now() with any datetime you want to alter:

DATE_ADD(
  DATE_FORMAT(now(), "%Y-%m-%d %H:%i:00"),
  INTERVAL(30 + (IF(SECOND(now()) > 29, 1, 0))) MINUTE
 )

Upvotes: 1

Zec
Zec

Reputation: 843

My experience is with T-SQL primarily, but I'd solve this with something like:

SELECT DATEADD(millisecond, -(DATEPART(millisecond, @myDate)), DATEADD(second, -(DATEPART(second, @myDate)), DATEADD(minute, -(DATEPART(minute, @myDate) % 30), DATEADD(minute, 30, @myDate))))

Essentially adding a half-hour first, deducting the remaining minutes modulo 30, deducting the seconds and then the milliseconds.

But I don't think the DATEPART function is available to you. A brief look at the spec and I see equivalent functions like MINUTE(), SECOND(), MICROSECOND() which should work similarly.

Upvotes: 0

Related Questions