英文:
MySQL Time between two columns
问题
我已经设置了一个表格来跟踪能源使用情况，我的表格包括3个列：
| 用量 | 开始时间 | 结束时间 |
|---|---|---|
| 0.079 | 2023-06-10 00:00:00 | 2023-06-10 00:30:00 |
| 0.025 | 2023-06-10 00:30:00 | 2023-06-10 01:00:00 |
| 1.250 | 2023-06-10 01:00:00 | 2023-06-10 00:30:00 |
| 3.524 | 2023-06-10 01:30:00 | 2023-06-10 02:00:00 |
| 3.269 | 2023-06-10 02:00:00 | 2023-06-10 02:30:00 |
| 3.211 | 2023-06-10 02:30:00 | 2023-06-10 03:00:00 |
开始和结束列都是日期时间类型，包括30分钟的时间段。
我已经有了语句的第一部分，其中我选择了消耗值的总和：
SELECT ROUND(SUM(consumption), 3) AS c
FROM electricity
WHERE
但我不明白如何进行第二部分。
我需要选择在开始和结束日期时间之间的所有消耗值。
例如，其中interval_start为2023-06-10 00:00:00，interval_end为2023-06-10 02:30:00，以及这两者之间的所有内容。
英文:
I have a table setup to track energy usage and my table consists of 3 columns:
| Consumption | interval_start | interval_end |
|---|---|---|
| 0.079 | 2023-06-10 00:00:00 | 2023-06-10 00:30:00 |
| 0.025 | 2023-06-10 00:30:00 | 2023-06-10 01:00:00 |
| 1.250 | 2023-06-10 01:00:00 | 2023-06-10 00:30:00 |
| 3.524 | 2023-06-10 01:30:00 | 2023-06-10 02:00:00 |
| 3.269 | 2023-06-10 02:00:00 | 2023-06-10 02:30:00 |
| 3.211 | 2023-06-10 02:30:00 | 2023-06-10 03:00:00 |
The start and end columns are both datetime and consist of 30 min slots.
I have the first part of the statement already where I select add up the value of consumption:
SELECT ROUND(SUM(consumption), 3) AS c
FROM electricity
WHERE
but don't understand how I can do the second part.
I need to select all consumption values between a start and end datetime.
eg. where interval_start is 2023-06-10 00:00:00 AND interval_end is 2023-06-10 02:30:00 and everything in between this.
答案1
得分: 1
如果您的时间间隔完全包含在您的时间窗口内，那么这应该是您所需要的：
SELECT ROUND(SUM(consumption), 3) AS c
FROM electricity
WHERE interval_start BETWEEN "2023-06-10 00:00:00" AND "2023-06-10 02:30:00"
AND interval_end BETWEEN "2023-06-10 00:00:00" AND "2023-06-10 02:30:00";
然而，如果您的时间间隔跨越了边界（即开始时间早于 "2023-06-10 00:00:00" 但结束时间晚于该时间），那么情况会变得更加复杂，您需要澄清应该使用的逻辑。
英文:
If your intervals are entirely contained within your time window, then this should give what you need:
SELECT ROUND(SUM(consumption), 3) AS c
FROM electricity
WHERE interval_start BETWEEN "2023-06-10 00:00:00" AND "2023-06-10 02:30:00"
AND interval_end BETWEEN "2023-06-10 00:00:00" AND "2023-06-10 02:30:00"
However, if you have intervals that straddle the boundary (ie started before 2023-06-10 00:00:00 but finished after) then that becomes more complex and you would need to clarify the logic that should be used.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论