如何在begin_date和end_date之间选择范围 - mysql [英] How to select range between begin_date and end_date - mysql

问题描述

在这种情况下，我有离开牌桌：

In this case i have "leave" table:

+--------+---------+---------+-------------+---------+-----------+
|ID_LEAVE|ID_WORKER| BEGIN_DATE          | END_DATE              |
+--------+---------+---------+---------+------------+------------+
| 4      |   26    |2019-03-19 07:00:00  |2019-03-22 15:00:00    |  
+--------+---------+---------+----------------------+------------+

我想写mysql查询选择全部在开始日期和结束日期之间，然后总结。我这样写的时候：

I wanna write mysql query which selects all in range between Begin Date and End date and then sum up. When i write like that:

SELECT *, 
time_format(SUM((datediff(END_DATE, BEGIN_DATE) + 1) * (time(END_DATE) - time(BEGIN_DATE))), '%H:%i:%s') AS 'LEAVE TIME' 
FROM leave 
WHERE (DATE(BEGIN_DATE) >= '2019-03-19' AND DATE(END_DATE) <='2019-03-22') 
GROUP BY ID_LEAVE;

然后显示如下：

Then shows like this:

+--------+---------+---------+-------------+---------+------------------------+
|ID_LEAVE|ID_WORKER| BEGIN_DATE          | END_DATE              | LEAVE_TIME |
+--------+---------+---------+---------+------------+--------------------+-----
| 4      |   26    |2019-03-19 07:00:00  |2019-03-22 15:00:00    |  32:00:00  |
+--------+---------+---------+----------------------+------------+-------------

SELECT *, 
time_format(SUM((datediff(END_DATE, BEGIN_DATE) + 1) * (time(END_DATE) - time(BEGIN_DATE))), '%H:%i:%s') AS 'LEAVE TIME' 
FROM leave 
WHERE (DATE(BEGIN_DATE) >= '2019-03-19' AND DATE(END_DATE) <='2019-03-21') 
GROUP BY ID_LEAVE;

它什么都没有显示。



我有一个问题：是否可以在（Begin_date和End_date）范围内显示所有内容？如果是，有人可以解释如何写吗？谢谢你的回答。



我的尝试：



我我试过这样的写作：

It shows nothing.

I have a question: Is it possible to show all in (Begin_date and End_date) range? If yes to can someone please explain how to write it? Thanks for any answer.

What I have tried:

I've tried writing like this:

SELECT *, 
time_format(SUM((datediff(END_DATE, BEGIN_DATE) + 1) * (time(END_DATE) - time(BEGIN_DATE))), '%H:%i:%s') AS 'LEAVE TIME' 
FROM leave 
WHERE (DATE(BEGIN_DATE) >= '2019-03-19' AND DATE(END_DATE) <='2019-03-21') 
GROUP BY ID_LEAVE;

但它没有帮助

But it didn't help

推荐答案

WHERE (DATE(BEGIN_DATE) >= '2019-03-19' AND DATE(END_DATE) <='2019-03-21')

您正在尝试选择其记录结束日期小于或等于21，但你记录的结束日期是22。

You are trying to select records whose end date is less than or equal to 21, but the end date of your record is 22.

根据理查德的解决方案，我建议阅读这篇优秀的文章，它可以帮助你理解时间段关系： .NET时代期间库 [ ^ ]



似乎，您想要获取在所需时间段内开始 或 的数据...您必须在 WHERE 声明：



Accordingly to Richard's solution, i'd suggest to read this excellent article, which may help you understand time period relations: Time Period Library for .NET[^]

Seems, you want to fetch data which starts or ends within desired time period... You have to make appropriate changes in your WHERE statement:

WHERE (DATE(BEGIN_DATE) >= '2019-03-19' AND DATE(END_DATE) <='2019-03-21')
    OR (DATE(BEGIN_DATE) >= '2019-03-19' OR DATE(END_DATE) <='2019-03-21')

的第一部分声明获取开始日期和结束日期在所需时间段（封闭时间段）之间的数据，第二个获取可能重叠/超过所需时间段的数据。



得到了吗？

First part of where statement gets the data which start date and end date are between desired time period (enclosed time period), the second one gets the data which may overlap/exceed desired time period.

Got it?

这篇关于如何在begin_date和end_date之间选择范围 - mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！