如何在mysql中获取两个日期之间有时间的记录? [英] How to get record in mysql that has a time in between two dates?

问题描述

我正在制作一个考勤系统，它包含一个介于两个日期之间的时间表，例如(晚上 10:00 到凌晨 3:00).如何从时间范围介于 2 个日期之间的 mysql 数据库中检索行?

I am making an attendance system and it consist of a time schedule that is in between two dates like for example (10:00pm to 3:00am). How do i retrieve rows from mysql database that has a time range that are in between 2 dates?

我已经知道如何在 mysql 中使用 BETWEEN，我的问题是如何在不知道确切时间范围的情况下查询 mysql?因为它可以是任何样子(晚上 11:00 到凌晨 4:00)

I already know how to use BETWEEN in mysql, my problem is how do I query mysql without knowing the exact time range? because it could be anything like it could be (11:00pm to 4:00am)

我正在使用 3 个表:time_tbl:存储有关员工时间表的信息(例如晚上 10 点到凌晨 3 点)date_tbl:存储有关员工日期安排的信息(例如星期一、星期二等)time_date_tbl:是一个联结表，从表time_tbl和date_tbl中取出员工的时间和日期时间表.

I am using 3 table: time_tbl: stores information about employee's time schedule (e.g. 10pm to 3am) date_tbl: stores information about employee's date schedule (e.g. Monday, Tuesday, etc.) time_date_tbl: is a junction table, with time and date schedule of an employee taken from the table time_tbl and date_tbl.

    time_tbl
    --------------------------------
    |  ID  |  Start    |  End      |
    --------------------------------
    |  1   |  22:00:00 |  03:00:00 |
    |  2   |  23:00:00 |  04:00:00 |
    |  3   |  08:00:00 |  11:00:00 |
    |  4   |  13:00:00 |  17:00:00 |
    --------------------------------

    date_tbl
    ---------------------
    |  ID  |  Days      |
    ---------------------
    |  1   |  Monday    |
    |  2   |  Tuesday   |
    |  3   |  Wednesday |
    ---------------------

    time_date_tbl
    -------------------------------------
    |  emp_id  |  time_id    |  date_id |
    -------------------------------------
    |  1       |  1          |  1       |
    |  1       |  1          |  2       |
    |  2       |  1          |  1       |
    |  3       |  3          |  1       |
    |  4       |  4          |  1       |
    |  4       |  1          |  1       |
    -------------------------------------

我想查询 mysql 以便我检索数据(例如今天是星期一)我想检索日期等于今天这是星期一"的记录，还包括像 10:00pm 到 3:00am 这样的记录，其中晚上 10 点是星期天，星期一的前一天......

I want to query mysql so that i would retrieve data (for example today is Monday) i want to retrieve records that has a date equal today "which is Monday" and also include records like 10:00pm to 3:00am where 10pm was on a Sunday, a day before Monday...

    desired result:
    -----------------------------------------------
    |  emp_id  |  time                 |  date    |
    -----------------------------------------------
    |  1       |  22:00:00 - 03:00:00  |  ?       | <-- started @ Sunday 10pm
    |  2       |  22:00:00 - 03:00:00  |  ?       | <-- started @ Sunday 10pm
    |  3       |  08:00:00 - 11:00:00  |  Monday  |
    |  4       |  13:00:00 - 17:00:00  |  Monday  | 
    |  4       |  22:00:00 - 03:00:00  |  ?       | <-- started @ Sunday 10pm
    -----------------------------------------------

mysql 中是否有某种Date Crosser"函​​数/算法来完成这项工作?...

Is there some kind of a "Date Crosser" function/algorithm in mysql to get this job done?...

推荐答案

感谢您更新问题并提供更多信息.如果您想按天查询并返回时间范围...您的表格会使这有点复杂...

Thanks for updating the question with more information. If you want to query by day and have the time ranges returned... Your tables will make this a little complicated...

SELECT tdt.emp_id,CONCAT(DATE_FORMAT(tt.Start,'%H:%m:%s'),' - ',DATE_FORMAT(tt.End,'%H:%m:%s')),dt.Days
FROM time_date_tbl tdt INNER JOIN date_tbl dt ON (tdt.date_id=dt.`ID`) INNER JOIN time_tbl tt ON (tt.`ID`=tdt.time_id)
WHERE dt.ID=1

现在我在上面没有看到的是一个带有日期的实际日期字段...换句话说，上面的查询将列出所有星期一...永远.您可能需要在 WHERE 部分添加更多内容以限制更多内容.

Now what I don't see above is an actual date field with a date... to put that another way the query above would list off all Mondays... ever. You may need to add more to the WHERE section to limit it more.

现在，如果这是一个较新的项目并且您仍然有能力重构...如果 time_tbl 是一个 id 以及两个日期 - 一个开始日期时间和一个结束日期时间，您真的不需要其余的表:

Now if this is a newer project and you still have the ability to restructure... if time_tbl is an id along with two dates - a start datetime and an end datetime you really wouldn't need the rest of the tables:

SELECT emp_id,CONCAT(DATE_FORMAT(tt.Start,'%H:%m:%s'),' - ',DATE_FORMAT(tt.End,'%H:%m:%s'))
FROM time_tbl tt
WHERE (DATE_FORMAT('%w',tt.Start)=1) OR (DATE_FORMAT('%w',tt.End)=1)

这将再次列出所有星期一.您可以添加到更多限制它的位置.

Again this would list all Mondays. You can add to the where to limit it more.

这篇关于如何在mysql中获取两个日期之间有时间的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！