phpmyadmin Question posted in
The official documentation can be found here.

SQL Find entries WHERE a specific day is on or between dateTimeStart and dateTimeFinish – Phpmyadmin

I’m looking to run a query that gets all rows that occur on a specific date.

enter image description here

For example, the query should return both of these entries, if I want all entries that occured on 2019-03-21.

Any ideas on how to do this?

Tags:

4

Answers


  1. 0

    You can use the date function:

    SELECT * from table where date(my_date) = '2019-03-21'

    Source: date

    Login or Signup to reply.
  2. 0

    You want:

    where '2019-03-21' <= date(enddatetime) and
      '2019-03-21' >= date(startDatetime)

    However, the most efficient way to write the query would be:

    where enddatetime >= date('2019-03-21') and
      startdatetime < date('2019-03-21') + interval '1 day'
    Login or Signup to reply.
  3. 0

    We’d need to check for an overlap, of datetime values from the rows, with start and end datetime

    visually, given a start and end we want to check overlap for, there are several possibilities:

         start       end
       |          | 
  s--e |          |             no overlap
  s----e          |             overlap (*)
  s--------e      |             overlap
  s---------------e             overlap
  s------------------e          overlap
       s---e      |             overlap
       s----------e             overlap
       s-------------e          overlap
       |  s---e   |             overlap
       |  s-------e             overlap
       |  s----------e          overlap
       |          s----e        overlap (*)
       |          |  s---e      no overlap

    to find rows that do not overlap (assuming that start_date_time is always before end_date_time, and both are non-null) we could use a test like this:

      WHERE r.end_date_time    <  '2019-03-21 00:00'
     OR r.start_date_time  >= '2019-03-22 00:00'

    If the “end” on the row is before the start of the range we want to test, there is no overlap. Or, if the “start” on the row is on or after the end of the range, there is no overlap.

    This is checking for any overlap at any time on 3/21/2019. (“start” is 3/21, and “end” is 3/22.)

    Inverting that condition would give us rows that do overlap:

      WHERE NOT ( r.end_date_time    <  '2019-03-21 00:00'
           OR r.start_date_time  >= '2019-03-22 00:00'
            )

    Which can be rewritten: 

      WHERE  r.end_date_time    >= '2019-03-21 00:00'
    AND  r.start_date_time  <  '2019-03-22 00:00'

    (Note that we need a more elaborate condition, if the values of start_date_time and/or end_date_time can be NULL, or if end_date_time can be before start_date_time.)

    Login or Signup to reply.
  4. 0

    Since you only have a DATE (no time) and you need to check with a DATETIME, then ignore the time. Extract the DATE portion of the DATETIME column and check if your date is between those dates:

    SELECT * FROM table_name
         WHERE '2019-03-21' BETWEEN DATE(StartDateTime) AND DATE(EndDateTime)

    Doing the opposite may also work:

    SELECT * FROM table_name
         WHERE CAST('2019-03-21' AS DATETIME) BETWEEN StartDateTime AND EndDateTime
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search