在 MySQL 中向时间添加某些分钟

Adding certain minutes to time in MySQL

提问by Ikke

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.

所以基本上，我想将分钟添加到时间值。我已经尝试过 DATE_ADD 函数，但它希望日期是日期时间，并设置了日期部分。我也尝试了 ADDTIME 函数，但这里的问题是持续时间字段是整分钟，而不是 hh:mm:ss 格式，所以它只是将它添加为秒。

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 |
+-----------------------------------+-----------+----------+

回答by Robban

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

Addtime 绝对是要走的路...只需添加一定的分钟数，您可以执行以下操作：

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

This would add 10 minutes to the first value.

这将使第一个值增加 10 分钟。

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

您可以在 dev.mysql.com 上阅读更多内容：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 mmand nothing else, then you could parse a time value with the SEC_TO_TIME()function. Like this:

除了你的评论。如果您以 的格式获得要添加的值mm，那么您可以使用该SEC_TO_TIME()函数解析时间值。像这样：

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:ssminute format.

这将返回hh:mm:ss分钟格式的时间。

回答by Jeremy Jones

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

从日期和时间添加和减去间隔的简单方法是使用+or-和单词INTERVAL：

SELECT startTime + INTERVAL 10 MINUTE

You can add & 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

您可以添加和减去秒、分、天、周、月等。完整列表在这里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.

也可以使用 INTERVAL 和 + 或 - 运算符来执行日期算术：

date + INTERVAL expr unit 
date - INTERVAL expr unit 

如果另一侧的表达式是日期或日期时间值，则 + 运算符的任一侧都允许使用 INTERVAL expr 单元。对于 - 运算符，INTERVAL expr 单元仅允许在右侧，因为从间隔中减去日期或日期时间值是没有意义的。

回答by Amber

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

也许在传递给TIMEADD? 或者，将该值乘以 60。

回答by cyborg86pl

TL:DR

翻译：博士

multiply minutes by 100, not 60

将分钟乘以 100，而不是 60

EXPLANATION

解释

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

如果您想以MM格式添加分钟，就像在您的示例中一样，并避免将其转换为HH:MM:SS格式，您应该将分钟乘以 100，而不是 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:

这是因为正如 MySQL 文档所说：

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'.

例如，您可能认为 '1112' 和 1112 表示 '11:12:00'（11 点后 12 分钟），但 MySQL 将它们解释为 '00:11:12'（11 分 12 秒） . 类似地，“12”和“12”被解释为“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)

所以你不想计算你有多少秒（例如：600 = 6 分钟，而不是你想的 10 分钟）

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

如果你想增加 15 或 30 分钟，你只需增加 1500 或 3000，就是这样。