Adding certain minutes to time in MySQL

Question

This question is related to this question

I have a field which is a time-field (it does not need to be a datetime field, cause the date part makes no sense here). The value i want to add is in another field, in minutes.

So basicly, I want to add minutes to a time value. I have tried the DATE_ADD function, but it expects the date to be a datetime, with the datepart set. I also tried the ADDTIME function, but the problem here is that the duration field is in whole minutes, and not in the format hh:mm:ss, so it just adds it as seconds.

Does anyone know a way to accomplish this?

[edit]

This is the current query:

SELECT ADDTIME(startTime, duration * 60), startTime, duration FROM tblAppointment
JOIN tblThreatment ON tblThreatment.threatmentid = tblAppointment.threatment_id;

and this is the result:

+-----------------------------------+-----------+----------+
| ADDTIME(startTime, duration * 60) | startTime | duration |
+-----------------------------------+-----------+----------+
| 09:18:00                          | 09:00:00  |       30 |
| 10:09:00                          | 10:00:00  |       15 |
| 09:09:00                          | 09:00:00  |       15 |
| 10:57:00                          | 10:30:00  |       45 |
+-----------------------------------+-----------+----------+

Added an addition in response to your comment.

Robban
– Robban

2009-09-15 14:44:29 +00:00
Commented Sep 15, 2009 at 14:44 — Robban
– Robban, Commented Sep 15, 2009 at 14:44

Kirby · Accepted Answer · 2024-08-02 17:10:54Z

29

The Addtime function is definitely the way to go. To just add a certain amount of minutes, you could do something like:

 AddTime('00:00:00', '00:10:00')

This would add 10 minutes to the first value.

You can read more on dev.mysql.com here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_addtime

EDIT:

In addition to your comment, if you get the value to add in the format of mm and nothing else, then you could parse a time value with the SEC_TO_TIME() function, like this:

SELECT ADDTIME(startTime, SEC_TO_TIME(duration*60)), startTime, duration
FROM tblAppointment
JOIN tblThreatment ON tblThreatment.threatmentid = tblAppointment.threatment_id;

This would return a time in the hh:mm:ss format.

edited Aug 2, 2024 at 17:10

Kirby

16k11 gold badges97 silver badges110 bronze badges

answered Sep 15, 2009 at 9:30

Robban

6,8372 gold badges42 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeremy Jones · Accepted Answer · 2024-11-08 23:47:34Z

A simple way to add and subtract intervals from dates and times is just to use + or - and the word INTERVAL:

SELECT startDateTime + INTERVAL 10 MINUTE

You can add and subtract seconds, minutes, days, weeks, months, etc. The full list is here https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-add

Date arithmetic also can be performed using INTERVAL together with the

or - operator:

date + INTERVAL expr unit date - INTERVAL expr unit

INTERVAL expr unit is permitted on either side of the + operator if the expression on the other side is a date or datetime value. For the - operator, INTERVAL expr unit is permitted only on the right side, because it makes no sense to subtract a date or datetime value from an interval.

Some examples:

mysql> select now() + interval 1 second;
+---------------------------+
| now() + interval 1 second |
+---------------------------+
| 2024-11-08 23:46:30       |
+---------------------------+
1 row in set (0.03 sec)

mysql> select '2024-11-08 15:00:00' + interval 20 minute;
+--------------------------------------------+
| '2024-11-08 15:00:00' + interval 20 minute |
+--------------------------------------------+
| 2024-11-08 15:20:00                        |
+--------------------------------------------+
1 row in set (0.03 sec)

mysql> select '2024-11-08 15:00:00' + interval 20 day;
+-----------------------------------------+
| '2024-11-08 15:00:00' + interval 20 day |
+-----------------------------------------+
| 2024-11-28 15:00:00                     |
+-----------------------------------------+
1 row in set (0.03 sec)

mysql> select '2024-11-08 15:00:00' + interval 20 week;
+------------------------------------------+
| '2024-11-08 15:00:00' + interval 20 week |
+------------------------------------------+
| 2025-03-28 15:00:00                      |
+------------------------------------------+
1 row in set (0.03 sec)

mysql> select '2024-11-08 15:00:00' + interval 120 year as "hello future people";
+---------------------+
| hello future people |
+---------------------+
| 2144-11-08 15:00:00 |
+---------------------+
1 row in set (0.03 sec)

mysql>

This is about the most intuitive option IMO. Be sure to use an actual time value, rather than a time string. That is TIME('11:30:00') + INTERVAL 15 MINUTE as opposed to '11:30:00' + INTERVAL 15 MINUTE

cyborg86pl · Accepted Answer · 2017-03-11 17:00:06Z

TL:DR

multiply minutes by 100, not 60

EXPLANATION

If you want to add minutes in MM format, like it is in your example, and avoid converting it to HH:MM:SS format, you should multiply minutes by 100, not 60:

SELECT ADDTIME(startTime, duration * 100), startTime, duration FROM tblAppointment
JOIN tblThreatment ON tblThreatment.threatmentid = tblAppointment.threatment_id;

It's because as MySQL documentation says:

For example, you might think of '1112' and 1112 as meaning '11:12:00' (12 minutes after 11 o'clock), but MySQL interprets them as '00:11:12' (11 minutes, 12 seconds). Similarly, '12' and 12 are interpreted as '00:00:12'.

So you don't want to count how many seconds you have (ex: 600 = 6 minutes, not 10 minutes as you'd think)

If you want to add 15 or 30 minutes, you simply add 1500 or 3000, and that's it.

Amber · Accepted Answer · 2009-09-15 09:31:18Z

0

Perhaps concatenate ':00' onto the end of the value from the minutes field before passing to TIMEADD? Or alternatively, multiply the value by 60.

answered Sep 15, 2009 at 9:31

Amber

532k89 gold badges643 silver badges558 bronze badges

3 Comments

Ikke Over a year ago

Both don't work. With the concat version, I get times like 39:00:00, 25:00:00.

Amber Over a year ago

How do you manage that, if the minutes coming in are 00-59? or do you have things like 39:00 in your minutes field?

Ikke Over a year ago

I really don't know. This is just what i'm getting. But this method wouldn't be very flexible in case you have durations of longer then 59 minutes.

Collectives™ on Stack Overflow

Adding certain minutes to time in MySQL

4 Answers 4

Comments

1 Comment

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related